topic Querying Multiple Datasets Into One Excel File in SAS Procedures

Querying Multiple Datasets Into One Excel File

Tegan — Wed, 09 Apr 2014 00:46:20 GMT

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?

Re: Querying Multiple Datasets Into One Excel File

art297 — Wed, 09 Apr 2014 01:20:01 GMT

Do you want a separate worksheet for each year?

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

Re: Querying Multiple Datasets Into One Excel File

Ksharp — Wed, 09 Apr 2014 03:58:28 GMT

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

Re: Querying Multiple Datasets Into One Excel File

RW9 — Wed, 09 Apr 2014 08:17:13 GMT

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.

Re: Querying Multiple Datasets Into One Excel File

Haikuo — Wed, 09 Apr 2014 15:15:47 GMT

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