topic Re: Using a do loop to create a macro array instead of using %LET in SAS Programming

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

scardinet — Wed, 31 Jan 2018 21:31:35 GMT

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.

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

art297 — Wed, 31 Jan 2018 22:42:56 GMT

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

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

ChrisNZ — Wed, 31 Jan 2018 23:28:02 GMT

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;

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

ChrisNZ — Wed, 31 Jan 2018 23:34:38 GMT

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

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

scardinet — Thu, 01 Feb 2018 00:58:40 GMT

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 🙂

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

scardinet — Thu, 01 Feb 2018 01:01:56 GMT

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 🙂