Joining multiple datasets

[shixin]

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!

[Reeza · Accepted solution]

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.

[ballardw]

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 ;

[shixin]

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.

[Tom]

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.

[shixin]

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

[Tom]

See updates above.

[shixin]

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