Many thanks! Well, first yes. I should &i. I will modify the code. second, because there is just one variable, so I just skip the "separated by". third the macro is as follows, * Macro_Rread; %include "E:\Program\Macro\Macro_Pre_Adv.sas"; %macro Read(varname=,outds=); proc sql noprint; select cats('"',catx('\',path,table),'"') into :path from var_index as A where A.var="&varname"; quit; %Pre_Adv(input=&path,output=&outds,var=&varname); proc sql noprint; select label into :label from var_index as A where A.var="&varname"; quit; data &outds; set &outds; label &varname="&label"; run; %mend Read; %macro Pre_Adv(input=,output=,var=); data IPO; set stk.WIND_IPO; run; PROC IMPORT out=zxczxc1 datafile=&input DBMS=EXCEL REPLACE; GETNAMES=YES; mixed=yes; SCANTEXT=no; RUN; data zxc1(keep=stkcd stknme) zxc2(drop=_COL0 _COL1 stkcd stknme); retain stkcd stknme; set zxczxc1; %let dsid=%sysfunc(open(zxczxc1)); %let n=%sysfunc(attrn(&dsid,nobs)); %let close=%sysfunc(close(&dsid)); stkcd=input(substr(_COL0,1,6),$12.); stknme=input(_COL1,$12.); if _N_=&n or _N_=&n-1 THEN DELETE; run; proc sql noprint; select catx('=','asd'||name,name) , catx('=','asd'||name,label) , cats('asd'||name,'=input(',name,',best12.)') , name into :rename separated by ' ' , :label separated by ' ' , :assign separated by ';' , :drop separated by ' ' from dictionary.columns where libname='WORK' and memname='ZXC2'; quit; data zxc3; retain &drop; set zxc2; &assign; rename &rename; label &label; drop &drop; run; data zxc4; merge zxc1 zxc3; run; proc sort data=zxc4; by stkcd; run; proc transpose data=zxc4 out=zxc5; by stkcd; run; data zxc6; set zxc5; varid=anydigit(_LABEL_,1); year=input(substr(_LABEL_,varid,4),best12.); &var=COL1; run; data zxc7; merge zxc6 IPO; by stkcd; if IPO_date='' then IPO_date=mdy(01,01,1990); if year<year(IPO_date) then delete; run; data &output(keep=stkcd year &var); set zxc7; run; proc datasets library=work; delete zxczxc1 zxc1 zxc2 zxc3 zxc4 zxc5 zxc6 zxc7 IPO; run;quit; %mend Pre_Adv;
... View more
