topic Re: Joining multiple datasets in SAS Programming

Joining multiple datasets

shixin — Fri, 27 Sep 2013 14:31:06 GMT

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!

Re: Joining multiple datasets

Reeza — Fri, 27 Sep 2013 15:13:34 GMT

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: ;

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.

Re: Joining multiple datasets

ballardw — Fri, 27 Sep 2013 15:22:25 GMT

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 ;

Re: Joining multiple datasets

shixin — Fri, 27 Sep 2013 17:14:28 GMT

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;

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.

Re: Joining multiple datasets

Tom — Fri, 27 Sep 2013 17:54:20 GMT

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.

Re: Joining multiple datasets

shixin — Fri, 27 Sep 2013 18:04:25 GMT

Thanks, but I am confused where I should insert my set statement...

Re: Joining multiple datasets

Tom — Fri, 27 Sep 2013 18:07:39 GMT

See updates above.

Re: Joining multiple datasets

shixin — Fri, 27 Sep 2013 18:10:51 GMT

I learned alot just by looking at your code, Thank you!!