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.
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;
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.
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?
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;
%macro allsets;
data master;
set
%do i=1 %to 10;
have&i
%end;
;
run;
%mend allsets;
%allsets
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;
Interesting, I had seen that wildcard-esque type before, would you have a link to the help on that?
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.
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?
@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;
Search for DATA SET LISTS.
http://support.sas.com/documentation/cdl/en/lrcon/62955/HTML/default/viewer.htm#a003040446.htm
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.
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.