Querying Multiple Datasets Into One Excel File

[Tegan]

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"

Possible?

[art297]

Do you want a separate worksheet for each year?

What version of SAS are you using and on which operating system?

[Ksharp]

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;

Xia Keshan

[RW9]

Hi,

For data on separate sheets:

ods tagsets.excelxp file="c:\output.xls" style=statistical options (frozen_headers="1" absolute_column_width="none" zoom="80");

proc sql;
  create table TMP as
  select  distinct MEMNAME
  from    DICTIONARY.TABLES
  where   LIBNAME="***insert your library name here***";
quit;

data _null_;
  set tmp;
  call execute('ods tagsets.excelxp options (sheet_name="'||strip(memname)||'");
                proc report data=***insert your libname here***.'||strip(memname)||';
                run;');
run;

ods tagsets.excelxp close;

If you need it in one sheet then drop the options(sheet_name part.

[Haikuo]

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.

Thanks,

Haikuo