topic Re: Macros to create excel output from a subset of sas datasets in SAS Programming

Macros to create excel output from a subset of sas datasets

AnnMarie — Wed, 26 Mar 2014 01:06:08 GMT

Please help!

How do I use a macro to read in 15 datasets (with the same variables) - location G:\xyz\output (filenames follow the same format - example ab_city.sas7bdat, xx_city.sas7bdat, yz_city.sas7bdat.

- and export an excel output of a subset of the variables in separate excel files to one location (G:\xyz\output\city). Each file will be saved as G\xyz\output\city\ab_city.xls.
I appreciate your help!

Re: Macros to create excel output from a subset of sas datasets

Reeza — Wed, 26 Mar 2014 01:30:49 GMT

Here's a sketch, you'll have to specify your export exactly.

libname out 'G:\xyz\output';

proc sql;

create table want as

select distinct memname into :file1-:file15 from dictionary.table

where libname="OUT" and upcase(memname) like "%_CITY";

quit;

%put &file1;

%put &file10;

%put &file15.;

%macro export_files;

%do i=1 %to 15;

proc export data=&&file&i .....rest of export statements;

%end;

%mend;

%export_files

Re: Macros to create excel output from a subset of sas datasets

Reeza — Wed, 26 Mar 2014 02:37:46 GMT

Adding in comments to help you understand it... and cleaning it up

SAS has a table called sashelp.vtable or Dictionary.table. Navigate to sashelp.vtable and take a look at it.

This will query that table and pull the table names into 15 macro variables file1 to file15. The where clause is used to filter the table, so lib name is the library as assigned and memname is the dataset name, where it starts anything but ends with _CITY.

Play around with different options to see what it returns.

proc sql noprint;

select distinct memname into :file1-:file15 from dictionary.table

where libname="OUT" and upcase(memname) like "%_CITY";

quit;

Re: Macros to create excel output from a subset of sas datasets

RW9 — Wed, 26 Mar 2014 09:31:03 GMT

Hi,

Just to add, in your methodology you could just write:

data _null_;

set sashelp.vtable (where=(libname="WORK" and index(upcase(memname),"TEST")>0));

call execute('proc export data=work.'||strip(memname)||' outfile="s:\temp\rob\'||strip(memname)||'.xls"; run;');

run;

Note to replace WORK and TEST with your libname and filenames. Saves all that macro processing.

Re: Macros to create excel output from a subset of sas datasets

AnnMarie — Wed, 26 Mar 2014 11:00:25 GMT

Thank you!

I modified yours a bit to see if without using the macro, I can get it one step at a time.
can I modify it to this? (I'm new at this and would like to know the basic and then work thru the complex)

*read a few variables from the original dataset

- data exportit

set output.filename; **(this calls all the variables in the original dataset)

keep var1, var2, var5, var10 **(this keeps the few variables I need to export to xls)

run;

proc export data = exportit;

outfile = 'G:\xyz\output\file1\var1.xls'

dbms=xls replace;

run;

I appreciate your help!

*(your original code);

data _null_;

set sashelp.vtable (where=(libname="WORK" and index(upcase(memname),"TEST")>0));

call execute('proc export data=work.'||strip(memname)||' outfile="s:\temp\rob\'||strip(memname)||'.xls"; run;');

run

Re: Macros to create excel output from a subset of sas datasets

RW9 — Wed, 26 Mar 2014 11:56:08 GMT

Hi,

No probs. Let me explain the call execute first, this may clarify things. Call execute takes a string and sends that string directly to the compiler rather than to the macro pre-processor. The macro pre-processor is a bit like a find replace function where it expands all you code to full code before sending to the compiler, so it resolves macro variables, expands %if or %do loops etc. What I am doing is creating the code I want to execute - and this can be any code - and utilizing the datastep which operates as a loop over each row to generate the code which gets sent to the compiler. Here is an update following your request:

data _null_;

set sashelp.vtable (where=(libname="WORK" and index(upcase(memname),"TEST")>0));

call execute('data work.exportit (keep=var1 var2 var5 var10); set work.'||strip(memname)||'; run;

proc export data=work.exportit outfile="s:\temp\rob\'||strip(memname)||'.xls"; run;');

run;

What this actually does is first creates a temporary dataset with all records from vtable where libname=WORK and TEST is in the dataset name. Then for each of these rows creates a string - say we have two: x1_test and y1_test then there will be two strings sent to the compiler:

"data work.exportit (keep=var1 var2 var5 var10); set work.x1_test; run;

proc export data=work.exportit outfile="s:\temp\rob\x1_test.xls"; run;"

"data work.exportit (keep=var1 var2 var5 var10); set work.y1_test; run;

proc export data=work.exportit outfile="s:\temp\rob\y1_test.xls"; run;"

If you check the log when you run something like the above, you will see what is generated under the 'NOTE: CALL EXECUTE generate line'

Re: Macros to create excel output from a subset of sas datasets

AnnMarie — Wed, 26 Mar 2014 13:06:43 GMT

Thank you so much For the details!!