topic Dataset to Excel sheets? in ODS and Base Reporting

Dataset to Excel sheets?

deleted_user — Thu, 11 May 2006 11:10:24 GMT

Hi everyone,
I am not sure about the right forum but...

I have a problem problem and I would be thankful if somebody can solve this.

I have dataset like this...

DOCTOR PATIENT
Ann Mark
John Anton
Ann Jeff
Ann James
John Howard
William Cliff
. .
. .
. .

And I should export this dataset to different excel files by using keyvariable DOCTOR like this...

PROC EXPORT DATA=doctorset
OUTFILE="c:\data\Ann.xls" DBMS=EXCEL2000 REPLACE;

PROC EXPORT DATA=doctorset
OUTFILE="c:\data\William.xls" DBMS=EXCEL2000 REPLACE;

PROC EXPORT DATA=doctorset
OUTFILE="c:\data\John.xls" DBMS=EXCEL2000 REPLACE;

PROC EXPORT DATA=doctorset
OUTFILE="c:\data\xxxx.xls" DBMS=EXCEL2000 REPLACE;
run;

Can I use some kind of macro or something? The dataset is so large that I cannot use handjob...

Thank you for advance!

Re: Dataset to Excel sheets?

Chevell_sas — Thu, 11 May 2006 14:07:54 GMT

You could use the below macro to do this. It creates a list of macro variables from the variable doctor and loops through the PROC EXPORT statements. The value of doctor is resolved in both the WHERE= data set option as well as the OUTFILE=option.

You might also want to take a look at the ExcelXP tagset which allows you to generate multiple worksheets based on the by value as well. See the below for more information on this.

http://support.sas.com/rnd/base/topics/odsmarkup/

Ex1.

data one;
input DOCTOR $ PATIENT $;
cards;
Ann Mark
John Anton
Ann Jeff
Ann James
John Howard
William Cliff
;
run;

/* Create a list of macro variables */

proc sql noprint;
select count(distinct doctor) into :last
from one;

select distinct(doctor) into: val1- :val%left(&last)
from one;
run;
quit;

%macro loopit;
%do i=1 %to &last;

PROC EXPORT DATA=one(where=(doctor="&&val&i"))
OUTFILE="c:\data\&&val&i" DBMS=EXCEL2000 REPLACE;

run;
%end;
%mend;
%loopit

Ex2.

data one;
input DOCTOR $ PATIENT $;
cards;
Ann Mark
John Anton
Ann Jeff
Ann James
John Howard
William Cliff
;
run;

proc sort data=one;
by doctor;
run;
ods tagsets.excelxp file="temp.xls";

proc print data=one;
by doctor;
run;

ods tagsets.excelxp close;

Re: Dataset to Excel sheets?

deleted_user — Fri, 12 May 2006 06:14:14 GMT

Thank you Chevell!
Message was edited by: IlariSAS at May 12, 2006 2:14 AM