I am trying to automate the set statment using a loop and macros. Below is the code:
libname abc "<path>";
%let startyr=2011;
data _null_;
today=today();ymm=month(today())-1;
yyr=year(today());
if ymm=01 then do; yyr=year(today())-1; call symput('endyr', put(yyr, Z4.)); end;
else call symput('endyr',put(year(today),z4.));
call symput('todaymm', put(ymm, Z2.));
run;
%macro tloop;
data temp1;
set
%do y=&startyr %to &endyr;
%do i=1 %to 12;
%if &i LT 10 %then %do; abc.myfile&y.0&i; %put abc.myfile&y.0&i; %end;
%else %do; abc.myfile&y&i ;%put abc.myfile&y.&i; %end;
%if &y=&endyr and &i=%eval(&todaymm-1) %then
%do; stop; %end;;
%end;
if n=1 then stop;
%end;;
run;
%mend tloop;
%tloop;
the desired output is
data temp1;
set abc.myfile201101 abc.myfile201102
abc.myfile201103 abc.myfile201104
abc.myfile201105 abc.myfile201106
abc.myfile201107 abc.myfile201108
abc.myfile201109 abc.myfile201110
abc.myfile201111 abc.myfile201112
abc.myfile201201 abc.myfile201202
abc.myfile201203 abc.myfile201204
abc.myfile201205 abc.myfile201206
abc.myfile201207 abc.myfile201208
abc.myfile201209 abc.myfile201210
abc.myfile201211 abc.myfile201212
abc.myfile201301 abc.myfile201302
abc.myfile201303 abc.myfile201304
abc.myfile201305 abc.myfile201306
abc.myfile201307 abc.myfile201308
abc.myfile201309 abc.myfile201310
abc.myfile201311 ;
run;
but when I run this code I get an error:
abc.myfile201101
NOTE: Line generated by the macro variable "I".
31 abc.myfile201102
_____________________
557
ERROR: DATA STEP Component Object failure. Aborted during the COMPILATION phase.
ERROR 557-185: Variable abc is not an object.
am I making any mistake here?
Or is there a better way to handle this?
Thank you.
I think your error is coming from these lines:
%if &i LT 10 %then %do; abc.myfile&y.0&i; %end;
%else %do; abc.myfile&y&i ; %end;
You do not want the ; after the data set name as it will appear in your set statement as
set;
abc.myfile201101;
abc.myfile201102;
abc.myfile201103;
etc.
Which is why it complained on the second iteration.
it is good that you have the second ; at the %end;;
Try using options mprint to examne the generated code when you have unexpected errors in macros.
Is there any reason you can't use the colon modifier to set all the files of the years as follows?
set abc.myfile2011: abc.myfile2012: abc.myfile2013:;
ballardw and Reeza.. Thank you for the response...
Ballardw: it was the semicolon problem , I was able to fix it. Thank you for the hint below is the modified code:
%macro tloop;
data temp1;
set
%do y=&startyr %to &endyr;
%do i=1 %to 12;
%if &i LT 10 %then %do;
abc.myfile.0&i %end;
%else %do; abc.myfiley&i %end;
%if &y=&endyr and &i=(&todaymm) %then %goto leave;
%end;
%end;;
%leave: ;
run;
%mend tloop;
Reeza: I haven't tried the colon modifier, will try it out.
Thank you
For reference you also could do it using proc sql this way:
proc sql;
create table temp1 as
%do y=&startyr %to &endyr;
%do i = 1 %to 12;
%if &i <10 %then %do;
select distinct * from abc.myfile&y.0&i
%end;
%else %do;
select distinct * from abc.myfile&y.&i
%if &y =&endyr and &i = 12 %then %do;
;
%end;
%else %do;
union corr
%end;
%end;
%end;
%end;
quit;
Adding the extra %IF/%THEN to deal with the leading zeros in the month value probably contributed to the confusion.
You could formulate the loop differently to avoid that problem. Here are two ways.
Since YYYYMM looks like a number you could just generate the sequence 201101 to 201112 as the inner %DO loop.
%macro x;
%do year=2011 %to 2013;
%do yearmonth=&year.01 %to &year.12 ;
abc.myfile&yearmonth
%end;
%end;
%mend x;
%put %x;
Or you can take advantage of SAS formats, such as YYMMN6. .
%macro y;
%do year=2011 %to 2013;
%do month=1 %to 12 ;
abc.myfile%sysfunc(mdy(&month,1,&year),yymmn6.)
%end;
%end;
%mend y;
%put %y;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: