Using a do loop to create a macro array instead of using %LET

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Using a do loop to create a macro array instead of using %LET

Hello,

 

I am querying an online database which has monthly tables (with the month's date in the table's name, e.g. "MONTH_201701"). I want to query all those tables in a do loop. A friend has been able to find relevant information in the following document. Basically, by using the following macro variable containing all the table names, the loop now manages to query all those tables. What I am trying to do now is create this variable automatically by using a span of dates (assuming I want all 12 months in a year).

 

The way the script works now is by having the following line, which I am hoping to replace by a call to a macro with the years as parameters:

 

%LET DB = YEAR_1901 YEAR_1902 YEAR_1903; /* etc. */

 

 

What I would like is for the db variable to be created automatically by a call such as:

 

data _null_;
do y = 1901 to 1903;
%let DB = cat( ' ', &DB, compress( 'YEAR_'!!y ) );
end;
run;

data _null_;
put &DB;
run;

This syntax is incorrect, but my ambition is that since the names of the tables follow an algorithm to simply use that algorithm to create the list of names without having to input them manually.

 

Thanks for your help,

 

Sylvain.

 


Accepted Solutions
Solution
3 weeks ago
PROC Star
Posts: 7,803

Re: Using a do loop to create a macro array instead of using %LET

Posted in reply to scardinet

How about?:

data _null_;
  length years $255;
  do y = 1901 to 1903;
    year=catt('YEAR_',y);
    years=catx(' ',years,year);
  end;
  call symput('DB',years);
run;
%put &db.;

Art, CEO, AnalystFinder.com

 

View solution in original post


All Replies
Solution
3 weeks ago
PROC Star
Posts: 7,803

Re: Using a do loop to create a macro array instead of using %LET

Posted in reply to scardinet

How about?:

data _null_;
  length years $255;
  do y = 1901 to 1903;
    year=catt('YEAR_',y);
    years=catx(' ',years,year);
  end;
  call symput('DB',years);
run;
%put &db.;

Art, CEO, AnalystFinder.com

 

New Contributor
Posts: 3

Re: Using a do loop to create a macro array instead of using %LET

This worked immediately, thanks !! I will leave more feedback regarding the other answers as well as, possibly, there might be a better way to do this code as a whole (I mean my whole macro thing is perhaps inefficient). But as I am time-constrained, just plugging this solution into my code worked like a charm Smiley Happy

PROC Star
Posts: 1,935

Re: Using a do loop to create a macro array instead of using %LET

Posted in reply to scardinet

Maybe you don't need macros.

Look at this:

%*create monthly data;
data D_191601 D_191602 D_191603 D_191604;
  X=1;
run;

%*gather all months;
data _V/view=_V; 
  set D_191601-D_191604 indsname=INDSNAME;
  MONTH=compress(INDSNAME,,'dk');
run;

%*analyse by month;
proc means data=_V;
  by MONTH;
run;
PROC Star
Posts: 1,935

Re: Using a do loop to create a macro array instead of using %LET

Posted in reply to scardinet

Or maybe you can manage your macro loop without that string:


%macro analyse(table);
  proc print; run;
%mend;

%macro loop;
  %local i;
  %do i=191601 %to 191812;
    %if %sysfunc(exist(D_&i.)) %then %analyse(D_&i.);
  %end;
%mend;
%loop
New Contributor
Posts: 3

Re: Using a do loop to create a macro array instead of using %LET

ChrisNZ, thanks for your replies. I accepted the other solution because it was the simplest way to just plug in some code and have it work. However, I realize that your answers open new directions to make my part of the code more maintainable than my current macro. I appreciate the time you took to make those suggestions and I hope to be able to try them out as soon as I have a bit more time.

Cheers Smiley Happy

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 87 views
  • 3 likes
  • 3 in conversation