topic Re: How to export multiple table using PROC PRINT? in SAS Programming

How to export multiple table using PROC PRINT?

mrafael03 — Thu, 16 Jul 2020 00:19:28 GMT

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;

Re: How to export multiple table using PROC PRINT?

SASKiwi — Thu, 16 Jul 2020 00:41:00 GMT

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;

Re: How to export multiple table using PROC PRINT?

ketpt42 — Thu, 16 Jul 2020 01:04:21 GMT

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.

Re: How to export multiple table using PROC PRINT?

Tom — Thu, 16 Jul 2020 01:10:56 GMT

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;

Re: How to export multiple table using PROC PRINT?

mrafael03 — Thu, 16 Jul 2020 02:05:34 GMT

what's the use of firstobs and obs?

Re: How to export multiple table using PROC PRINT?

SASKiwi — Thu, 16 Jul 2020 02:59:05 GMT

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

Re: How to export multiple table using PROC PRINT?

mrafael03 — Thu, 16 Jul 2020 03:32:09 GMT

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

Re: How to export multiple table using PROC PRINT?

Tom — Thu, 16 Jul 2020 04:08:02 GMT

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

Re: How to export multiple table using PROC PRINT?

SASKiwi — Thu, 16 Jul 2020 05:00:37 GMT

@mrafael03 - Please post your SAS log.

Re: How to export multiple table using PROC PRINT?

andreas_lds — Thu, 16 Jul 2020 05:20:09 GMT

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

Re: How to export multiple table using PROC PRINT?

Kurt_Bremser — Thu, 16 Jul 2020 08:00:50 GMT

The ODS option should be

file=

not

filename=

Re: How to export multiple table using PROC PRINT?

mrafael03 — Fri, 17 Jul 2020 01:15:53 GMT

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;

Re: How to export multiple table using PROC PRINT?

mrafael03 — Fri, 17 Jul 2020 01:19:40 GMT

I'm using SAS 9.4m4

Re: How to export multiple table using PROC PRINT?

andreas_lds — Fri, 17 Jul 2020 04:41:50 GMT

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.