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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.