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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

DangIT
Fluorite | Level 6

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?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

stat_sas
Ammonite | Level 13

%macro allsets;

data master;

set

%do i=1 %to 10;

have&i

%end;

;

run;

%mend allsets;

%allsets

Reeza
Super User

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;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

DangIT
Fluorite | Level 6

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.

DangIT
Fluorite | Level 6

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?

Reeza
Super User

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

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
  • 10 replies
  • 8786 views
  • 6 likes
  • 5 in conversation