BookmarkSubscribeRSS Feed
Todor
Calcite | Level 5

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

6 REPLIES 6
Kurt_Bremser
Super User

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.

Todor
Calcite | Level 5

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

 

Tim_SAS
Barite | Level 11

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.

Todor
Calcite | Level 5

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.

Kurt_Bremser
Super User

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;
Todor
Calcite | Level 5

Many thanks Kurt.

 

The code is doing what I want it to now.

 

Best wishes

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 2129 views
  • 0 likes
  • 3 in conversation