SAS中是否有方法在多个数据集上运行相同的程序集

Question

我在rawMed库中有大约10个数据集，我想运行这段代码，创建所有变量的副本，将它们连接到旧数据集并输出新数据集。有没有一种方法可以将其合并到一个循环中，在不复制和粘贴10次的情况下创建这些新表？我还想自动创建表的列表，因为将来可能会添加新的表，这也需要重复。

(我不能先合并所有表并运行单个proc转置，因为生成的表会出错，因为它大约有2000列(

proc sort data= rawMed.mr1982;
by MRFORM MedReportID;
run;
proc transpose data=rawMed.mr1982 out=long_mr1982 (rename=(col1=rawValue ));
by   MRFORM MedReportID;
var _all_;
run;
data want_long;
set long_mr1982 ;
newTXDT = rawValue;
run;
proc transpose
data=want_long
out=want_wide(drop=_name_ )
suffix=_new let;
by MRFORM MedReportID ;
id _NAME_;
var newTXDT;
run;`
proc sql;
create table final.f_mr1982 as
select 
a.*
,b.*
from rawmed.mr1982 as a
left join want_wide as b on a.mrform=b.mrform and a.MedReportID=b.MedReportID
;
quit;

Answer 1

这效率很低，但有效。

• 创建一个包含起始年和结束年两个参数的宏
• 使用do循环在参数之间循环
• 用宏变量替换数据集中年份(1982(的所有实例
• 删除任何未唯一命名的中间数据集

%macro loop(yr_start = , yr_end = );
%do yr=&yr_start %to &yr_end.;
proc sort data= rawMed.mr&yr.;
by MRFORM MedReportID;
run;
proc transpose data=rawMed.mr&yr. out=long_mr&yr. (rename=(col1=newtxdt));
by   MRFORM MedReportID;
var _all_;
run;

proc transpose
data=long_mr&yr.
out=want_wide(drop=_name_ )
suffix=_new let;
by MRFORM MedReportID ;
id _NAME_;
var newTXDT;
run;`
proc sql;
create table final.f_mr&yr. as
select 
a.*
,b.*
from rawmed.mr&yr. as a
left join want_wide as b on a.mrform=b.mrform and a.MedReportID=b.MedReportID
;
quit;
*delete want_wide so it does not exist in next loop in case something goes wrong;
proc datasets lib=work nodetails nolist;
delete want_wide;
quit;
%end;
%mend;
*call the macro;
%loop(yr_start = 1982, yr_end = 2019);

Answer 2

如果您想创建每个变量的副本并保留它们的所有属性(格式、长度、标签等(，有更好的方法。

请考虑以下与数据集共联接的宏。然后对您的10个数据集使用宏：

%cojoin (data=rawMed.mr1982, ignore=MRFORM MedReportID, out=final.f_mr1982)
%cojoin (data=rawMed.mr1983, ignore=MRFORM MedReportID, out=final.f_mr1983)
...
%cojoin (data=rawMed.mr1989, ignore=MRFORM MedReportID, out=final.f_mr1989)

%macro cojoin(data=, keep=_all_, ignore=, suffix=_new, out=);
/* pass 1 get names of variables to cojoin */
proc contents noprint data=&data(keep=&keep drop=&ignore) out=_c_;
run;
/* codegen rename and assignment statments */
proc sql noprint;
select 
cats (name,'=',name,"&suffix")
, cats (name,"&suffix",'=',name)
into
:renames separated by ' ' 
, :assignments separated by ';'
from _c_;
%put &renames;
/* pass 2 cojoin each record with assignments */
data &out;
set &data &data(rename=(&renames) obs=0);
&assignments;
run;
proc delete data=_c_;
run; 
%mend;

用法示例：

options mprint;
%cojoin(data=sashelp.cars, ignore=make model, out=work.want);

记录

MPRINT(COJOIN):   data work.want;
MPRINT(COJOIN):   set sashelp.cars sashelp.cars(rename=(Cylinders=Cylinders_new DriveTrain=DriveTrain_new EngineSize=EngineSize_new
Horsepower=Horsepower_new Invoice=Invoice_new Length=Length_new MPG_City=MPG_City_new MPG_Highway=MPG_Highway_new MSRP=MSRP_new
Origin=Origin_new Type=Type_new Weight=Weight_new Wheelbase=Wheelbase_new) obs=0);
MPRINT(COJOIN):   Cylinders_new=Cylinders;
MPRINT(COJOIN):  DriveTrain_new=DriveTrain;
MPRINT(COJOIN):  EngineSize_new=EngineSize;
MPRINT(COJOIN):  Horsepower_new=Horsepower;
MPRINT(COJOIN):  Invoice_new=Invoice;
MPRINT(COJOIN):  Length_new=Length;
MPRINT(COJOIN):  MPG_City_new=MPG_City;
MPRINT(COJOIN):  MPG_Highway_new=MPG_Highway;
MPRINT(COJOIN):  MSRP_new=MSRP;
MPRINT(COJOIN):  Origin_new=Origin;
MPRINT(COJOIN):  Type_new=Type;
MPRINT(COJOIN):  Weight_new=Weight;
MPRINT(COJOIN):  Wheelbase_new=Wheelbase;
MPRINT(COJOIN):   run;