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.
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
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
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 🙂
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;
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
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 🙂
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.