09-12-2016 10:45 AM
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.
09-12-2016 10:59 AM
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.
09-12-2016 11:18 AM
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:
Hence, it is putting everything in the same workbook. Do you know how this can be fixed?
Thank you very much in advance,
09-12-2016 12:38 PM
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.
09-12-2016 12:48 PM
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.
09-13-2016 02:11 AM
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;