How to export multiple table using PROC PRINT?

[mrafael03]

Hello,

I need to know on how I will separate the tables into 2 separate tabs. The codes below only resulted into 1 tab

/*1st TAB w/ 2 tables */
ods excel options (sheet_interval="none" sheet_name='TAB1');
PROC PRINT DATA=TABLE1 NOOBS;
RUN;

PROC PRINT DATA=TABLE2 NOOBS;
RUN;

/*2nd TAB */
ods excel options (sheet_name='TAB2');
PROC PRINT DATA=R.PRGNT_LIST NOOBS;
RUN;

[SASKiwi]

ods excel file = "MyExcel.xlsx";
ods excel options (sheet_interval="none" sheet_name='TAB1');
PROC PRINT DATA=TABLE1 NOOBS;
RUN;

PROC PRINT DATA=TABLE2 NOOBS;
RUN;

/*2nd TAB */
ods excel options (sheet_interval="none" sheet_name='TAB2');
PROC PRINT DATA=R.PRGNT_LIST NOOBS;
RUN;
ods excel close;

[ketpt42]

That doesn't work for me. I get everything on one sheet if I do that. If I set the 2nd sheet_interval option to 'now', the 2nd sheet is created as expected.

[Tom]

Tell you want to start a new sheet.

ods excel filename='C:\downloads\msheets.xlsx';
ods excel options (sheet_interval="none" sheet_name='TAB1');
PROC PRINT DATA=sashelp.class (obs=4) NOOBS;
RUN;
PROC PRINT DATA=sashelp.class (firstobs=5 obs=10) NOOBS;
RUN;
ods excel options (sheet_interval='now' sheet_name='TAB2');
PROC PRINT DATA=sashelp.class(firstobs=11 obs=16) NOOBS;
RUN;
ods excel options (sheet_interval='none' );
PROC PRINT DATA=sashelp.class(firstobs=17) NOOBS;
RUN;
ods excel close;

 

[mrafael03]

what's the use of firstobs and obs?

[SASKiwi]

FIRSTOBS specifies the row to start reading from and OBS specifies the row to stop reading from.

[mrafael03]

The code isn’t working. Got an error on sheet_interval

[SASKiwi]

@mrafael03 -  Please post your SAS log.

[mrafael03]

I got an error from when I added sheets 3 and 4 codes

ERROR: Argument now for word option sheet_interval not recognized. Expecting one of these keywords output, table, page, bygroup,

bygroups, proc, none or none.

SHEET 1

ods excel file=""

options (sheet_interval="none"

sheet_name="TAB1");

PROC PRINT DATA=R.TBL1 NOOBS;

RUN;

PROC PRINT DATA=R.TBL2 NOOBS;

RUN;

PROC PRINT DATA=R.TBL3  NOOBS;

RUN;

----------------

SHEET 2

ods excel options(sheet_interval='now'

sheet_name="TAB2");

PROC PRINT DATA=R.TBL4 NOOBS;

RUN;

----------------

SHEET 3

ods excel options(sheet_interval="none"

sheet_name="TAB3");

PROC PRINT DATA=R.TBL5 NOOBS;

RUN;

----------------

SHEET 4

ods excel options(sheet_interval='now'

sheet_name="TAB3");

PROC PRINT DATA=R.TBL5 NOOBS;

RUN;

[andreas_lds]

---

@mrafael03 wrote:
The code isn’t working. Got an error on sheet_interval

---

Interesting, code works as expected using SAS 9.4m5. So, when you post the log as text, using the "insert code" button, please add information about the SAS version you are using.

[mrafael03]

I'm using SAS 9.4m4

[andreas_lds · Accepted solution]

The value "now" for sheet_interval has  been added with 9.4m5 - try changing "now" to "proc", if that doesn't help, you should consider upgrading the sas version to a more recent release.

[Tom]

---

@mrafael03 wrote:
what's the use of firstobs and obs?

---

So each PROC PRINT is printing different observations from SASHELP.CLASS to make it easier to check that the Excel file has the expected information.

They are called dataset options. Well the documentation calls them "data set" options but that is just because SAS documentation writers don't know how to spell dataset.

[Kurt_Bremser]

The ODS option should be

file=

not

filename=