前言
本片文章适用于,已拥有数据集的模板规范(specification),包含产出哪些datasets,每个dataset拥有哪些variable,每个变量拥有何种属性均为已知。需要根据spec,产生一系列空的数据集dummy datasets(shell),以便后期使用,减少冗余代码,提高工作效率。
一、思路:使用proc sql 创建dummy dataset
下方示例:使用proc sql 创建dummy dataset,将variable name, length, label等依次输入。
proc sql;
create table demo
( STUDYID char(20) label="Study Identifier"
,SUBJID char(20) label="Subject Identifier"
,AGE num label="Age"
);
quit;
二、使用macro创建shell步骤
在使用proc sql 创建dummy datasets基础上,可以使用macro,结合spec批量创建shell。
1.首先需要将spec引入
下方以.xlsx格式的Spec示例,使用PROC IMPORT引入Spec数据集。
PROC IMPORT OUT=datasets
DATAFILE= "E:\project\Specification.xlsx"
DBMS=excel replace;
SHEET="datasets";
GETNAMES=yes;
RUN;PROC IMPORT OUT=variables
DATAFILE= "E:\project\Specification.xlsx"
DBMS=excel replace;
SHEET="variables";
GETNAMES=yes;
RUN;
2.创建宏程序
代码如下(示例):
%macro shell_demo;
/*Define macro variables.
1.dstnum:total number of datasets,
2.dstlist: all datasets name lists;
3.labellist:all datasets label*/
proc sql noprint;
select count(distinct dataset) into:dstnum from datasets where dataset^="";
select dataset into:dstlist separated by " " from datasets where dataset^="";
select description into:labellist separated by "@" from datasets where dataset^="";
quit;
/*Perform do-loop to get dataset name and label to each dataset*/
%do k=1 %to &dstnum.;
/*Define macro variables. 1.dst:datasets name 2.dstlabel:dataset label*/
data _null_;
call symputx("dst","%scan(&dstlist,&k,' ')");
call symputx("dstlabel","%scan(&labellist,&k,'@')");
run;
/*Assign data type for each variable*/
data &dst.;
length domain variable label type $200.;
set variables;
where dataset="%upcase(&dst.)";
if data_type in ("Char") then type="CHAR";
else if data_type in ("Num") then type="NUM";
domain=dataset;
run;
/*Assign data length for each variable*/
data spec;
length type_len $200;
set &dst.;
where (upcase(type) in ("CHAR" "NUM"));
if upcase(type) in ('CHAR') then type_len="char(200)";
else type_len="num(8)";;
run;
/*Define macro variables: 1.variable 2.label 3.type_len 4.varnum 5.varlist*/
%global varlist;
proc sql noprint;
select variable into: var1-:var999 from spec;
select label into: lab1-:lab999 from spec;
select type_len into: len1-:len999 from spec;
select count(*) into: varnum from spec;
select variable into: varlist separated by ' ' from spec;
quit;
/*Create shell datasets one by one*/
proc sql;
create table &dst(label="&dstlabel.")
(
%do i=1 %to &&varnum;
%if &i=1 %then %let comma_=;
%else %let comma_=,;
&comma_ &&var&i &&len&i label="&&lab&i"
%end;
);
quit;
%end;
%mend shell_demo;
%shell_demo;
总结
当数据集中的变量需要根据既定的规范(Spec),即所有变量需要根据指定的的顺序,结构,长度,变量标签展现时,单纯使用data步或proc步定义,效率比较低。使用Spec定期线下维护,结合创建shell,再将编程好的dataset,同shell dataset进行set的方式较为优。