topic Re: How to generate multiple excel workbooks with ODS procedure in ODS and Base Reporting

How to generate multiple excel workbooks with ODS procedure

Todor — Mon, 12 Sep 2016 14:45:52 GMT

Hi All,

I am very new to SAS and I am stuck at trying to write a code (within a macro) which generates a seperate excel workbook for each outputed SAS dataset. I have managed to write a code which puts all of the datasets into a single workbook (in different tabs), but as outputs might get quite large the single workbook will become too heavy to work with. Here is the code that I have:

ODS TAGSETS.EXCELXP FILE=&PATH STYLE=STATISTICAL
OPTIONS(FROZEN_ROWHEADERS='YES' SHEET_NAME="SEL"
             AUTOFILTER='ALL');

%DO K=1 %TO &NUM;

PROC PRINT DATA= SEL&K;
RUN;

%END;

ODS TAGSETS.EXCELXP CLOSE;

The &NUM variable represents the final line of my conditions file (I have a conditions table with 1-30 rows e.g. it might have 10, 15, 5 rows etc.) so the code at the moment is telling SAS to create as many tabs in the excel workbook according to the number of rows in my conditions table. The above code is part of a bigger Macro.

Here is the code that I have in order to generate the NUM variable:

DATA _NULL_;
SET _CH1 END=EOF;
IF EOF THEN CALL SYMPUTX('NUM',_N_);
RUN;

The version of SAS that I am using is: SAS Enterprise Guide 7.1 (64-bit).

Any help on how to put each dataset in a seperate workbook instead of into a single workbook, so that i end up with 1 to n different workbooks will be much appreciated.

Kind regards,

Todor

Re: How to generate multiple excel workbooks with ODS procedure

Kurt_Bremser — Mon, 12 Sep 2016 14:59:17 GMT

In your first ODS statement, do not set a sheet name, instead set sheet_interval='none'.

Then, in your %do loop, use an additional ods tagsets.excelxp options() to set the sheet_name for each dataset.

Re: How to generate multiple excel workbooks with ODS procedure

Todor — Mon, 12 Sep 2016 15:18:54 GMT

Hi Kurt,

Many thanks for your response.

The code at the moment looks like that:

ODS TAGSETS.EXCELXP FILE=&PATH STYLE=STATISTICAL
OPTIONS(FROZEN_ROWHEADERS='YES' SHEET_INTERVAL='NONE'
             AUTOFILTER='ALL');

%DO K=1 %TO &NUM;

PROC PRINT DATA= SEL&K;RUN;
%END;

ODS TAGSETS.EXCELXP CLOSE;

I didn't put another ODS statment in the loop as it is not important the name of the sheet as there will be only one single sheet per workbook.

However, when I run the code I get everything in the same workbook, in a single worksheet, each simulation below the other.

I think this is due to the path variable which at the moment is set as follows:

path='****\Results\MULTITABLE1.XLS');

Hence, it is putting everything in the same workbook. Do you know how this can be fixed?

Thank you very much in advance,

Best wishes,

Todor

Re: How to generate multiple excel workbooks with ODS procedure

Tim_SAS — Mon, 12 Sep 2016 16:38:04 GMT

Each ODS TAGSETS.EXCELXP statement with the FILE= option creates a new workbook. Move both ODS TAGSETS.EXCELXP statements inside the macro loop and specify a different file name for each iteration of the loop.

Re: How to generate multiple excel workbooks with ODS procedure

Todor — Mon, 12 Sep 2016 16:48:36 GMT

Hi Tim,

Thank you very much for the suggestion. I am not very clear on how I can specify a different file name for each iteration of the loop. Some example code would be of great help.

Thanks in advance.

Re: How to generate multiple excel workbooks with ODS procedure

Kurt_Bremser — Tue, 13 Sep 2016 06:11:26 GMT

Remove the single quotes and the .xls extension from the declaration of the macro variable path.

Then move the ODS statements inside the %do loop, and do

ods tagsets excelxp file="&path&i..xls" .........;
/* output generating code */
ods tagsets.excelxp close;

Re: How to generate multiple excel workbooks with ODS procedure

Todor — Tue, 13 Sep 2016 07:42:50 GMT

Many thanks Kurt.

The code is doing what I want it to now.

Best wishes