topic Re: automation using macros for set statement. in SAS Programming

automation using macros for set statement.

invincible06 — Fri, 20 Dec 2013 16:10:16 GMT

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.

Re: automation using macros for set statement.

ballardw — Fri, 20 Dec 2013 16:24:49 GMT

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.

Re: automation using macros for set statement.

Reeza — Fri, 20 Dec 2013 16:59:30 GMT

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:;

Re: automation using macros for set statement.

invincible06 — Fri, 20 Dec 2013 18:02:52 GMT

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

Re: automation using macros for set statement.

overmar — Sat, 21 Dec 2013 19:33:47 GMT

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;

quit;

Re: automation using macros for set statement.

Tom — Sat, 21 Dec 2013 21:22:07 GMT

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;

%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;

%mend y;

%put %y;