DATA Step, Macro, Functions and more

automation using macros for set statement.

Reply
New Contributor
Posts: 2

automation using macros for set statement.


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.

Super User
Posts: 11,343

Re: automation using macros for set statement.

Posted in reply to invincible06

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.

Super User
Posts: 19,770

Re: automation using macros for set statement.

Posted in reply to invincible06

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

New Contributor
Posts: 2

Re: automation using macros for set statement.

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

Frequent Contributor
Posts: 83

Re: automation using macros for set statement.

Posted in reply to invincible06

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;

Super User
Super User
Posts: 7,039

Re: automation using macros for set statement.

Posted in reply to invincible06

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;

Ask a Question
Discussion stats
  • 5 replies
  • 708 views
  • 0 likes
  • 5 in conversation