BookmarkSubscribeRSS Feed
invincible06
Calcite | Level 5


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.

5 REPLIES 5
ballardw
Super User

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.

Reeza
Super User

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

invincible06
Calcite | Level 5

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

overmar
Obsidian | Level 7

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;

Tom
Super User Tom
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1637 views
  • 0 likes
  • 5 in conversation