DATA Step, Macro, Functions and more

Stacking multiple datasets within a Macro

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 81
Accepted Solution

Stacking multiple datasets within a Macro

Hi,

I have a macro that run's a 'report' on in the structure of:

%macro Report(Weekname,LYwk,TYwk) ;

which creates an output dataset named by the Weekname...

An example of a few weeks of the above macro (I have about to run):

%Report(Feb_wk02,201302,201402) ; (output dataset = Feb_wk01)

%Report(Feb_wk04,201304,201404) ; (output dataset = Feb_wk04)

What I would like to do is create a macro to stack all of these output datasets into one Master table.

I know I can do it the manual method of :     Data Master; Set (name of all the datasets) ; run ;

But i'm wondering if there's a more automated method.

Thanks in advance for your help.


Accepted Solutions
Solution
‎06-18-2014 10:31 AM
Super User
Posts: 17,864

Re: Stacking multiple datasets within a Macro

You can also use shorthand notation or add a proc append to the end of your main macro.

APPEND @ end of current macro

proc append base=data_final data=feb_wk01;

run;

Short hand using the colon short hand notation, note the colon at the end of the jan_wk: , which tells SAS to include all datasets that start with the name jan_wk.

data data_final;

     set jan_wk: feb_wk: mar_wk: ;

run;

View solution in original post


All Replies
Super User
Super User
Posts: 7,407

Re: Stacking multiple datasets within a Macro

Hi,

You could use the SASHELP.VTABLE, and then generate the code directly from there - e.g:

data _null_;

     set sashelp.vtable (where=(libname="your libname")) end=last;

     if _n_=1 then call execute('data total; set ');

     call execute(memname||" ");

     if last then call execute('; run;');

run;

This will generate a dataset with a set and then every dataset within the library.

Frequent Contributor
Posts: 81

Re: Stacking multiple datasets within a Macro

Thank you, i will look into this method, can the above code be modified to include a prefix of the tables rather than all the datasets in the library?

Super User
Super User
Posts: 7,407

Re: Stacking multiple datasets within a Macro

Yes, sure.  Whatever you can do in a dataset you can do here so assuming I prefix with "AAA":

data _null_;

     set sashelp.vtable (where=(libname="your libname" and susbtr(memname,1,3)="AAA")) end=last;

     if _n_=1 then call execute('data total; set ');

     call execute(memname||" ");

     if last then call execute('; run;');

run;

Trusted Advisor
Posts: 1,204

Re: Stacking multiple datasets within a Macro

%macro allsets;

data master;

set

%do i=1 %to 10;

have&i

%end;

;

run;

%mend allsets;

%allsets

Solution
‎06-18-2014 10:31 AM
Super User
Posts: 17,864

Re: Stacking multiple datasets within a Macro

You can also use shorthand notation or add a proc append to the end of your main macro.

APPEND @ end of current macro

proc append base=data_final data=feb_wk01;

run;

Short hand using the colon short hand notation, note the colon at the end of the jan_wk: , which tells SAS to include all datasets that start with the name jan_wk.

data data_final;

     set jan_wk: feb_wk: mar_wk: ;

run;

Super User
Super User
Posts: 7,407

Re: Stacking multiple datasets within a Macro

Interesting, I had seen that wildcard-esque type before, would you have a link to the help on that?

Frequent Contributor
Posts: 81

Re: Stacking multiple datasets within a Macro

I ended up using the Append approach, it was much simpler solution going forward.

But i will have to include some controls to ensure if the macro that week's table does not append to the master twice if it's re-run for some reason.

Thanks for all your help.

Frequent Contributor
Posts: 81

Re: Stacking multiple datasets within a Macro

Reeza, any thoughts on how I can ensure that if the macro is run again, that the previous data is removed and only the latest data is appended to the master table?

Super User
Posts: 17,864

Re: Stacking multiple datasets within a Macro

@rw9     http://www.pharmasug.org/proceedings/2012/TA/PharmaSUG-2012-TA05.pdf

Couldn't find anything in the docs in my 2 min search Smiley Sad

@dangIT

I would add a check at the beginning of the macro

ie if the output dataset is going to be called feb_wk01, and the data has that info in the data set as well the following may work.

You can change the WHERE criteria to match your data;

proc sql;

delete * from master_table where week="feb_wk01";

quit;

Super User
Super User
Posts: 6,502

Re: Stacking multiple datasets within a Macro

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 3466 views
  • 6 likes
  • 5 in conversation