CODEWITHSUNDEEP
excelsasdatasetexport
Vikas Bairy
Vikas Bairy

Reputation: 9

How to export 2 datasets in to excel “Sheet1”and another set of 2 datasets into “Sheet2” using SAS

Good Morning! I want to export a few dataset's to excel using SAS, But I have a few challenges here. Both datasets are unique and which we cannot merge or append.

There are 2 programs that create 2 dataset's each, however, I want to export program1 output datasets(2 datasets) to excel sheet1 and Program2 output dataset'(2 datasets) to Excel sheet2.

I tried using Proc print but it didn't work

    ods excel file="&OUTFILE." options(sheet_interval="none"  sheet_name="sheet1");

TITLE 'CLOSED_SR_VOLUMES_BY_PERCENT';
proc print data=CLOSED_SR_VOLUMES_BY_PERCENT;
run;
TITLE 'CLOSED_SR_VOLUMES_BY_VOLUME';
proc print data=CLOSED_SR_VOLUMES_BY_MONTH;
run;


ods excel options(sheet_interval="none" sheet_name="sheet2");
TITLE 'ACTIVE_SR_VOLUMES_BY_VOLUME';
proc print data=SR_VOLUMES_BY_MONTH;
run;
TITLE 'ACTIVE_SR_VOLUMES_BY_PERCENT';
proc print data=SR_VOLUMES_BY_PERCENT;
run;
ods excel close;

I am not getting any error but all the datasets are exporting it to one excel sheet instead 2 datasets into sheet1 and another 2 datasets into Sheet2.

Please let me know if there any way to this...

Upvotes: 0

Views: 491

Answers (2)

Joe
Joe

Reputation: 63424

If you have the most recent maintenance release of SAS, you can do this with a small modification:

   ods excel file="&OUTFILE." options(sheet_interval='none' sheet_name="sheet1");

TITLE 'CLOSED_SR_VOLUMES_BY_PERCENT';
proc print data=sashelp.class;
run;
TITLE 'CLOSED_SR_VOLUMES_BY_VOLUME';
proc print data=sashelp.class;
run;


ods excel options(sheet_interval='now' sheet_name="sheet2");
TITLE 'ACTIVE_SR_VOLUMES_BY_VOLUME';
proc print data=sashelp.class;
run;
TITLE 'ACTIVE_SR_VOLUMES_BY_PERCENT';
proc print data=sashelp.class;
run;
ods excel close;

Note I add the 'now' instead of 'none' in the second one. That seems to be necessary to force a sheet change.

This doesn't work with early versions of ODS EXCEL, but either in 9.4 TSM3 or TSM4 I believe it was fixed. I have TSM6 and can confirm it works on that.

Upvotes: 1

Serhii Omelchuk
Serhii Omelchuk

Reputation: 41

At first look seems like a known ods bug, so u can try to investigate it. As an easier solution, i would suggest to use DATA STEP to export data using LIBNAME :

libname xllib xlsx "&outpath/filename.xlsx";
data xllib.sometabname;
 set sashelp.cars;
 run;
libname myxl clear;

This code extracts data from sashelp.cars and writes it to the filename workbook on a tab named sometabname.

Upvotes: 1

Related Questions