04-08-2014 08:46 PM
I have Variables A-Z separated by Year in files labelled 1999.sas7bdat, 2000.sas7bdat, ect.
Each yearly file has the same variables, content split by year.
I want to take all 25 years, and query them all to output to one Excel file.
"Give me all rows, in all 25 files, which contain 'dog' in Variable_A, export to one excel file to be saved in this location"
"Give me all rows, in all 25 files, in which Variable_A equals '1', export to one excel file to be saved in this location"
04-08-2014 11:58 PM
Is it a homework ?
data year1999; set sashelp.class; run; data year2000; set sashelp.class; run; data year2001; set sashelp.class; run; data year2002; set sashelp.class; run; %macro one(dsn=); proc export data=&dsn(where=(name contains 'l')) outfile='c:\temp\x1.xls' dbms=excel replace; sheet="&dsn"; run; %mend one; %macro two(dsn=); proc export data=&dsn(where=(age=14)) outfile='c:\temp\x2.xls' dbms=excel replace; sheet="&dsn"; run; %mend two; data _null_; input dsn : $20.; call execute('%one(dsn='||dsn||')'); call execute('%two(dsn='||dsn||')'); cards; year1999 year2000 year2001 year2002 ; run;
04-09-2014 04:17 AM
For data on separate sheets:
ods tagsets.excelxp file="c:\output.xls" style=statistical options (frozen_headers="1" absolute_column_width="none" zoom="80");
create table TMP as
select distinct MEMNAME
where LIBNAME="***insert your library name here***";
call execute('ods tagsets.excelxp options (sheet_name="'||strip(memname)||'");
proc report data=***insert your libname here***.'||strip(memname)||';
ods tagsets.excelxp close;
If you need it in one sheet then drop the options(sheet_name part.
04-09-2014 11:15 AM
An additional comment: If you have 9.3 M2 or newer, then the call execute() from both and could be replaced by DOSUBL(). Personally I start gearing away from using call execute() for the following reason:
1. DOSUBL() is more intuitive in terms of programming flow. It executes the macro right away, not until after the data step finishes.
2. So far for me, it does everything that call execute does, and it becomes the only option if I need to feed back some returns to the data step.
Please correct me if I am wrong.