topic Re: Stacking multiple datasets within a Macro in SAS Programming

Stacking multiple datasets within a Macro

DangIT — Wed, 18 Jun 2014 14:04:19 GMT

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.

Re: Stacking multiple datasets within a Macro

RW9 — Wed, 18 Jun 2014 14:23:34 GMT

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.

Re: Stacking multiple datasets within a Macro

stat_sas — Wed, 18 Jun 2014 14:27:50 GMT

%macro allsets;

data master;

set

%do i=1 %to 10;

have&i

%end;

;

run;

%mend allsets;

%allsets

Re: Stacking multiple datasets within a Macro

DangIT — Wed, 18 Jun 2014 14:28:25 GMT

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?

Re: Stacking multiple datasets within a Macro

Reeza — Wed, 18 Jun 2014 14:31:22 GMT

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;

Re: Stacking multiple datasets within a Macro

RW9 — Wed, 18 Jun 2014 14:32:06 GMT

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;

Re: Stacking multiple datasets within a Macro

RW9 — Wed, 18 Jun 2014 14:33:27 GMT

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

Re: Stacking multiple datasets within a Macro

DangIT — Wed, 18 Jun 2014 14:45:59 GMT

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.

Re: Stacking multiple datasets within a Macro

DangIT — Wed, 18 Jun 2014 14:52:56 GMT

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?

Re: Stacking multiple datasets within a Macro

Reeza — Wed, 18 Jun 2014 15:07:39 GMT

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

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

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

Re: Stacking multiple datasets within a Macro

Tom — Wed, 18 Jun 2014 15:25:02 GMT

Search for DATA SET LISTS.

http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a003040446.htm