Page 1 of 16
SAS® Output to Excel, So Many Choices, What Do I Do?
Steve Rhoades & Cathryn Ooro, IMS Health Plymouth Meeting, Pa
ABSTRACT:
SAS gives you a lot of choices for getting your data into Excel and it can get
confusing pretty quickly. You can create a variety of files such as CSV, HTML,
XML and XLS via SAS using Data _NULL_, ODS or Proc Export. So are there any
other choices? Well your other choices within a workbook are a single spreadsheet,
multiple worksheets or multiple outputs in a single sheet.
And of course there are differences between the mainframe and PC. This presents
some challenges when the data is on the mainframe. So what do you need to do in
these cases?
In our shop there is not much time for learning so we prefer seeing the input, output
and the technical details. It tends to speed the development process. For the poster,
the flow will be the same to reduce the learning curve. Now you can really get into
this by the use of templates which is a topic in itself. However, there will be some
references to it here.
INTRODUCTION:
Your boss calls you in giving you new specs for a project with a very tight deadline,
sound familiar? Let’s make it easy, this is an internal report so it does not have to
be as pretty as one going to clients. Still it may need to be created on the mainframe
or Windows, there are other options but not in this paper. Do we produce one
workbook with a single worksheet, or multiple worksheets within a single workbook
or multiple outputs in a single worksheet? Other questions, do we need style sheets
or post processing and how do we deliver the spreadsheets. We often use email and
SAS does have a facility to do that. This paper will have a number of examples to
help you quickly see what is needed.
PostersNESUG 2009
Page 2 of 16
SYNOPSIS OF EXAMPLES CONTAINED IN THIS PAPER
Ex 1: Mainframe create a single workbook with multiple Excel worksheets and
email it
Ex 2: Mainframe create a single workbook and with multiple Excel (HTML)
Outputs to the same worksheet
Ex 3: Mainframe create multiple workbooks with output like Ex 2, create a second
workbook.
Ex 4: Windows create a single workbook with multiple Excel worksheets and email
it.
Ex 5: Windows create a single workbook and with multiple Excel (HTML) Outputs
to the same worksheet.
Ex 6: Insert leading spaces and percents into an Excel spreadsheet.
EXAMPLE 1 – Mainframe processing to create a Workbook with multiple worksheets.
This example reads in data and creates three different worksheets within a
workbook. It will also show you how to automatically email them
EXAMPLE 1
JCL PIECES NEEDED FOR THE EXCEL FILE The IEFBR14 utility below is creating a file structure to contain the Excel spreadsheets. Note particularly the LIBRARY, RECFM and DSORG statements. //S05A EXEC PGM=IEFBR14
//O1PDSE DD DSN=STS.SJR.NESUG001.W05209,
// DISP=(NEW,CATLG),DSNTYPE=LIBRARY,
// SPACE=(27998,(50,10),RLSE),UNIT=SYSDA,
// DCB=(LRECL=8196,BLKSIZE=27998,RECFM=VB,DSORG=PO),
// LABEL=RETPD=360
//*
The file created by the IEFBR14 program is used here to write out the Excel files, it was done this way to prevent job errors. I1IN is the main data file and the I4EMAIL file specifies who gets the emailed spreadsheet. //S10A EXEC SASP
//I1IN DD DSN=STS.PFR06081.S05A3.FACTORNS.W05209,DISP=SHR
//I4EMAIL DD DSN=H704769.IIRS.TEXT(PFR83EML),DISP=SHR
//O1PDSE DD DSN=STS.SJR.NESUG001.W&MMWYY,DISP=SHR
CONTENTS OF I4EMAIL FILE TO [email protected] Sending to one person
FROM/SENDER [email protected]
Alternate style using an Outlook Distribution list TO [email protected]
FROM/SENDER [email protected]
PostersNESUG 2009
Page 3 of 16
PRINT OF MAIN DATA FILE CONTENTS (I1IN) AvgPF_Per_
NS_CHANNEL_ Wk CMF7_
OUTLETCTR TYPE CMF7 FACTOR Date
ChannelWkly
25543 C 1111111 20650.09 09052 0.80844
25564 C 1111112 20662.97 09052 0.80828
25577 C 1111113 20603.59 09052 0.80555
25529 C 1111114 20846.14 09052 0.81657
25413 C 1111115 20788.94 09052 0.81804
25346 C 1111116 20918.81 09052 0.82533
25488 C 1111117 20562.45 09052 0.80675
25527 C 1111118 20613.03 09052 0.80750
PROGRAM /* Read in Email information and put it in macro values*/
Data _NULL_;
INFILE I4EMAIL ;
Input @1 Asterisk $1. /*Used to indicate a comment line*/
@1 ToAddr $Char02.
@1 From $Char04.
@13 EmailAddr $Char60.
;
If Asterisk='*' Then Delete;
Else
If upcase(ToAddr) ='TO' Then
Call Symput('ToAddr',Trim(EmailAddr));
Else
If upcase(From) ='FROM' Then
Call Symput('FromAddr',Trim(EmailAddr));
Run;
ODS LISTING CLOSE; Close the listing window
ODS TAGSETS.EXCELXP body="W&CurWkJCL" &CURWkJCL contains mmwyy date
PATH =O1PDSE(URL=NONE) O1PDSE=mainframe file for Excel
RECORD_SEPERATOR=NONE;
RUN;
PROC PRINT DATA=o2sas.WklyData NOOBS; Write Excel Spreadsheet
RUN;
PROC PRINT DATA=o2sas.Flagged NOOBS; Write Excel Spreadsheet
RUN;
Proc Print data=TtlOfProjFactsByNSChan NOOBS;Write Excel Spreadsheet
Run;
ODS TAGSETS.EXCELXP CLOSE; Close the Excel output
ODS LISTING ; Write Excel Spreadsheet
filename test email
"&ToAddr"
subject='From mainframe job ABC'
FROM=("<&FromAddr >")
SENDER=("<&FromAddr >") &FNAME=STS.SJR.NESUG001
attach =("&FName..W&CurWkJCL(W&CurwkJCL)" extension='xls'
CT="text/xml");
data _null_;
PostersNESUG 2009
Page 4 of 16
file test;
put 'at end of procedure';
run;
CONTENTS OF EMAIL
Contents of the W05209.xls spreadsheet after opening. Notice the three separate tabs.
EXAMPLE 2 - Mainframe create a single workbook and put Excel Outputs in the same Excel worksheet
In this example two outputs of a Proc Print are written to the same worksheet.
Output is not limited to Proc Print there are a number of choices for output. Before
getting started there are a few points that will be covered. The O1PDSE file will still
need to be created, see Example 1 for how to do this. The ODS code writes output to
an HTML file that is downloaded to a PC directory using FTP BINARY data
transfer. From within Excel the file is opened, fortunately Excel can read HTML
code directly.
PostersNESUG 2009
Page 5 of 16
EXAMPLE 2 JCL Pieces You will need a PDSE file, See Example 1 on how to create
one.
//S10A EXEC SASP
//O1PDSE DD DSN=STS.SJR.NESUG002.W&MMWYY,DISP=SHR
DATA - In this example the data is created using INFILE DATALINES.
PROGRAM (NOTE: This is a partial print, the key part is the Proc Prints are inside the ODS code and it is going to HTML) DATA WklyData WkDate (Keep=WkDate) ;
INFILE Datalines ;
INPUT @1 OUTLETCTR 2.
@3 NS_CHANNEL_TYPE $1.
@4 CMF7 $CHAR07.
@11 FACTOR 4.2
@15 Wkmm $Char02.
@17 Wkw $Char01.
@18 WKyy $CHAR02.
;
WkDate=Compress(WKYY || WKMM || WkW);
AvgPF_Per_CMF7_ChannelWkly = Factor / OutletCtr;
OUTPUT;
Drop WKYY WKMM WkW;
Datalines;
02W1111511442205209
22W1111101942205209
;
ODS LISTING CLOSE; Close Listing Window
ODS HTML Open HTML WINDOW
STYLE=MINIMAL
BODY="WklyData" (URL="WklyData.HTML") Write to HTML
PATH =O1PDSE(URL=NONE)
RECORD_SEPERATOR=NONE
TRANTAB=ASCII;
RUN;
Proc Print data=WklyData (Obs=&PrntObs);
Title "Print of weekly data file";
Run;
Proc Print data=WKDate (Obs=&PrntObs);
Title "Print of week date file ";
Run;
ODS TAGSETS.HTML CLOSE; Close HTML Window
ODS LISTING ; Open HTML Window
PostersNESUG 2009
Page 6 of 16
After the job runs,WklyData file is created
Next download the file
Notice I added the HTML suffix to the file on the PC, then open Excel and read in the file: Contents of the W05209.HTML spreadsheet after opening. Notice the two Prints within a single Worksheet.
PostersNESUG 2009
Page 7 of 16
Two Outputs within a single worksheet.
EXAMPLE 3 - Mainframe putting two Excel Outputs in one worksheet and create a second workbook containing a single worksheet.
This example starts just like Example 2, this time the ODS process uses Proc Print
to write two files to one workbook, closes and then opens a second time to create
another workbook. Output is not limited to Proc Print there are a number of
choices for output. Before getting started there are a few points that will be
covered. The O1PDSE file will still need to be created, see Example 1 for how to do
this. The ODS code writes output to an HTML file that is downloaded to a PC
directory using FTP BINARY data transfer. From within Excel, click Open and
navigate to the folder and file. Highlight the file and double click to open it in Excel.
PostersNESUG 2009
Page 8 of 16
EXAMPLE 3 JCL Pieces You will need a PDSE file, See Example 1 or 2.
DATA - If you can create data for Proc Print, Report, Freq among others, you can use this technique.
PROGRAM (NOTE: The output data files are being used in the Proc Prints within the ODS code) DATA WklyData WkDate (Keep=WkDate)
ANOTHERFILE (KEEP= NS_CHANNEL_TYPE CMF7); Creating a third file
INFILE Datalines ;
INPUT @1 OUTLETCTR 2.
@3 NS_CHANNEL_TYPE $1.
@4 CMF7 $CHAR07.
@11 FACTOR 4.2
@15 Wkmm $Char02.
@17 Wkw $Char01.
@18 WKyy $CHAR02.
;
WkDate=Compress(WKYY || WKMM || WkW);
AvgPF_Per_CMF7_ChannelWkly = Factor / OutletCtr;
OUTPUT;
Drop WKYY WKMM WkW;
Datalines;
02W1111511442205209
22W1111101942205209
;
/* This portion is the same as example 1 other than body names were changed*/ ODS LISTING CLOSE; Close Listing Window
ODS HTML Open HTML WINDOW
STYLE=MINIMAL
BODY="SPRD1"(URL="SPRD1.HTML")Write to HTML
PATH =O1PDSE(URL=NONE)
RECORD_SEPERATOR=NONE
TRANTAB=ASCII;
RUN;
Proc Print data=WklyData (Obs=&PrntObs);
Title "Print of weekly data file";
Run;
Proc Print data=WKDate (Obs=&PrntObs);
Title "Print of week date file ";
Run;
ODS TAGSETS.HTML CLOSE; Close HTML Window
ODS LISTING ; Open HTML Window
ODS HTML CLOSE;
PostersNESUG 2009
Page 9 of 16
Run;
/* This part is new, notice the (2) */
ODS HTML (2) Create another workbook
STYLE=DEFAULT
BODY="BODY2"(URL="SPRD2.HTML")
PATH=O3PDSE(URL=NONE)
RECORD_SEPERATOR=NONE
TRANTAB=ASCII;
Proc Print data=AnotherFile NOOBS;
TITLE 'PRINT OF ANOTHER FILE';
Run;
ODS HTML (2) CLOSE ; Close the 2nd workbook
Run;
For additional workbook creation, keep incrementing ODS HTML.
Example:
ODS HTML (3)
.
.
ODS HTML (3) CLOSE ; Close the 3nd workbook
Run;
After the job runs, two HTML files are created
Next download the files and add an HTML file suffix.
PostersNESUG 2009
Page 10 of 16
Contents of SPRD1.HTML spreadsheet after opening in Excel contains two outputs within a single Worksheet.
Contents of the second workbook created on the mainframe is in Excel
PostersNESUG 2009
Page 11 of 16
EXAMPLE 4 – Windows Processing to create a Workbook with multiple worksheets.
This example reads in data and creates two different worksheets within a workbook.
It will also show you how to automatically email them.
EXAMPLE 4
PROGRAM DATA UNIV1;
INPUT @1 SPEC $CHAR3.
@4 DESC $CHAR12.
@16 COUNT 1.
@17 REGION $CHAR1.
;
CARDS;
DRMDERMATOLOGY 2N
FP FAMILY PRAC 2N Partial input
FPPFP-PART TIME2N
GE GASTRO 3N
GERGERIATRICS 3N
ODS LISTING CLOSE; Close Listing Window
ODS TAGSETS.EXCELXP
options(sheet_name="Table 3" embedded_titles='yes'
suppress_bylines='yes') Include tab names etc
PATH="&DIR"
FILE="TEMP1.XLS"
STYLE=STYLES.TEST;
/* Writing this to Excel Spreadsheet */
PROC TABULATE DATA=UNIV1 ORDER=DATA;
CLASS SPEC DESC REGION;
VAR COUNT;
FORMAT REGION $REGION.;
TABLE DESC ALL, REGION*COUNT=' '*((SUM)*F=COMMA8.0)
ALL*COUNT=' '*(SUM='UNIVERSE'*F=COMMA8.0 PCTSUM*F=5.1)/RTS=25;
LABEL DESC='GROUP SPECIALTY';
KEYLABEL ALL='TOTAL'
SUM=' '
PCTSUM='%';
TITLE1;
TITLE2 "2009 UNIVERSE COUNTS BY SPECIALTY AND REGION";
RUN;
ODS TAGSETS.EXCELXP
options(sheet_name="Table 4" embedded_titles='yes'
suppress_bylines='yes'); Include tab names etc
/* Write Excel Spreadsheet */
PROC TABULATE DATA=ONE;
CLASS SPEC REGION;
PostersNESUG 2009
Page 12 of 16
VAR DESIGN;
FORMAT REGION $REGION.;
TABLE SPEC ALL, REGION*DESIGN=' '*((SUM=' ')*F=COMMA10.2)
ALL* DESIGN=' '*(SUM='UNIVERSE'*F=COMMA10.2)/RTS=20 ;
LABEL SPEC='GROUP SPECIALTY' DESIGN='SAMPLE DESIGN';
KEYLABEL ALL='TOTAL';
TITLE1 'QUARTERLY SAMPLE DESIGN BY SPECIALTY AND REGION';
RUN;
ODS TAGSETS.EXCELXP CLOSE; Open Listing Window
ODS LISTING; Write Excel Spreadsheet
FILENAME ALL
EMAIL '[email protected]'
SUBJECT='from PC NTI JOB'
FROM=("<[email protected]>")
SENDER=("<[email protected]>")
ATTACH= ("&DIR.\TEMP1.XLS”) ;
DATA _NULL_;
FILE ALL;
PUT 'NTI Reports';
RUN;
Contents of the TEMP1.xls spreadsheet after opening. Notice the two separate tabs.
PostersNESUG 2009
Page 13 of 16
EXAMPLE 5: Windows creating a single workbook and putting two Excel Outputs in the same worksheet
In this example, the two outputs of a Proc Tabulate are written to the same
worksheet.
EXAMPLE 5 PROGRAM
ODS LISTING CLOSE; Close LISTING WINDOW
ODS HTML BODY="&DIR.\TEMP2.XLS"; Open HTML WINDOW
TITLE "<TD ALIGN CENTER COLSPAN=7>
2009 UNIVERSE COUNTS BY SPECIALTY AND REGION Center TITLE
</TD>";
/* Write For Excel Spreadsheet using HTML */
PROC TABULATE DATA=UNIV1 ORDER=DATA;
CLASS SPEC DESC REGION;
VAR COUNT;
FORMAT REGION $REGION.;
TABLE DESC ALL, REGION*COUNT=' '*((SUM)*F=COMMA8.0)
ALL*COUNT=' '*(SUM='UNIVERSE'*F=COMMA8.0 PCTSUM*F=5.1)/RTS=25;
LABEL DESC='GROUP SPECIALTY';
KEYLABEL ALL='TOTAL'
SUM=' '
PCTSUM='%';
RUN;
TITLE "<TD ALIGN CENTER COLSPAN=7>
QUARTERLY SAMPLE DESIGN BY SPECIALTY AND REGION Center TITLE
</TD>";
/* Write For Excel Spreadsheet using HTML */
PROC TABULATE DATA=ONE;
CLASS SPEC REGION;
VAR DESIGN;
FORMAT REGION $REGIONX.;
TABLE SPEC ALL, REGION*DESIGN=' '*((SUM=' ')*F=COMMA10.2)
ALL*DESIGN=' '*(SUM='UNIVERSE'*F=COMMA10.2)/RTS=20;
LABEL SPEC='GROUP SPECIALTY' DESIGN='SAMPLE DESIGN';
KEYLABEL ALL='TOTAL';
RUN;
ODS HTML CLOSE; Close HTML WINDOW
ODS LISTING; Open LISTING WINDOW
PostersNESUG 2009
Page 14 of 16
EXAMPLE 6: Insert leading zeroes and percents
In this example, the tagattr Tag is used to do this. Notice the various VAR
statements that are used to line up the output columns
EXAMPLE 6 PROGRAM ODS LISTING CLOSE;
run;
ODS NOResults;/*Stop filling up Results window */
ODS TAGSETS.EXCELXP PATH="&O1WBLIB" FILE="COVERAGETOOLS RETAIL
&CURQTR..XML" STYLE=XLSANSPRINTER;
PostersNESUG 2009
Page 15 of 16
PROC PRINT DATA=RTLUSC NOOBS LABEL SPLIT="*" ;
VAR USC5 /STYLE(DATA)={TAGATTR="FORMAT:@"};
FORMAT USC5 Z5.;
VAR USC_DESC;
VAR &CURQTR._TRX_COVERAGE /STYLE(DATA)={TAGATTR="FORMAT:0%"};
VAR &PRVQTR._TRX_COVERAGE /STYLE(DATA)={TAGATTR="FORMAT:0%"};
VAR COVERAGEDIFF /STYLE(DATA)={TAGATTR="FORMAT:0%"};
VAR FLAG INCDEC;
TITLE1 "&EXCELT1RUSC5 FOR &CURQTR ";
TITLE2 "&EXCELT2RUSC5";
ODS TAGSETS.EXCELXP OPTIONS(SHEET_NAME="RTL USC5 LVL");
RUN; QUIT;
ODS TAGSETS.EXCELXP CLOSE;
CONCLUSIONS:
There are a number of ways to create Excel files with SAS ODS. This paper has
shown examples from both the mainframe and Windows to create a single
workbook with multiple spreadsheets (and an email example), a single workbook
with multiple outputs sent to the same worksheet and the creation of multiple
workbooks in a single job step. This as a quick guide to see what syntax is required
to get this output. If you want to customize the output, take a look at templates that
will help with fonts, colors and formatting.
PostersNESUG 2009
Page 16 of 16
REFERENCES & ACKNOWLEDGEMENTS:
Parker, Chevell. “Generating Custom Excel® Spreadsheets Using ODS.”
Proceeding of Twenty-Eighth Annual SAS® Users Group International Conference.
Mosely Mercer, Wanda. “Multiple Multi-Sheet Excel Workbooks Using ODS.”
LABS, June, 2004.
Parker, Chevell. “Using ODS to Generate Excel Files.” SAS Institute, Inc., 2003.
Gebhart, Eric. “The Devil is in the Details: Styles, Tips, and Tricks That Make Your
Microsoft Excel Output Look Great!” SAS Global Forum 2008.
DelGobbo, Vincent. “Tips and Tricks for Creating Multi-Sheet Microsoft Excel
Workbooks the Easy Way with SAS®.” NESUG 2008.
Cohen, Mathew. “Any Data Set to Excel, Dynamically.” NESUG 2007.
CONTACT INFORMATION:
Steve Rhoades / Cathryn Ooro
IMS HEALTH Inc.
660 W. Germantown Pike
Plymouth Meeting, Pa. 19462
Work Phone: Steve-(610) 834-4614 Cathryn-(610) 834-4470
Email: [email protected]
SAS and all other SAS Institute Inc. product or service names are registered trademarks or
trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.
Other brand and product names are registered trademarks or trademarks of their respective
companies.
PostersNESUG 2009