Writing a loop within a macro in SAS

Question

What is wrong with my program below? I am trying to import several excel files and merge them. Rename does not work in the import step. More importantly, merge does not work even if datasets y1 and y2 are created. Thanks.

proc sort data=sourceh.caps;
by symbol;
run;

%MACRO RunProgram(month, year, n);

PROC Import DATAFILE= "D:\new\&month. &year. &n. min correlations.xls" 
   dbms=excel5  OUT= sourceh.y&n. (rename=(avcorr=y&n.))  replace;
    GETNAMES=YES;
RUN;


data sourceh.testy&month.&year.;
merge sourceh.y&n. sourceh.caps;
by symbol;
drop number;
Month="&month.";
Year=&year.;
run;

%MEND;

%macro l;

%do n=1 %to 2;

%RunProgram(Jan, 12, &n);
%RunProgram(Apr, 12, &n);

%end;

%mend;

%l;

"Does not work" needs to be explained. My guess is your variable names aren't correct. I also think your macro is not quite right for what you want, because the output dataset from n=1 will be overwritten by n=2. — Joe
Thanks. It was indeed being overwritten. I fixed it ,possibly not in the most elegant or efficient way but it worked. — Betty

DaBigNikoladze DaBigNikoladze · Accepted Answer · 2014-07-20T15:22:00

Joe is right, we need the log errors.

Your code's syntax seems ok, check that the imported file is sorted by symbol.

Try to rename the variable in a separate data step (or in the one with the merge) after the proc import.

Writing a loop within a macro in SAS

1 Answers