SAS Automation - From Password Protected Excel Raw Data
To Professional-Looking PowerPoint Report
Jeff Hao, PhD, JPMorgan Chase & Co., Columbus, OH
In banking and finance industry, the most common report format presented to the executives is Microsoft PowerPoint
(PPT). How to realize the SAS automation process from Excel raw data to PPT report is a tremendous challenge.
The first step of the process is to directly import password protected Excel raw data into SAS data sets. The second
step is to convert the tables and graphs to be used on PowerPoint slides into image formats such as PNG or JPG.
The last step deals with exporting the tables and figures images into customized professional-looking PowerPoint
report. Actually, the first and third steps are the most difficult parts of the whole automation process.
By combining SAS with DDE, Proc Template, SAS/Graph Statistical Graphics (SG) procedures, Graph Template
Language (GTL), Proc Report, ODS Printer, and VBScript, the whole automation process comes true.
This paper presents a detailed step by step approach to the seamless automation process from password protected
Excel raw data to PowerPoint report by a single SAS run click.
Microsoft® Excel is an excellent tool for formatting and presenting data. For security sake, especially for sensitive
materials, Excel files are actually password protected to restrict access to the information.
SAS® has become a powerful business solution tool for clinical trial reporting, data management, statistical analysis,
econometrics, data mining, business planning, forecasting, and decision supports, etc. due to its versatile functions,
especially its automation capability.
Without password protection, MS Excel files can be imported into SAS data sets in one of the following ways: 1) Proc
Import; 2) Proc Access; 3) Using Import Wizard (Manually); 4) Using DDE routine syntax; 5) Using SAS/Access to
ODBC (Proc SQL); 6) Using JMP (Manually or programmatically); or 7) Using Data step (saving the Excel file into
CSV first, then using FILENAME and INFILE statements). Since this content is beyond scope of the paper, we will not
discuss it in detail here.
However, if we use PROC IMPORT to read a password protected Excel file, it will return the following error message:
ERROR: Connect: External table is not in the expected format.
ERROR: Error in the LIBNAME statement.
Using SAS/ACCESS to ODBC (Open Database Connectivity) will return the same error message as PROC IMPORT.
In fact, none of the methods mentioned above will enable us to read a password protected Excel file into SAS data
To solve this issue, one simple way is to open the file with password and then to save it into an Excel file without
password, and finally using
PROC IMPORT to read the file into SAS data set. If there is only one such files, it won’t
be an issue.
However, if there are more than 5, 10, 20, 50, even more than 100 such excel files, it will be time-consuming and
prone to do so.
Meanwhile, from the standpoint of security, it’s not a recommended practice for us to save a
high sensitive file into a none password protection file first, then destroy it after SAS read it. Unfortunately, we are
facing this challenge everyday.
It’s necessary to find a way to read high sensitivity files into SAS data sets without
sacrificing security concern.
Can we find an easy way to
deal with this challenge? The answer is ‘Yes’. We can use Dynamic Data Exchange
(DDE) to set up the communication bridge between SAS and Microsoft Excel so that SAS can use X4ML commands