A lot of consumer
finance analytics work involves some form of reporting. Analytics
community prefers the reports to be in a spreadsheet (MS Excel) format
as they can carry out further analysis. For the regular production
reporting automation is carried out to avoid manual work as much as
possible. This chapter briefs some techniques and possibilities to
explore for various reporting tasks.
In Consumer Finance, we use
PROC EXPORT, ODS HTML or DDE methods to produce an excel or web based
report. Let us look at some example to understand each one of them.
PROC EXPORT procedure
exports a SAS dataset into a MS Excel. It can also export the data into
other formats like Access or Lotus but we limit our discussion to Excel
only. Windows SAS provides an interface to do the data set export and
when you want to do it automatically, you can specify the same as a
program block as shown below.
PROC EXPORT
DATA= work.Account_perf
OUTFILE= "D:\ex_Account_perf.xls"
DBMS=EXCEL2000 REPLACE;
RUN;
Line: 1 Specifies the SAS
library and dataset name to be exported
Line: 2 Specifies the path
and Excel filename where the data to be explored
Line: 3 tells SAS what data
output format it is and to replace the file if existing already.
Note that not all formats
applied on the variables are preserved during the export process.
Also the labels are not exported by default.
Before we create reports
using proc export, we may have to create the data set in SAS, which
could be directly exported to excel. This method is normally used when
we have to send only data tables as reports on a regular basis.
Output Delivery System
(ODS) statement in SAS allows the analysts to create HTML, and Excel
reports that are sharable. Its possible to create templates for these
reports and applies such formats on the final reports.
HTML formats are
sharable across platforms and its possible to open those
files in excel. So with appropriate changes in file extensions we
can create XLS files through the ODS HTML statement. This is an
advantage as many times customer prefers an Excel report but they
cannot be produced in Unix environments.
When using the ODS
HTML statement, the basic call contains two parts: the ODS call
statement to tell SAS that an HTM/XLS output is requested and the ODS
close statement to tell SAS to close the report. Any output
operations in between call and close statements are directly written
into an HTML output file.
For example, we are doing
two operations- a PROC FREQ and a PROC PRINT – output of these two
procedures would be written into an HTML file, if they are specified
between and open and close statements. Let us look at an example
DATA account_perf;
format current_os dollar10.2
tot_payment words40. ;
label account="Account #"
fico_seg = "Fico Segments";
INPUT client $ account fico_seg $
current_os tot_payment ext_status_code $ ;
cards;
Cmart 1002 401-500 300 100 A
C
Cmart 1003 501-600 200 150 A
C
Cmart 1004 601-700 1200 180 A D
Cmart 1005 701-800 800 190 Z
A
Cmart 1006 801-900 450 200 Z
A
GIA 1007 401-500
560 210 Z A
GIA 1008 501-600
450 180 A D
GIA 1009 601-700
900 145 A D
GIA 1110 701-800
300 148 Z D
;run;
ODS HTML FILE='D:\ods_report_test.html';
proc print
data = account_perf;
by client;
title "Sample accounts with FICO Score";
run;
Proc Freq
data = account_perf;
tables client/list;
Title "# of Accounts by Client";
run;
ODS HTML CLOSE;
The data step above creates
a SAS dataset ‘account_perf’. Format and labels are also applied to
demonstrate later that how ODS preserves them in the final output.
Line: 1 of ODS statement
specifies the path and file name of the output report.
Line: 2 – A print statement
that prints data for each client separately.
Line: 6- FREQ procedure is
used to display the number of accounts for each client.
Line: 10 -Tells SAS to
close the ODS file opened for various output.
All the outputs of the
procedure used above are written to D:\ods_report_test.html file as
output tables. The standard template is used by default by SAS for the
display. When writing into the html file, SAS preserves the label,
format, titles that are applied on the data, which is an advantage over
PROC EXPORT.
How do we produce an Excel
report? One can just modify Line: 1 and change the file extension to
.xls (instead of .html) to create an excel file! This file can be
opened in Excel with all formats and labels preserved.
The output template
provided by default can be changed by creating templates using PROC
Template procedure and the same can be applied at ODS run time.
Dynamic Data
Exchange (DDE) is a MS Windows protocol for dynamically
transferring data between Windows-based applications using a
client/server model. Using this protocol SAS System can request data or
send data and commands to other windows applications like Excel or
PowerPoint.
“Could you put the data in
an Excel spreadsheet, so that we could play with it?” We hear the all
the time from the managers, as they really like navigating through the
spreadsheet and doing additional calculations. Web based reports or SAS
based reports (PROC REPORT or PROC TABULATE) do not meet this
requirement of the customer but mastering DDE can help the analyst to
create the customized reports in Excel
This is what you can
achieve through DDE when working with Excel
Now let us look at an
example to understand how DDE works in SAS. In the earlier example we
had a limitation that we could not use our own excel template for
reporting . Now let us assume we designed one excel template and stored
it as ‘D:\MyReports\DDE_test.xls’ . Now we want SAS to open this
template and fill in the data for various columns.
First let us invoke excel
and open the template we designed:
options noxwait noxsync;
x '"C:\Program Files\Microsoft
Office\Office\excel.exe" ';
FILENAME DDTEM DDE 'EXCEL|SYSTEM';
DATA _NULL_;
FILE DDTEM;
PUT
'[OPEN("D:\MyReports\DDE_test.xls")]';
RC = SLEEP(3);
RUN;
Line:1- X command is used
to invoke the excel short-cut in D: drive. If you don’t have a shortcut
created, please create one!
Line:2 – A FILENAME
statement that establish a link with Excel system opened with a
keyword ‘DDE’ DDTM is the handle defined to refer to this link
later in SAS data step.
Line:3 – A temporary
dataset is created to invoke the template.
Line:4 – FILE statement to
tell SAS which link is established with excel to be used
Line:5 – PUT command –
passes on the instruction to EXCEL system – Here the instruction is to
open the template we created.
Line:6- SLEEP command to
stop SAS system from processing for 3 minutes. This is to make sure
that the template in Excel is opened before SAS proceeds with the data
updating in the steps followed.
Now we have the template
open in Excel and ready to receive data. We need to implement another
link to target worksheet and define the area in the template before we
tell SAS where to put the data. We will do it using the same
FILENAME statement.
FILENAME DDE_SAS DDE
"EXCEL|ACCOUNT_PERF!R2C1:R20C6" NOTAB;
Here, ‘Account_Perf’ in the
template is defined as the target worksheet and the data area marked
contains rows starting from 2(R2) and ending at 20 (R20) and columns
1(C1) to columns 6(C6). Please note that any data outside this defined
area will be ignored. Next step is to drive the data into the
spread sheet in a DATA step. Here is how we do it.
(Please note that dataset
used in previous section is used here , PUT statement is used to write
specified fields in to a file that is specified earlier with FILENAME
statement. “09”x is a delimiter specification that tells SAS to put a
TAB after each variable written into the file.)
DATA _NULL_;
FILE DDE_SAS;
SET account_perf;
PUT
client $ "09"x
account "09"x
fico_seg $ "09"x
current_os "09"x
tot_payment "09"x;
RUN;
The above code use the link
established with excel and drive the data from account_perf to
‘DDE_SAS’. PUT statement specifies the variable names and the
order. "09"x is the delimiter (ie TAB) .
We have seen the basic form
of how DDE is used in automating the Excel reports with SAS.
Flexibility is that we can define the specific areas in Excel sheet and
put the values from SAS dataset there. Coupled with Macros, DDE could
be used as a powerful method to create EXCEL based reports.
Suppose there is an Excel
macro on Client_report.xls, which does some formatting on the reports
and graphs after the data was updated. To do this task analyst have to
open the file and run the macro. With DDE this can also be achieved
from SAS. Suppose our file had a macro called ‘painter’ defined
and we are now running the excel macro through SAS and saving the file
with a different name.
DATA _NULL_;
FILE DDE_SAS;
PUT '[RUN("Client_report.xls!painter")]';
PUT
'[SAVE.AS("D:\MyReports\Client_report_formatted.xls")';
PUT '[QUIT()]';
RUN;
To conclude, SAS based
reporting is largely limited to ODS HTML and DDE in analytics
environments. However SAS procedures like PROC REPORT and PROC TABULATE
provide lot of features to customize and format the reports.
