Using ODS EXCEL and PROC EXPORT to bundle Excel-based reports

I recently wrote about my foray into the experimental world of ODS EXCEL. Using this new destination in SAS 9.4, I was able to create multisheet workbooks — containing reports and graphs — in the XLSX format. I outlined some of the benefits — such as being part of Base SAS and its ability to create native XLSX files. But I also pointed out some of the shortcomings that I experienced, such as slow performance with large amounts of data.

I’m working on a consulting project in which I needed to provide my client with an Excel-based report. This report requires an easy-to-read summary sheet, such as might be created with PROC MEANS and PROC TABULATE. That’s a perfect job for ODS EXCEL. But I also needed to include subsequent sheets that contained different versions of detailed data — tables with lots of columns and many thousands of rows. In my experience, this can bog down ODS EXCEL; I find that PROC EXPORT with DBMS=XLSX performs much better. So…I decided to see if I could combine the two approaches to create a single file.

The process worked perfectly for my purposes. It relies on the following behaviors/assumptions:

• ODS EXCEL creates a new XLSX file. That’s okay, because each time I run my process I want to replace the XLSX that I had before. If I wanted to retain previous versions, I could tack a date suffix onto the target file name.
• PROC EXPORT with DBMS=XLSX can add/update a sheet within an existing XLSX file. That’s a relatively recent feature, added in SAS 9.4 (and perhaps worked in SAS 9.3M2). This means that I can create the file with ODS EXCEL, then update the same file using PROC EXPORT, all within a single SAS program. (Remember, PROC EXPORT with DBMS=XLSX requires SAS/ACCESS to PC Files.)

I would like to show an example of the output, but I’m sure that my customer wouldn’t approve of me featuring their data details on my blog. So instead I’ll risk alienating a different group of acquaintances: my Facebook friends.

Years ago I built an app that turns your Facebook friend data into a SAS program. Even though eons have passed (in social media time), the app still works. (You can try it yourself: it runs as a custom task in SAS Enterprise Guide or as a standalone program on your Windows PC.) I ran it just now to gather my latest Facebook friend data and create SAS data sets with my friend details. Then I used ODS EXCEL to create a summary sheet, and PROC EXPORT to create several detailed sheets. Here’s an example of the summary:

And then one of the detailed sheets, produced by PROC EXPORT of one of the detailed data sets my Facebook program created:

Here’s the SAS program that produces this output. First, I run the program that is generated by my Facebook app. Then I follow up with this program that generates the Microsoft Excel spreadsheet file.

/* Create a new FB summary and report workbook for this snapshot 
   of Facebook data                                             */
filename fbout "c:tempFBReport_&SYSDATE..xlsx";
 
/* A little ODS style trick to make headings in my sheet */
ods escapechar='~';
%let bold_style=~S={font_size=12pt font_weight=bold}~;
 
/* CREATES a new XLSX file */
ods excel (id=fb) file=fbout
  /* choose a style you like */
  style=pearl
  /* SHEET_INTERVAL of NONE means that each PROC won't generate a 
     new sheet automatically                                     */
  options (sheet_interval="none" sheet_name="Summary")
  ;
 
ods noproctitle;
ods text="&SYSDATE. Friend Report for &myFacebookName";
proc sql;
     select count(distinct(UserId)) as 
         Number_Of_Friends into: NumberOfFriends
	 from friends;
quit;
 
ods text="&bold_style.Count of friends by gender";
proc freq data=frienddetails
	order=internal;
	tables gender / 
	nocum   
	scores=table;
run;
 
ods text="&bold_style.Calculated Ages based on Graduation years";
proc means data=ages
	min max mean median p99;
	var age;
	class how;
run;
 
ods graphics on / width=800 height=300;
ods text="&bold_style.Count of friends by Relationship Status";
proc freq data=frienddetails
	order=internal;
	tables relationshipstatus / 
	nocum   
	scores=table plots(only)=freqplot;
run;
 
ods excel (id=fb) close;
 
/* ADDS new SHEETS to the existing XLSX file */
proc export data=frienddetails
  dbms=xlsx
  outfile=fbout replace;
  sheet="Friend Details";
run;
 
proc export data=schoolfriends
  dbms=xlsx
  outfile=fbout replace;
  sheet="Schools";
run;
 
proc export data=statusprep(keep=name date message)
  dbms=xlsx
  outfile=fbout replace;
  sheet="Latest Status";
run;

I could achieve a similar result using other methods, of course. But I like to take advantage of my consulting opportunities to explore new features in SAS software. I find that if I can learn a new feature “on the job”, I can produce a good result for my customer while also adding to my bag of SAS tricks.

Related articles

Experimenting with ODS EXCEL to create spreadsheets from SAS
How do I export from SAS to Excel files: Let me count the ways
Running PROCs on Your Facebook Friends (2011 version)

The post Using ODS EXCEL and PROC EXPORT to bundle Excel-based reports appeared first on The SAS Dummy.