PROC EXPORT: SAS-data-sets to Excel 2000

[karlomutschler]

good Day,

I wish you and your relatives a happy and prosperous 2005.

In my script I create 3 different SAS datasets
DATA=WORKUNIX.MonthlySum
DATA=WORKUNIX.SalesChannelMonth
DATA=WORKUNIX.SalesRegionsMonth

which I export separately to excel using the following PROC EXPORT:

PROC EXPORT DATA= WORKUNIX.SAS-data-set
OUTFILE= "C:\Path\filename.xls"
DBMS=EXCEL2000 REPLACE;
RUN;

However I wish to export the THREE datasets to ONE excel file and
place the reports on different tabs, one tab for each report, i.e.
DATA=WORKUNIX.MonthlySum on MoSum_Tab
DATA=WORKUNIX.SalesChannelMonth on Channels_Tab
DATA=WORKUNIX.SalesRegionsMonth on Regions_Tab

Any assistance would be highly appreciated.
Kind regards
Karlo

 

[burndengreen]

Karlo

Best wishes but i think you may be out of luck. According to the SAS institute web site :-
In SAS 8.2, using PROC EXPORT to write a SAS data set to an Excel file,
and that Excel file exists, the entire Excel file is replaced when code
similar to this is used:

PROC EXPORT DATA= WORK.TEST
OUTFILE= "c:\my documents\mydata.xls"
DBMS=EXCEL2000 REPLACE;
RUN;


Beginning with SAS 9.0, the Export Procedure supports writing to a
specific worksheet in an existing Excel workbook file using the
following code:

PROC EXPORT DATA= SASHELP.Class
OUTFILE= "C:\export class.xls"
DBMS=EXCEL REPLACE;
SHEET="class";
RUN;

So Unless u have SAS 9.0 I think you will have to write seperate spreadsheets and the amalgamate them manually into one.

Cheers.

 

[ChrisW75]

SAS Version 9 allows you to do it definitely, I thought you could do something similar with Version 8 though, using ODBC, treating the spreadsheet as a library and the worksheets as datasets....

 

[mplee]

assuming you have version 9

%macro export(dsn,tab);
PROC EXPORT DATA= &dsn
OUTFILE= "C:\Path\filename.xls"
DBMS=EXCEL2000 REPLACE;
RUN;
%mend;

then to execute

%export(WORKUNIX.MonthlySum,MoSum_Tab);

i hope this helps.

** mp **

 

[mplee]

whoops! i forgot a critical line in the above.

%macro export(dsn,tab);
PROC EXPORT DATA= &dsn
OUTFILE= "C:\Path\filename.xls"
DBMS=EXCEL2000 REPLACE;
sheet="&tab";
RUN;
%mend;

i hope _this_ helps.

** mp **