Making SAS programs run faster

An alternative title for this post could have been ways to avoid sorting your data. Sort procedures in SAS are notorious memory hogs, and can radically slow down your program. Therefore, whenever you're able to avoid sorting a data set, do so. It makes your SAS programs run much faster. Also try to eliminate as many data steps as you can. The following methods can be used to improve the efficiency of your programs:

Indexing

An index is an optional data file created for a SAS data set to provide direct access to specific observations within the data set. The index file consists of entries that are organized in a tree structure, and connected by pointers. Your program knows where to look, and avoids a wasting a lot of time skimming through unnecessary data. Indexes can be created in a data step or an SQL statement.

Used the following code to create an index in a data step:

data flights
(index = (origin /* Creating a simple index */
dteflt = (fltDate flightID/unique)); /* Creating a composite index */
infile 'UPS_FLIGHTS.dat';
input flightID $7. routeID $7. fltDate date9. origin $3. dest $3. distance 8.
;
run;

Later, after you're finished with that piece of code and you want to delete the index:

proc datasets library = work nolist;
modify flights;
index delete origin;
index delete dteflt;
quit;

Use the following code to create an index in a SQL statement:

proc sql;
create index origin
on flights (origin);
create unique index dteFlt
on flights (fltdate, flightID);
quit;

If you want to delete the index in a SQL statement:

proc sql;
drop index origin
from flights;
drop index dteFlt
from flights;
quit;

Conditional Merges

Conditional merges are more complicated than conventional merges. In certain instances, however, conditional merges can work much faster than conventional merges.

A conditional merge matches observations in a secondary dataset with the observations in a primary dataset. The merge steps through the primary dataset one observation at a time, seeking “matching records” in the secondary dataset.

This matching is done within the context of an ‘Event Segment’. An event segment can be defined as the set of observations in the primary dataset which shares the same values with the observations belonging to the secondary dataset for a given number of variables (i.e., trade date, issue, firm, etc.). A set of rules within the event segment allows the records from the secondary dataset to merge with the records from the primary dataset.

In our example, we created five new variables to facilitate the conditional merge logic:

FirstZ – The FirstZ variable indicates the start of an event segment.

NextZ – The NextZ variable indicates where the pointer within a particular event segment.

Zloc The Zloc variable indicates whether the record for the secondary dataset is before, in or after a particular event segment.

Continue – The Continue variable tells the whether to process the data (i.e., execute event segment rules) within an event segment or move to the next event segment. Continue set to ‘1’ implies that the secondary record is still within the event segment, and the processing should continue. Continue set to ‘0’ implies the pointer for the secondary record is after the event segment, and processing should start for the next event segment.

Noobs - Number of records in the secondary dataset for a particular event segment.

In the conditional merge, the records in the secondary dataset are merged to that of the primary dataset by the set of common variables (which also determine the event segment). If the values belonging to any of these variables in the secondary dataset are less than that of the primary dataset, Zloc is given a value of ‘-1’. If the value is greater, Zloc is given a value of ‘1’. If the values are the same, Zloc is given a value of ‘0’.

Zloc equals ‘-1’ implies that the pointer for the secondary record is before the event segment. The value for NextZ is incremented until it matches the value for FirstZ (Start of the event segment). When NextZ exceeds the number of records in the secondary dataset for the event segment, Continue is reset from ‘1’ to ‘0’ prompting the pattern to evaluate the next event segment. FirstZ (i.e., start of the next event segment) is reset to correspond with NextZ.

If Zloc equals ‘1’, the pointer for the secondary record is below the end to the event segment. Continue is reset to ‘0’ prompting the pattern to evaluate the next event segment. NextZ is reset to correspond with FirstZ (i.e., start of the next event segment).

If Zloc equals ‘0’, the pointer for the secondary record is within the event segment. During this stage, the merge will execute any rules specified within the event segment. For example, if there is a rule to put a new value for a field (i.e., Change ‘N’ to ‘Y’ for a Flag) when the primary and secondary datasets variables (i.e., Sell Date = Buy Date) are the same, that rule will be executed within the event segment. The pointer for the secondary record is also incremented by 1, causing the pointer to move to the subsequent secondary record.

At the end of the conditional merge, we should have a dataset which has all the primary records aligned with the secondary records that satisfy the rules within a particular event segment.

The code of a condition merge is given below:

data merged_data (drop=firstZ continue Zloc [any columns you don’t need to output]);
retain nextZ 1 firstZ 1 Zloc break 0;

set primary_data;
by pdate pitem ptime pprice;

continue = 1;

do while (continue=1 and nextZ<=Zcount);
set secondary data
nobs=Zcount point=nextZ;

if sdate LT pdate then Zloc = -1;
else if sdate GT pdate then Zloc = 1;
else do;
if sitem LT pitem then Zloc = -1;
else if sitem GT pitem then Zloc = 1;
else do;
if stime LT pitem then Zloc = -1;
else if stime GT pitem then Zloc=1;
else Zloc = 0;
end;
end;

/* before window, step forward in secondary unless at end */
if Zloc = -1 then do;
if nextZ LT Zcount then nextZ + 1; else continue = 0;
firstZ = nextZ;
end;

/* after window, reset the cursor to start of window and do next primary */
if Zloc = 1 then do;
continue = 0;
nextZ = firstZ;
end;

/* in window, step thru, check logic, but leave the cursor as is */
if Zloc = 0 then do;

/*Put business logic here*/

If sprice GE pprice + (pprice*0.1)
if nextZ LT Zcount then nextZ + 1; else continue = 0;
end;
end;

if sprice GE (pprice + (pprice*0.1)) then do;
output;
end;

run;

Hash Objects

Hash objects are a new addition to SAS, and are supposed to speed up your program while making the most efficient use of memory. An unfortunate drawback of this technique is that has hash objects cannot be used in SQL statements, only in data steps. In the example below, we are trying to merge the variables for the participant data set with those in the weight table. The merged data set is called results. If we try to perform a conventional merge procedure, we would have create two additional data steps to sort the participants and weight data sets by name, thus causing the program to require more memory as well as increasing its run time.

data participants;
input name $ gender $1. treatment $;
datalines;
John M Placebo
Ronald M Drug_A
Barbara F Drug_B
Alice F Drug_C
;

data weight (drop = i)
input data DATE9. @;
do i to 4;
input name $ weight @;
output;
datalines;
05MAY2006 Barbara 125 Alice 130 Ronald 170 John 160
04JUN2006 Barbara 122 Alice 133 Ronald 168 John 155
;

data results;
length name treatment $ 8 gender $ 1;
if _n_ = 1 then do;
declare hash h (dataset: 'participants');
h.defineKey('name');
h.defineData('gender','treatment');
h.defineDone();
end;
Set weight;
if h.find() = 0 then
output;
run;

proc print data = results;
format date DATE9.;
var date name gender weight treatment;
run;

Format tables

Format tables are similar to hash objects, although slightly trickier to implement. This method requires an additional step, which is creating the format table. Subsequently, the values of the format table can be appended to a dataset based on a key variable that resides in both format table and dataset, similar to the hash object shown above. In the example below, we use the format procedure to add the names of the stock symbols to the indat dataset, and calculate the count of stock issues. Alternatively, we could have sorted both the indat dataset and a dataset containing the issue names, merged them together and performed a summary or means procedure to get the number of issues. Our method eliminated two sort procedures, one merge procedure and one summary/means procedure. When analyzing millions of records, time and memory space savings from eliminating four procedures can be significant.

First, read in your data set.

data indat;
input @1 name $3. @5 ask 5.2 @11 bid 5.2; cards;
IBM 16.25 16.12
AOL 17.00 17.06
AOL 16.25 13.02
IBM 16.25 16.05
IBM 18.25 17.02
FNM 18.00 18.06
FNM 18.25 17.02
FRE 18.25 17.02
;
run;

Second, create your format table.

proc format;
value $Symbol
'IBM'='IBM'
'AOL'='America Online'
'FNM'='Fannie Mae'
'FRE'='Freddie Mac';
run;

When performing your analyses, recall the values from your format table.

proc freq data=indat;
tables name /list;
format name $Symbol.;
title "Issue Count";
run;

Tools of the Trade V: Kicking Excel VBA Macros from SAS

The code below enables SAS programs to launch Excel VBA macros. Let's assume that you have an Excel Spreadsheet named Indat.xls that contains a macro which generates a bar chart when certain cells are populated. You have named the macro ShoChrt. Use the following code to populate the necessary cells and launch the ShoChrt Macro:

The first step is assigning the file references to the Excel spreadsheet containing the VBA macro(s), and opening the spreadsheet.

options xsync;

filename EXCEL DDE 'EXCEL|SYSTEM';
filename EXPORT DDE 'EXCEL|Sheet1!r1c1:r2c6' notab ;

data _null_;
file excel;
put '[open("C:\TEST\INDAT.XLS")]';
run;

Write the code to perform your analyses in SAS.

/* Perform your analysis in SAS */
proc summary data = indat nway; class size;
var price;
output out = outdat sum=;
run;
Export your output data to Excel.

data _null_;
set outdat;
tab = '09'x;
file EXPORT;
run;

The SAS code below will kick off the VBA macro(s).

/* Execute the previously created VBA macro named 'SHOCHRT' */
data _null_;
file excel;
put '[RUN("ShoChrt")]';
run;

Finally, the code below will remove the file references for your Excel spreadsheet(s).

filename EXCEL clear;
filename EXPORT clear;

Note: If you have any problems launching the Excel file using the code above, try this block of code instead:

options noxwait noxsync;
x '"c:\program files\microsoft office\office11\excel.exe"';

data _null_;
x=sleep(5);
run;

filename excel DDE 'EXCEL|SYSTEM';

Tools of the Trade IV: Using SAS to Output the Data

We will be using SAS to output data in different formats.

Output to a text file
This is the most basic and popular way to output data from a SAS program. The code below will create the header for your dataset:

Data _null_;
file "c:\Item_List.txt" lrecl = 150;
put
@1 "Product"
@10 "Type"
@20 "Service"
@30 "Price";
run;

This code will let you output the contents of your dataset:

Data _null_;
file "c:\Item_List.txt" mod lrecl = 150;
set ItemList;
put
@1 product
@10 type
@20 service
@30 price;
run;

To directly output a SAS dataset into a spaced tab text file, use the following procedure:

PROC EXPORT DATA= WORK.ItemList
OUTFILE= "C\Item_List.txt"
DBMS=TAB REPLACE;
RUN;

To output a SAS dataset into a delimited text file, use the following procedure:

PROC EXPORT DATA= WORK.ItemList
OUTFILE= "C:\Item_List"
DBMS=DLM REPLACE;
DELIMITER='00'x;
RUN;

Output data to *.csv file
To output data to a *.csv file, use the following procedure:

PROC EXPORT DATA= WORK.ItemList
OUTFILE= "C:\Item_List.csv"
DBMS=CSV REPLACE;
RUN;

Output data to an Excel file
There are several ways to output SAS datasets to an Excel spreadsheet. The following code will dump the data into your Excel spreadsheet, but you'll need to keep the spreadsheet open:

filename toexcel dde "excel|Sheet1!r2c1:r30000c11";
data _null_;
file toexcel dlm='09'x notab;
set Final_Data;
put Date Day Train Orig Dest Pallets Footage;
run;

This SAS macro allows you more flexibility to output your data. You can specify the columns and rows of your spreadsheet where you want to output the data:

%macro m_xlout(sheet, tbl, ds, var, row1, col1, row2, col2);

filename tmp dde "excel|[&sheet.]:&tbl.!r&row1.c&col1.:r&row2.c&col2.";
data _null_;
set &ds.;
file tmp;
put &var.;
run;

%mend m_xlout;
%m_xlout(UtilizationRates.xls, Utilization Details, facilities, route trip orig dest arrivl utiliz, 5, 2, 16000, 7);

Finally, if you just want to dump the entire SAS dataset into an Excel spreadsheet, use the following procedure:

PROC EXPORT DATA= Final_Data
OUTFILE= "C:\Final Data.xls"
DBMS=EXCEL2000 REPLACE;
RUN;

Output data in HTML
Here is a quick and dirty way to output SAS data in HTML format:

filename odsout "C:\Outputs\Analytics Dashboards"
ods html path=odsout
body="Dashboard reports.html"
nogtitle;

proc summary data=indat;
var revenue;
output out=outdat sum=;
proc print;
title "Summary of Premier Transactions";
run;

ods html close;
ods listing;
title;

The printouts between ods html path and ods html close statements will appear in an HTML page in the Analytics Dashboard folder in the C:\ drive.

Tools of the Trade III: Simple Data Validation Procedures

The reliability of your analytical model depends to a great extent on the quality of its underlying data. Unfortunately, bad data is a prevalent problem for most corporations. A survey by Gartner found that at least 25 percent of all data for Fortune 1000 firms were bad or corrupted. Based on my experience, carrying out several simple procedures after reading in the data will let you track and rectify many of these data quality problems, even if you don't get 100 percent accuracy. If you are getting your data from an outside source, it is a good idea to share your findings from the data validation procedures with it. The people providing you the data may be able to tell you whether your preliminary findings make sense or needs further investigation. Carry out these procedures, even if you have assurances that the data is good and complete. These simple - yet effective - procedures include the following:

1. Prior to performing any analyses, pull out the first and last 15 observations of the data and look for any obvious inconsistencies. Examine columns with blanks and null values. Check and see whether the values for a variable make sense (i.e., a column that should have numeric values, such as price, does not contain numeric values).

2. If using SAS, perform a contents procedure on the dataset to see if all the variables are accounted for in the proper format. Date and Time are often the trickiest variable. Make sure whether they are in time, timestamp or character format. IDs are another problematic variable. Generally, they should be in character format (even if they are numbers), but often exist in numeric format.

3. Verify the number of records you read into your program. Investigate if you have more (often resulting from trailing blanks) or less records (records getting dropped by the read in program) than anticipated. Trailing blanks are not harmless and may cause your program to malfunction during later stages of your analyses.

Try not to eliminate any records. Save the bad records in separate datasets for further analyses. These records often provide valuable clues for future QA strategies.

4. Make sure the data falls within valid ranges. For instance, if you know the age of people in the dataset should not be less than 18 years or more than 65 years, all the DOB should be within that range. If you know that the highest and lowest unit prices in your dataset are $100 and $0.10 respectively, then seperate and study records where unit prices are above $100 or less than 10 cents.

5. Perform a frequency on key variables. A frequency on dates could show records missing for an entire day. Also, pay attention to dates equavalent to default dates (i.e., Jan 1, 1900 for Excel and Jan 1, 1960 for SAS). Make sure there are no blanks when there should be null values, and vice versa.

6. Randomly pull 15 records from the entire dataset and ascertain that there are no egregious errors.

7. Read out the records with the 10 highest and lowest values for key variables.

8. Output the mean, median and mode for key variables.

Save your data validation results. Comparing the different findings over time should enable you to identify and correct data problems.

Using SAS to compare two datasets

You can use the SAS compare procedure to validate a dataset by comparing it with a baseline dataset. Use the following code to compare two identical datasets:

proc compare base=indat1 compare=indat2 OUTSTATS=outdat1 noprint;
run;

This is helpful when you need to modify an existing SAS program, but do not want the modifications to change the output.

Tools of the Trade II: Using SAS to Extract the Data

In this post, we will be using SAS to read in the input data from different file formats.

Reading data from a text file
Reading data from a text file is the most basic and popular way to read in data for a SAS program.Usually this is how analysts first learn how to read in data into their programs. It gives you the greatest control while reading in the data, and you can read in as much data as you want. Let's assume that there is a text file named 'hsbctxns.txt' saved in a folder called hsbcdata in your computer's C\: drive. The code for reading that file is given below:

data raw; infile
"C:\hsbcdata\hsbctxns.txt" missover pad lrecl=1000;
input
@1 cardname $8.
@9 merchant $20.
@29 amount 8. ;
run;

If you want greater control over the text file you want to read in, you can utilize the following code. The double questions marks (??) allows the code to read in data even if the format type is different what is specified in your code. In the example below, your code would read in amount values, even if some of them are in character format, although the amount field has been specified as numeric.

data raw;
infile "C:\hsbcdata\hsbctxns.txt" missover pad lrecl=1000;
input
@1 cardnom1 ?? $8.
@8 cardnom2 ?? $8. @;
cardnom2 = cardnom1;
input
@19 merchant ?? $20.
@39 amount ?? 8. ;
run;

To save type typing code and directly import a spaced tab *.txt file to your program, use the following code:

PROC IMPORT OUT= WORK.INDAT
DATAFILE= "C:\REJECT_REPORT.txt"
DBMS=TAB REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

To import a delimited *.txt file, use the following code:

PROC IMPORT OUT= WORK.Profit_Rpt
DATAFILE= "C:\Profit_Rpt.txt"
DBMS=DLM REPLACE;
DELIMITER='00'x;
GETNAMES=YES;
DATAROW=2;
RUN;

Reading data from a *.csv file
Now we'll read in data from delimited or *.csv file. The delimiter let's the program know when the current field ends and the next field begins. This method is advantageous over the previous method because you don't have to specify the position and format for each variable you read. Also, this method lets you read in as much data as you want. Let's assume there's another file named 'deptamt.csv' in the hsbcdata folder in your C:\ drive. Here is how you would read in that data:

data rawcsv;
infile "C:\hsbcdata\deptamt.csv" dlm=',' dsd missover pad firstobs=2 lastobs=1000;
length name $8. type $4. amount 8.2;
input name $ type $ amount;
run;

One problem you might encounter using comma (',') as a delimiter is that numbers (i.e., 23,000) or certain fields (i.e., LastName, FirstName) may have commas within their values that may corrupt your dataset. A less frequently used special character, such as tilda ('~'), as a delimiter can help you avoid this problem.

To import a *.csv file, use the following code:

PROC IMPORT OUT= WORK.Employee_Records
DATAFILE= "C:\Employee_Records.csv"
DBMS=CSV REPLACE;
GETNAMES=YES;
DATAROW=2;
RUN;

Reading data from an Excel file

Now on to reading in data from a Microsoft excel file. This saves the effort of converting your Excel file into another file format and reading it in. However, Excel limits the row and columns of data you can read in. This code lets you read in the data from an Excel file named hsbcfigs.xls:

filename toSAS dde"Excel[hsbcfigs.xls]hsbcstat!r1c1:r100c100" notab;
data rawxls;
infile toSAS dlm='09'x dsd missover;
length name $8 type $4 amount 8.2;
input name$ type$ amount;
run;

To import an Excel file, use the following code:

PROC IMPORT OUT= WORK.indat
DATAFILE= "C:\valid_tests.xls"
DBMS=EXCEL2000 REPLACE;
GETNAMES=YES;
RUN;

Reading data from a DB2 Database
Utilizing the SQL pass thru code below, you can read data directly from DB2 database to your SAS program. The same pass thru code can also be used to read in data from an Oracle database.

proc sql;
connect to db2 (database=app user=appguest password=app123 dsn=core schema=appcore);
create table testdata as
select * from connection to db2
(select m.name, m.type, m.amount
from appcore.sales m
where m.salesdate='10JUN2007' and m.amount<=100.00);
disconnect from db2;
quit;

Often, the data for your analytical model would come to you from different sources in various forms. You can use SAS to read in the data based on how the data is made available to you.

Tools of the Trade I: The Reporting System Architecture

Want to know more about your customers' buying behavior? What was the impact of the $5 coupons that you handed out at the metro station? Should you hand out more? Is there really a correlation between the sales of diapers and beer in your store after midnight? What other items are these grumpy dads buying? Nachos to go with the beer? Or do they prefer pretzels? In today's blog entry, I want to discuss about the tools you will be using for your analytics strategy so that you can answer questions like these. Specifically, I want to show you how to put together an analytical reporting system using SAS and Microsoft Excel to analyze your data. This system will to pull data from various sources, scrub it clean, perform your analyses, and output the result(s) in accordance to your needs. The architecture that I have shown below should be sufficient to solve a large number of your analytics problems; basically I think that you're limited only by your imagination. Most large firms have SAS licenses and almost everyone with a PC has Microsoft Excel. Both softwares work seamlessly together and create a very powerful analytics tool; however, I have noticed most companies use only a fraction of the capabilities that these two systems offer. A reason why these two products complement each other so well is because they counterbalance their weaknesses. For instance, SAS can read in hundreds of millions of records and perform very sophisticated statistical analyses on very large datasets. SAS, however, can't produce professional looking deliverables with graphs and charts that you can hand out at a meeting. Excel, on the other hand, can produce pretty looking deliverables, but is severely restrained on the number of records it can work on (65,536 rows per worksheet). The best approach, therefore, is to have SAS do the heavy lifting, and Excel do the trimmings. A note to freshman analysts. If you can, at the very least, learn and execute the components needed to implement the system shown below, there will always be food on your table and a roof over your head.



The structure above has four main components. These are:

Extracting the Data
I'll show how to extract data from different types of files. In one example, we will read in the entire data from an Excel file. In another example, we will read in data from a specific row (In case someone provided you an Excel file with headers and titles that you want to exclude when extracting data). We will also read in data from a comma delimited file and a text file. I will also tell you what file formats are preferable in what circumstances (extracting from text files gives you the greatest level of control on what you want to read into your program, Excel gives you the least. However, Excel being a very popular file format means that you may be receiving a lot of your data in Excel format). In another example, we will extract data from a DB2 database (i.e., if you want to read data from your corporate databases) using SQL code in a pass-thru program (if your organization prefers Oracle, similar code can be used to extract data from Oracle databases too). A lot of people don't know that SAS has specific code that produces a GUI to add or delete individual records from a table (think of it as a back door approach to handle one-sies and two-sies, so you don't need to run the data extraction programs again). We will utilize that code too.

Data Validation
After reading in the data, you will want to ensure that it is not corrupted or useless for your purpose. In this stage, we will employ common sense techniques and filtering procedures to ascertain the quality and integrity of our data.

The Analytics Engine
This is the heart of your system, where all the 'magic' takes place. Whether four lines of a regression datastep or 120 pages of extremely sophisticated linear programming code to execute an optimization strategy or pick stocks, think of it as a black box where raw data enters in one end, and actionable information comes out the other end. This black box will contain all your business rules.

Reporting Your Output
SAS allows us to output data in Excel, comma delimited, or text formats. Moreover, one of the nice features about SAS is that it allows us to kick off Excel VBA macros from the program itself. We will use this feature to automatically format the Excel workbook to which SAS outputs our results, eliminating the need to manually pretty up our deliverable. The idea is to have the deliverable ready as soon as the SAS program finishes its run, as well as eliminate any unnecessary manual work or possibility for human errors. SAS also allows us to output data in HTML format to a specific directory. Therefore, we can output the results to an internet directory and have a web based report available to everyone with a web browser, preferably at the same time the spreadsheet deliverables are available for distribution. Think web based Dashboards with hard copy deliverables available instantaneously for distribution.

Over the next several weeks, I'll be elaborating further on how each of these system components work. I might also be tweaking the overall system to introduce improvements, such as introducing a control program and scheduling reports to run automatically. Finally, I will be providing several actual examples where this architecture has been implemented.