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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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.

View solution in original post

7 REPLIES 7
Reeza
Super User

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
Super User

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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

Tom
Super User Tom
Super User

See updates above.

shixin
Calcite | Level 5

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

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
  • 7 replies
  • 1302 views
  • 6 likes
  • 4 in conversation