Previously, I called my macro 4 times to run reports for the past 4 quarters. I get 4 different files.
%macro report(YEAR,QUARTER)
Now, I want to join all the 4 files.
data MATCH;
set REPORT_2013_3
REPORT_2013_2
REPORT_2013_1
REPORT_2012_4;
run;
I was wondering if there is a shorter way....
Thanks!
You can use the colon short cut for datasets or the - as well, similar to variables, as long as you have a naming convention limited to those 4 files.
data match;
set report: ;
OR
set report_2013:;
or set report_2013_1-report_2013_4;
run;
EDIT: This would also be an "Append Data Sets" rather than a "Join" if you were trying to search.
You can use the colon short cut for datasets or the - as well, similar to variables, as long as you have a naming convention limited to those 4 files.
data match;
set report: ;
OR
set report_2013:;
or set report_2013_1-report_2013_4;
run;
EDIT: This would also be an "Append Data Sets" rather than a "Join" if you were trying to search.
Will the order of the records be important in dataset Match?
If not using Reeza's second idea you could add parameters to the macro:
%macro (startyear,endyear, startqtr, endqtr);
and use
set report_&startyear._&startqtr - report_&endyear._&endqtr ;
The order doesn't matter because I created a variable column that shows the year&qtr.
That said, do you know how do I call for the past 4 quarters using today()?
I used the code below to create 4 datasets but now I want to join all 4.
data _NULL_;
count =0;
do i = year(today()) to 2010 by -1 until (count = 4);
if i = year(today()) then
do j = qtr(today()) to 1 by -1 until (count = 4);
call execute('%report('||i||','||j||')');
count + 1;
output;
end;
else
do j = 4 to 1 by -1 until (count = 4);
call execute('%report('||i||','||j||')');
count + 1;
output;
end;
end;
run; /*This creates report_2013_3, report_2013_2, report_2013_1 and report_2012_4 in my work folder*/
data MATCH;
set report: ;
run;
Sometimes I have many reports in my work folder but I only want the last 4 quarters.
data _null_;
length filelist $200;
do i=-3 to 0 ;
date = intnx('qtr',today(),i);
year= year(date);
qtr = qtr(date);
put year= qtr=;
filelist=catx(' ',filelist,catx('_','REPORT',year,qtr));
end;
put filelist=;
call symputx('filelist',filelist);
run;
data want ;
set &filelist;
run;
year=2012 qtr=4
year=2013 qtr=1
year=2013 qtr=2
year=2013 qtr=3
filelist=REPORT_2012_4 REPORT_2013_1 REPORT_2013_2 REPORT_2013_3
Updated to include example of how to generate macro variable with list of dataset names for use in a later SET statement.
Thanks, but I am confused where I should insert my set statement...
See updates above.
I learned alot just by looking at your code, Thank you!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.