12月 222022

The addition of the PYTHON procedure and Python editor in SAS Viya enables users to execute Python code in SAS Studio. This new capability in SAS Viya adds another tool to SAS's existing collection. With this addition I thought, how can I utilize this new found power?

In this example, I'll keep it simple. I want to create a Microsoft Excel report using a combination of SAS, Python and SQL. I'll use data that's stored in a SAS library; however, the library could be using data stored anywhere, like a path, database or in the cloud. I'll write a program that executes the following:

All code used in this post is located on GitHub, here.

Set folder path and file name

To begin, I'll create a macro variable to specify the output folder path and Microsoft Excel workbook name.

%let path=/*Enter your output folder path*/;
%let xlFileName = myExcelReport.xlsx;

Prepare data

Further, I'll prepare the data using the SAS DATA step. I'll use the available sashelp.cars table, create a new column named MPG_Avg, and drop unnecessary columns. Instead of using the DATA step you can use Python or SQL to prepare the data. Whatever tool works best for you.

data work.cars;
    set sashelp.cars;
    MPG_Avg=mean(MPG_City, MPG_Highway);
    drop Wheelbase Weight Length;

Create the Microsoft Excel workbook

After the data is ready, I'll use the ODS EXCEL statement to create the Excel spreadsheet. The following ODS options are used:

  • FILE - specifies the file path and name.
  • STYLE - modifies the appearance of the SAS output
  • EMBEDDED_TITLES - specifies titles should appear in the worksheet
  • SHEET_INTERVAL - enables manual control when to create a new worksheet
ods excel file="&path./&xlFileName" 

Worksheet 1

Print the data using SAS

With the ODS EXCEL destination open I'll name the first worksheet Data, and manually specify when a new sheet is created. Next, I'll use the PRINT procedure to print the detailed data to Excel. The PRINT procedure will print the entire SAS data set with the associated formats and styles to Excel.

* Sheet 1 - Print the data using SAS *;
ods excel options(sheet_name='Data' sheet_interval='none');
title height=16pt color=white "Detailed Car Data";
proc print data=work.cars noobs;

Worksheet 2

Create violin plots using Python

Next, I want to create violin plots on a new worksheet named Origin_MPG. Now, these can be created in SAS, but I personally found the matplotlib package in Python a bit easier to use . With the PYTHON procedure, I can include the Python code within the SAS program (or you can reference a .py file) to create the visualization. Then I'll use the SAS.pyplot method to save and render the visualization. Since the pyplot callback renders the image in the results tab, it exports the image to the Excel workbook by default.

First I'll use ODS EXCEL to create the new worksheet and the TITLE statement to add a title to the Excel worksheet.

ods excel options(sheet_name='Origin_MPG' sheet_interval='now');
title justify=left height=16pt color=white "Analyzing MPG by Each Car Origin";

Then I'll execute the PYTHON procedure to execute my Python code.

* Create violin plots using Python *;
proc python;
## Import packages and options
import pandas as pd
import matplotlib.pyplot as plt
outpath = SAS.symget('path')
## Data prep for the visualization
## Load the SAS table as a DataFrame
df = (SAS
      .sd2df('work.cars')                 ## SAS callback method to load the SAS data set as a DataFrame
      .loc[:,['Origin','MPG_Avg']]        ## Keep the necessary columns
## Create a series of MPG_Avg for each distinct origin for the violin plots
listOfUniqueOrigins = df.Origin.unique().tolist()
mpg_by_origin = {}
for origin in listOfUniqueOrigins:
    mpg_by_origin[origin] = df.query(f'Origin == @origin ').MPG_Avg
## Create the violin plots
## Violin plot
fig, ax = plt.subplots(figsize = (8,6))
ax.violinplot(mpg_by_origin.values(), showmedians=True)
## Plot appearance
ax.set_title('Miles per Gallon (MPG) by Origin')
rename_x_axis = {'position': [1,2,3], 'labels':listOfUniqueOrigins}
## Save and render the image file
SAS.pyplot(plt, filename='violinPlot',filepath=outpath)

SQL Aggregation

SQL is an extremely common and useful language for data analysts and scientists. I find using SQL for aggregation easy, so I will create a simple aggregation and add it below the visualization on the same worksheet in the the Excel report.

* SQL Aggregation *;
title justify=left "Average MPG by Car Makes";
proc sql;
select Origin, round(mean(MPG_Avg)) as AverageMPG
	from work.cars
	group by Origin
	order by AverageMPG desc;

Add text

At the end of the same worksheet I'll add some simple text using the ODSTEXT procedure to give some information about the data.

proc odstext;
   heading 'NOTES';
   p 'Using the SASHELP.CARS data. The following car Origins were analyzed:';
   list ;
      item 'Asia';
      item 'Europe';
      item 'USA';
   p 'Created by Peter S';

Close the Excel workbook

Lastly, I'll close the ODS EXCEL destination since I am done writing out to Excel.

ods excel close;


That's it! Now I'll execute the entire program and view the Excel workbook.


With the capabilities of SAS and the new ability to execute Python code in SAS Studio, teams have a variety of tools in SAS Viya for their analytic needs.

Additional resources

PYTHON Procedure documentation
SAS opens its code editor interface to Python users
Using PROC PYTHON to augment your SAS programs
ODS Excel Statement

Creating a Microsoft Excel report using SAS, Python and SQL! was published on SAS Users.

5月 212015

When you weren't watching, SAS did it again. We smuggled Yet Another Excel Engine into a SAS release.

SAS 9.2 Maintenance 2 added the XLSX engine, which allows you to read and write Microsoft Excel files as if they were data sets in a library. The big advantage of using this engine is that it accesses the XLSX file directly, and doesn't use the Microsoft data APIs as a go-between. (LIBNAME EXCEL and LIBNAME PCFILES rely on those Microsoft components.) That means that you can use this engine on Windows or Unix systems without having to worry about bitness (32-bit versus 64-bit) or setting up a separate PC Files Server process.

The XLSX engine does require a license for SAS/ACCESS to PC Files. Are you a SAS University Edition user? The SAS/ACCESS product is part of that package, so this technique works there. It's an easy way to get well-formed Excel data into your SAS process.

/* because Excel field names often have spaces */
options validvarname=any;
libname xl XLSX '/folders/myfolders/sas_tech_talks_15.xlsx';
/* discover member (DATA) names */
proc datasets lib=xl; quit;
libname xl CLEAR;

Example output:

Once the library is assigned, I can read the contents of a spreadsheet into a new SAS data set:

/* because Excel field names often have spaces */
options validvarname=any;
libname xl XLSX '/folders/myfolders/sas_tech_talks_15.xlsx';
/* read in one of the tables */
data confirmed;
  set xl.confirmed;
libname xl CLEAR;

And here's the result in my SAS University Edition:

Sometimes you need just one value from a spreadsheet. That's a common use case for dynamic data exchange (DDE), which isn't as feasible as it once was. You can use FIRSTOBS and OBS options to control how much data you retain:

/* read in just one value */
data _null_;
  set xl.confirmed (firstobs=6 obs=6 keep='Job title'n);
  call symput('VALUE','Job Title'n);
%put &value;


 76         %put &value;
 Testing Manager,  Quality-driven User Experience Testing

You can also use the XLSX engine to create and update XLSX files.

libname xlout XLSX '/folders/myfolders/samples.xlsx';
data xlout.cars;
  set sashelp.cars;
data xlout.classfit;
  set sashelp.classfit;
data xlout.baseball;
  set sashelp.baseball;
data xlout.air;
  set sashelp.air;
libname xlout clear;

Here is my output in Microsoft Excel with all of these data sets now as sheets:

Remember, you can also create Microsoft Excel files with Base SAS by using ODS EXCEL -- experimental in 9.4 Maintenance 2 but production in Maintenance 3 (coming soon).

The XLSX libname is different from the EXCEL and PCFILES engines in other ways. For example, the XLSX engine does not support Excel named ranges (which can surface a portion of a spreadsheet as a discrete table). Also, you won't see the familiar "$" decoration around the spreadsheet names when they are surfaced in the library within SAS. If you need that sort of flexibility, you can use PROC IMPORT to provide more control over exactly what Excel content is brought into SAS and how.

One other IMPORTANT caution: The XLSX engine is a sequential access engine in that it processes data one record after the other. The engine starts at the beginning of the file and continues in sequence to the end of the file. Some techniques to MODIFY the data in-place will not work. Also, some SAS data viewers cannot render the data from the XLSX engine. SAS VIEWTABLE and SAS Enterprise Guide and even SAS Studio can't open these tables directly in the data grid view. VIEWTABLE gives you a nice message, but SAS Enterprise Guide simply "hangs" in the attempt. For that reason, I recommend using DATA step to copy the Excel content that you want to another SAS library, then CLEAR the XLSX library to avoid accidentally opening a table in a viewer that won't support it. (This is currently a bug in SAS Enterprise Guide that should be fixed in a future release.)

I have found LIBNAME XLSX to be a quick, convenient method to bring in Excel data on any SAS platform. If you have SAS 9.4 Maintenance 2 or later, try it out! Let me know how it works for you by sharing a comment here.

tags: excel, ODS EXCEL, SAS 9.4, SAS University Edition, xlsx

The post Using LIBNAME XLSX to read and write Excel files appeared first on The SAS Dummy.

9月 222014

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 */
  /* 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;
ods text="&bold_style.Count of friends by gender";
proc freq data=frienddetails
	tables gender / 
ods text="&bold_style.Calculated Ages based on Graduation years";
proc means data=ages
	min max mean median p99;
	var age;
	class how;
ods graphics on / width=800 height=300;
ods text="&bold_style.Count of friends by Relationship Status";
proc freq data=frienddetails
	tables relationshipstatus / 
	scores=table plots(only)=freqplot;
ods excel (id=fb) close;
/* ADDS new SHEETS to the existing XLSX file */
proc export data=frienddetails
  outfile=fbout replace;
  sheet="Friend Details";
proc export data=schoolfriends
  outfile=fbout replace;
proc export data=statusprep(keep=name date message)
  outfile=fbout replace;
  sheet="Latest Status";

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)

tags: excel, facebook, ODS EXCEL, PROC EXPORT, xlsx
8月 302014

The ODS ExcelXP tagset has served us well over the years. It provides a reliable method to get formatted SAS output into Microsoft Excel workbooks, where the business world seems to like to live. And it's available in Base SAS, which means that you don't need SAS/ACCESS to PC Files and any extra setup to begin using it.

In SAS 9.4 Maintenance 1, ODS EXCEL was introduced as an experimental feature. Even though it still has the "experimental" label in the recently released SAS 9.4M2, I've found it to be a useful addition to the many ways I can create Excel content from SAS. ODS EXCEL offers a couple of big advantages over ExcelXP:

ODS EXCEL produces a native XLSX file. Users of the ExcelXP tagset know that Excel complains about the file format as you open it. And the XML Spreadsheet format that is uses is uncompressed, resulting in potentially very large files.

ODS EXCEL supports SAS graphics in the output. ExcelXP users have come up with creative methods to insert graphs "after the fact", but it's not as convenient as a "once and done" SAS program. With ODS EXCEL, graphics from SAS procedures are automatically included in output.

Syntax-wise, ODS EXCEL is similar to ODS TAGSETS.ExcelXP. Chevell Parker shares many of those details in his SAS Global Forum 2014 paper; in absence of official doc for this experimental feature, Chevell's paper is essential.

Let's dive into an example. The following program looks similar to how you might use the ExcelXP tagset, but can you see the key differences?

ods excel file="c:projectsoutputexample.xlsx" 
 /* will apply an appearance style */
  /* for multiple procs/sheet */
  /* name the sheet tab */
  sheet_name="CARS summary"
/* add some formatted text */
ods escapechar='~';
ods text="~S={font_size=14pt font_weight=bold}~Cars Summary and Histogram";
/* tabular output */
proc means data=sashelp.cars;
var msrp invoice;
/* and a graph */
ods graphics / height=400 width=800 noborder;
proc sgplot data=sashelp.cars;
histogram msrp;
ods excel close;

Note how I've targeted an XLSX file directly, instead of going through an XML intermediary file to placate Excel with the file format. And I included a graph with no problem. Like other ODS output destinations, I can add apply styles and special formatting as needed. Here's a screen shot of the resulting spreadsheet:


I have a few additional observations from playing with this feature (did I say it was experimental?):

  • ODS EXCEL doesn't perform well with large detailed output. That is, if I use ODS EXCEL and PROC PRINT a data set with lots of columns and many thousands of rows, it's going to be slow -- and might hit a wall with memory usage. For that use case, PROC EXPORT DBMS=XLSX works better (though that requires SAS/ACCESS to PC FILES).
  • ODS EXCEL overwrites any existing Excel file that you target. That means that you aren't going to use this method to poke new values into an existing spreadsheet, or add sheets to an existing workbook. Compare that to PROC EXPORT DBMS=XLSX, which allows you to update an existing workbook by targeting just one sheet.
  • My example code works perfectly on the SAS University Edition! Simply change the destination file to be a location that SAS can write to -- for example, file="/folders/myfolders/example.xlsx".

If you want to learn more about the new ODS destinations in SAS 9.4 (which include HTML5, EPUB, POWERPOINT, and EXCEL), watch my interview and demo with David Kelley, R&D manager for ODS at SAS:

tags: excel, excel 2007, ods, ODS EXCEL, SAS 9.4, SAS University Edition