Making a Microsoft Excel report utilizing SAS, Python and SQL!

The addition of the PYTHON process and Python editor in SAS Viya permits customers to execute Python code in SAS Studio. This new functionality in SAS Viya provides one other instrument to SAS’s current assortment. With this addition I assumed, how can I make the most of this new discovered energy?

On this instance, I am going to hold it easy. I need to create a Microsoft Excel report utilizing a mixture of SAS, Python and SQL. I am going to use knowledge that is saved in a SAS library; nonetheless, the library could possibly be utilizing knowledge saved anyplace, like a path, database or within the cloud. I am going to write a program that executes the next:

All code used on this publish is situated on GitHub, right here.

Set folder path and file title

To start, I am going to create a macro variable to specify the output folder path and Microsoft Excel workbook title.

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

Put together knowledge

Additional, I am going to put together the info utilizing the SAS DATA step. I am going to use the out there sashelp.automobiles desk, create a brand new column named MPG_Avg, and drop pointless columns. As an alternative of utilizing the DATA step you should utilize Python or SQL to arrange the info. No matter instrument works greatest for you.

knowledge work.automobiles;
    set sashelp.automobiles;
    MPG_Avg=imply(MPG_City, MPG_Highway);
    drop Wheelbase Weight Size;
run;

Create the Microsoft Excel workbook

After the info is prepared, I am going to use the ODS EXCEL assertion to create the Excel spreadsheet. The next ODS choices are used:

• FILE – specifies the file path and title.
• STYLE – modifies the looks of the SAS output
• EMBEDDED_TITLES – specifies titles ought to seem within the worksheet
• SHEET_INTERVAL – permits handbook management when to create a brand new worksheet

ods excel file="&path./&xlFileName" 
		  model=ExcelMidnight   
		  choices(embedded_titles="on");

Worksheet 1

Print the info utilizing SAS

With the ODS EXCEL vacation spot open I am going to title the primary worksheet Information, and manually specify when a brand new sheet is created. Subsequent, I am going to use the PRINT process to print the detailed knowledge to Excel. The PRINT process will print your complete SAS knowledge set with the related codecs and kinds to Excel.

* Sheet 1 - Print the info utilizing SAS *;
ods excel choices(sheet_name='Information' sheet_interval='none');
title peak=16pt coloration=white "Detailed Automobile Information";
proc print knowledge=work.automobiles noobs;
run;

Worksheet 2

Create violin plots utilizing Python

Subsequent, I need to create violin plots on a brand new worksheet named Origin_MPG. Now, these will be created in SAS, however I personally discovered the matplotlib bundle in Python a bit simpler to make use of . With the PYTHON process, I can embrace the Python code inside the SAS program (or you possibly can reference a .py file) to create the visualization. Then I am going to use the SAS.pyplot methodology to avoid wasting and render the visualization. For the reason that pyplot callback renders the picture within the outcomes tab, it exports the picture to the Excel workbook by default.

First I am going to use ODS EXCEL to create the brand new worksheet and the TITLE assertion so as to add a title to the Excel worksheet.

ods excel choices(sheet_name='Origin_MPG' sheet_interval='now');
title justify=left peak=16pt coloration=white "Analyzing MPG by Every Automobile Origin";

Then I am going to execute the PYTHON process to execute my Python code to create the violin plot.

* Create violin plots utilizing Python *;
proc python;
submit;
 
##
## Import packages and choices
##
 
import pandas as pd
import matplotlib.pyplot as plt
plt.model.use('fivethirtyeight')
outpath = SAS.symget('path')
 
##
## Information prep for the visualization
##
 
## Load the SAS desk as a DataFrame
df = (SAS
      .sd2df('work.automobiles')                 ## SAS callback methodology to load the SAS knowledge set as a DataFrame
      .loc[:,['Origin','MPG_Avg']]        ## Maintain the mandatory columns
)
 
 
## Create a sequence of MPG_Avg for every distinct origin for the violin plots
listOfUniqueOrigins = df.Origin.distinctive().tolist()
 
mpg_by_origin = {}
for origin in listOfUniqueOrigins:
    mpg_by_origin[origin] = df.question(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 look
ax.set_title('Miles per Gallon (MPG) by Origin')
rename_x_axis = {'place': [1,2,3], 'labels':listOfUniqueOrigins}
ax.set_xticks(rename_x_axis['position'])
ax.set_xticklabels(rename_x_axis['labels'])
 
## Save and render the picture file
SAS.pyplot(plt, filename='violinPlot',filepath=outpath)
 
endsubmit;
stop;
title;

SQL Aggregation

SQL is a particularly widespread and helpful language for knowledge analysts and scientists. I discover utilizing SQL for aggregation straightforward, so I’ll create a easy aggregation and add it under the visualization on the identical worksheet within the the Excel report.

* SQL Aggregation *;
title justify=left "Common MPG by Automobile Makes";
proc sql;
choose Origin, spherical(imply(MPG_Avg)) as AverageMPG
	from work.automobiles
	group by Origin
	order by AverageMPG desc;
stop;
title;

Add textual content

On the finish of the identical worksheet I am going to add some easy textual content utilizing the ODSTEXT process to offer some details about the info.

proc odstext;
   heading 'NOTES';
   p 'Utilizing the SASHELP.CARS knowledge. The next automobile Origins had been analyzed:';
   listing ;
      merchandise 'Asia';
      merchandise 'Europe';
      merchandise 'USA';
   finish;    
   p 'Created by Peter S';
stop;

Lastly, I am going to shut the ODS EXCEL vacation spot since I’m completed writing out to Excel.

Outcomes

That is it! Now I am going to execute your complete program and think about the Excel workbook.

Abstract

With the capabilities of SAS and the brand new capability to execute Python code in SAS Studio, groups have quite a lot of instruments in SAS Viya for his or her analytic wants.

Further sources

PYTHON Process documentation
SAS opens its code editor interface to Python customers
Utilizing PROC PYTHON to reinforce your SAS packages
ODS Excel Assertion