The SAS Output Delivery System and reporting techniques

How to generate multiple excel workbooks with ODS procedure

Reply
New Contributor
Posts: 4

How to generate multiple excel workbooks with ODS procedure

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 #

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

Esteemed Advisor
Posts: 6,685

Re: How to generate multiple excel workbooks with ODS procedure

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: How to generate multiple excel workbooks with ODS procedure

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 #

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

 

Super Contributor
Posts: 394

Re: How to generate multiple excel workbooks with ODS procedure

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.

New Contributor
Posts: 4

Re: How to generate multiple excel workbooks with ODS procedure

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.

Esteemed Advisor
Posts: 6,685

Re: How to generate multiple excel workbooks with ODS procedure

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
New Contributor
Posts: 4

Re: How to generate multiple excel workbooks with ODS procedure

Many thanks Kurt.

 

The code is doing what I want it to now.

 

Best wishes

Ask a Question
Discussion stats
  • 6 replies
  • 383 views
  • 0 likes
  • 3 in conversation