BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
scardinet
Fluorite | Level 6

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.

 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

5 REPLIES 5
art297
Opal | Level 21

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

 

scardinet
Fluorite | Level 6

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 🙂

ChrisNZ
Tourmaline | Level 20

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;
ChrisNZ
Tourmaline | Level 20

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
scardinet
Fluorite | Level 6

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 🙂

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
  • 547 views
  • 3 likes
  • 3 in conversation