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

Question

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...

What version of SAS do you have? This was a known bug in 9.4M2 but was fixed in 9.4M3 afaik. There is a known workaround though, but it depends on your version. — Reeza
– Reeza, Commented May 21, 2019 at 14:56
@SolarMike So that people can search for questions that contain both tags? — Robert Penridge
– Robert Penridge, Commented May 21, 2019 at 21:45
@RobertPenridge so looking for excel based questions to answer, people get that and it’s not relevant... — Solar Mike
– Solar Mike, Commented May 22, 2019 at 3:34
@SolarMike You can easily exclude questions that contain both the sas and excel tags using this search query: [excel] -[sas]. Alternatively you can read the title of the question (this one explicitly states 'using SAS' as part of the title), or look at the tags before clicking into the question. Of the 195k questions on SO that contain an excel tag, only 1k of them also contain a sas tag. So ignoring one question out of every 200 shouldn't be too much of an ordeal regardless. Personally, I still think the upside of listing both tags outweighs the downsides. — Robert Penridge
– Robert Penridge, Commented May 22, 2019 at 18:56

Serhii Omelchuk · Accepted Answer · 2019-05-21 13:57:29Z

1

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.

answered May 21, 2019 at 13:57

Serhii Omelchuk

414 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Joe · Accepted Answer · 2019-05-21 17:26:14Z

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.

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related