How to Optimize Existing SAS® Programs For Generating Standard Reports

Teo Gamishev, Office of Research and Statistics, Columbia, SC

Download the PDF version from here

ABSTRACT
How many times you have to submit and resubmit numerous SAS programs to create standard reports every month, again and again? How many times you have to manually change dozens of dates and file names within a single program and do it again and again for all of the remaining programs? How many times you have found errors in the above changes and had to repeat the entire tedious process from the very beginning? What if you could perform all those standard reports with a single click, only. That is correct, no manual changing of dates, no manual creating of directories and renaming files. This paper shares a simple way to modify existing SAS programs and create a driving SAS program in order to automate the entire process of generating standard reports. These techniques can be used to automate many daily tasks, simplify more complex tasks and increase your overall programming productivity.

PROBLEM
Most of the Standard Reports, created by a different SAS programs, are generated on a monthly, quarterly or yearly basis. The usual routine, a SAS programmer has to go through, is to change the dates, filenames and resubmit the programs.
Some of the monthly standard reports packages require resubmitting of numerous SAS programs and changing from one to dozens of dates within each program. That becomes a tedious and noncreative time-consuming workload.
The most unpleasant point here is that incrementing hundreds of dates, within a short time, increases the probability for incorrect entries. The errors in dates are absolutely unacceptable and generate more unnecessary workload to correct and resubmit the programs, if caught by the programmer or by the checker. This might be very frustrating when facing the report’s deadlines.

SOLUTION
SAS provides very useful tools to fully automate the process of changing the dates and reduce the amount of the programs you have to submit on a fixed time basis. The existing SAS codes need a little adjustment, only, to achieve a considerable overall improvement.
The best part here is that errors are eliminated, completely. Once the dates are automatically incremented and the new directories and files are created, in most of the cases, you don’t need to reopen the program and it can be submitted automatically, too.

OUTCOME
By applying the method, described below, the time for just one of the standard report processes, I am in charge, has been reduced from 13 hours to 13 minutes.
The overall outcome is: creating correct standard reports for a significantly shorter time.

SAS TOOLS
Here is a brief explanation of the SAS tools in use:
INTNX is a SAS time function which creates a SAS date, time or datetime value that is a given number of time intervals from a starting value. The form of this function is:
INTNX(interval, from, n <, alignment>),
The INTNX function computes the date (or datetime) of the starting date or datetime (from) of each interval (character constant or variable), using (n) as the number of intervals to increment. Alignment is optional and controls the alignment of the dates within the interval. It can take the values BEGINNINGB, MIDDLEM, ENDE or SAMEDAY.
Beginning is a default value.


CALL SYMPUT creates a macro variable whose value is information from the DATA step or assigns a DATA step value to an existing macro variable. Here is the syntax:
CALL SYMPUT(macro-variable, value);


FILENAME PIPE feature enables your SAS application to receive input from any UNIX command that writes to standard output and to route output to any UNIX command that reads from standard input. The syntax is:
FILENAME fileref PIPE 'UNIX-command' ;


PROC PRINTTO redirects the Output or the Log of your SAS session to any destination other than ODS (Output Delivery System). For example your printer or a file could be one of your desired destinations.


There are many more tools provided by SAS to use here. For example, you can check or compare the number of observations and create and stop the program in case of discrepancies. SAS can type an error message in the log, send an e-mail on completion of the program and the list goes on…

UPDATING PROCESS
The usual steps for producing a standard reports may include:
Updating and submitting SAS programs for creating and updating SAS tables
Updating and submitting SAS programs for generating different sets of Standard Reports
The goal is to eliminate all the work of changing the dates and creating new directories and files.


Here is one way to optimize the entire process by:
Step One: Creating driving SAS program
Step Two: Modifying existing SAS programs


This is just and example. Your case might differ a lot. In general, you’ll get the idea and the inspiration to do whatever you learn from the following pages. Use your imagination and creativity to apply it and get nice results at the end.

STEP ONE: CREATING DRIVING SAS PROGRAM
A simple approach suggests grouping all programs performing a similar task. Then creating a new SAS Program (named runme.sas here) in order to generate all the dates you’ll need for the programs within that particular group and submit them from within driving program. This program is referred as DRIVING program.
Here is a typical structure of such RUNME.sas program where you can see the practical application of the above SAS tools:


The following options statement on top of the program is very useful with SAS macros:
options mprint mlogic symbolgen;


This statement will turn on the above SAS options so that you’ll be able to see in the log the following important information:
 all SAS statements that are generated by macro execution – mprint
 how the macro statements have been executed - mlogic
 what values have been assigned to your macro variables – symbolgen


The above options will give an opportunity to get information to help debug your macro, also.



USE INTNX FUNCTION TO CREATE ALL THE DATES YOU NEEED

EXAMPLES
Suppose you want to create a variable that is the first day of the data month, which is usually the month before the current month. You could use the INTNX function to help you create your new variable:


* Data month is usually the month before the current month
dmfirst= INTNX('month',today(),-1); * first day of the data month;


Now, you need probably the last day of the data month, too. Here comes the beauty of the INTNX alignment option:


dmlast= INTNX('month',today(),-1,'end'); * last day of the data month;


You got the idea; INTNX can create any date you need for your reports, with starting point today() or any desired date/datetime.


dmfirst=intnx('month',’01jan2007’d,-1); * first day of the data month;
dmlast=intnx('month', ’01jan2007’d,-1,'end'); * last day of the data month;


The variable names here are up to your preference.


THEN USE CALL SYMPUT TO GENERATE ALL THE FORMATS OF THE ABOVE DATES YOU NEED

EXAMPLES
Here you can find a simple way to use the variables created by INTNX and transfer them into macro variables:


call symput('DMFD',put((dmfirst),DATE9.)); * first day of the data month;


The followings examples contain some of the very useful date formats:


* the name for the month before data month;
call symput('BDMN',trim(left(put((bdmlast), MONNAME.))));


* the first day of the current month;
call symput('CMFD', trim(left(put((cmfirst), DAY.))));


* the number of the year of the month before data month;
call symput('YBDY', put((bdmlast), YEAR4.));


* the MONYY format for data month;
call symput('DMMYY', put((dmfirst), MONYY5.));


* the number of the data month in two digit format;
call symput('MM', put(month(dmlast), z2.));


* the number of the data month year in two digit format;
call symput('YY', put(dmfirst, year2.));


Hint: use the following statement to check the values of the macro variables created above. SAS will list the macro variables and there values in the Log:
%put _user_;

HERE COMES THE TIME TO USE FILENAME PIPE STATEMENT
It allows you to create all new directories from within the SAS without annoying DOS window. A simple example below can be used to create any directory you may need for your data and standard reports:

EXAMPLES
Here is how you can use that feature to create a new directory or a file from within SAS program:


FILENAME lynn PIPE "mkdir ""E:\DSP\Reports &DMMYY.""";


Notice the use of double quotes, only. They allow the word scanner to recognize the macro variable &DMMYY by & trigger. As a result, the macro variable will be sent to the Macro Processor and resolved.


Now you need to tell SAS to execute the mkdir command using the following step:


data _null_;
infile lynn;
run;

LAST SECTION: TRIGGER ALL SAS PROGRAMS WITHIN THE SAME GROUP USING %INCLUDE COMMAND

*** Calling all programs to creates monthly Standard Reports ***;
%include 'E:\DSP\cap\Programs\01a.sas';
%include 'E:\DSP\cap\Programs\01a_pt2.sas';
…
%include 'E:\DSP\cap\Programs\09.sas';
run;

APPENDIX 1 contains an example of driving program.

STEP TWO: MODIFYING EXISTING PROGRAMS

This is a very delicate step. The updates to the existing programs have to be made carefully without messing up with the main logic. It is strictly recommended to create a new directory for all updated programs. Here is one way to do it:

CREATE THE NEW OUTPUT FILE NAMES
You’ll need the output(s) and eventually the SAS log(s) to be saved in files. The statements, creating the above files, are placed on top of the program, usually. Pay attention that the new folder has been created in the above driving program, already!

filename myoutput "e:\DSP\ Reports &DMMYY.\01a&mm.&yy..lst" lrecl = 190;
filename mylog "e:\DSP\SASLogs &DMMYY.\01a&mm.&yy..log" lrecl = 190;

REPLACE ALL DATES WITH THE NEWLY CREATED MACRO VARIABLES FROM THE DRIVING PROGRAM
Substituting the dates with the new macro variables is the most important part in the entire process of updating the programs. The following examples are demonstrating one way of completing that step:

* OLD PROGRAM ; * NEW PROGRAM ;
%let begdt=’01mar2006’d; %let begdt="&dmfd"d;
%let enddt=’31mar2006’d; %let enddt="&dmld"d;
%let title=March 1 – March31; %let title=&dmmn. &dmfdd. - &dmmn. &dmldd.;

PRINT THE OUTPUTS/LOGS DIRECTLY INTO THE FILES USING PROC PRINTTO

EXAMPLES
In many cases you’ll need the outcome to be saved into the file and this can be done using PROC PRINTTO. The following statement has to be pasted before the first PROC PRINT statement creating the report within the old program:


*** redirects the output into the file created in the filename statement above ***;
proc printto print=myoutput;
run;

If you need to save the log, the following statements should be placed in the beginning of the program:


*** redirects the log into the file created in the filename statement above ***;
proc printto log=mylog; run;


If you need your Output to go directly to the printer then here is the solution:


filename myoutput printer;
proc printto print=myoutput;
run;

You can do the same for the Log:


filename mylog printer;
proc printto log=mylog;
run;

Very important moment is to close the PRINTTO connection after all PROC PRINT statements or after each output and log in order to separate the contents going to the report file and log file. The following simple statement will do that:
proc printto; run;

Consider printto connection as a telephone call to different clients/files. You need to close it first, before calling the next client/file.

PERFORM NUMEROUS TESTS TO ASSURE THE CORRECT OUTCOME
Each updated program has to be run and the logs and outcomes compared against the log and the outcome of the old version of the program.
Once you complete successfully the above two steps, all you have to do is to submit the driving SAS program when the trigger e-mail or fax arrives.
Checking the log and the outcome is a must do step, always.

Appendix 2 contains an example of modified SAS program.

CONCLUSION
It will take a little work to update the old programs, create a new driving program to generate the new macro variables and test them, but the reword is enormous. The correct standard reports will be produced within a few minutes with a few clicks of the mouse and that, compared to 1 or 2-3 days of tedious work, is a great improvement of the entire process.

REFFERENCES
Andrew H. Karp. 1999. “Working with SAS DATE and TIME Functions”. Proceedings of the Twenty Fourth Annual SAS Users Group International Conference, Miami Beach, FL, 058-24.
Hsiwei Yu (Michael), Gary Huang. 2002. “Create Directory on Windows without the Fleeting DOS Window”.
Proceedings of PharmaSUG 2002, Salt Lake City, UT
SAS Institute Inc. 1999. “SAS® Companion for UNIX Environments”. Cary, NC: SAS Institute Inc.
SAS Institute Inc.(2006). “SAS OnlineDoc® 9.1.3”. Cary, NC: SAS Institute Inc.
Russ Tyndall. 2005. SAS Technical Support Documents. “Give Your Macro Code an Extreme Makeover”. <> (June 15, 2006)

ACKNOWLEDGEMENTS
The author would like to thank Roumen Kozarev and Jason Dew for their support and cooperation during the process of creating this paper.

CONTACT INFORMATION
Your comments and questions are valued and encouraged. Contact the author:

Teo Gamishev
BCB - Office of Research and Statistics
1919 Blanding Street
Columbia, SC 29201
gamishev@hotmail.com


SAS and all other SAS Institute Inc. product or service names are registered trademarks or trademarks of SAS Institute Inc. in the USA and other countries. ® indicates USA registration.

Other brand and product names are trademarks of their respective companies.



APPENDIX 1

EXAMPLE OF SAS DRIVING PROGRAM - RUNME.SAS


*** turning on SAS macro options ***;
options mprint mlogic symbolgen;
*** Creating global macro variables to be used by the programs ***;
data _null_;
dmfirst=intnx('month',today(),-1); * the first day of the data month;
dmlast=intnx('month',today(),-1,'END'); * the last day of the data month;
call symput('DMMN',trim(left(put((dmfirst), MONNAME.)))); * data month name;
call symput('DMFD', put((dmfirst), DATE9.)); * first day of the data month;
call symput('DMLD', put((dmlast), DATE9.)); * last day of the data month;
call symput('DMYY', trim(left(put((dmfirst), YEAR4.)))); * data month year;
run;
*** Checking the values of the variables, created above ***;
%put _user_;
*** Creating Directory for saving the outputs of the following programs ***;
filename lynn pipe "mkdir ""E:\DSP\Reports\Outcomeas &dmmyy.""";
data _null_;
infile lynn;
run;
*** Calling all programs to creates the monthly Reports ***;
%include 'E:\DSP\OUTCOMEMEASURES\Macro\OUTCOMEMEAS 01a.sas';
%include 'E:\DSP\OUTCOMEMEASURES\Macro\OUTCOMEMEAS 01a_pt2.sas';
%include 'E:\DSP\OUTCOMEMEASURES\Macro\OUTCOMEMEAS 01b.sas';
%include 'E:\DSP\OUTCOMEMEASURES\Macro\OUTCOMEMEAS 02.sas';
run;
*** EOF ***;

APPENDIX 2

EXAMPLE OF MODIFYED SAS PROGRAM - OUTCOMEMEAS01a.sas


(The amendments within the program are in bold)


libname kids 'e:\dsp\\data\kids count';
options nonumber nodate ls=132 ps=60 missing=0;
filename myoutput "E:\DSP\Reports\Outcomeas &dmmyy.\OHAN &dmmyy..lst";
%let BEGDT="&dmfd"d;
%let ENDDT="&dmld"d;
%let TITLE=&dmmn. &dmfdd., &dmyy. - &dmmn. &dmldd., &dmyy.;
***get determinations from Jan 1, 2005 - May 31, 2005;
proc sql;
create table deter as
select distinct a.case_id,a.deter_date,a.intk_id,b.decision_date
from kids.kc2cpsvic a,kids.kc1cpsacc b
where a.intk_id=b.intk_id and &BEGDT LE a.deter_date LE &BEGDT;
data days;
set deter;
count=1;
days = (deter_date - decision_date) + 1;
if 0 LE days LE 45 then days45=1;
else if 46 LE days LE 90 then days90=1;
else if days GT 90 then gt90=1;
proc sort data=days nodupkey;by intk_id;
proc summary data=days;
var count days45 days90 gt90;
class wrkr_offc;
output out=outdays sum=;
run;
*** redirects the output into the file ***;
proc printto print=myoutput;
run;
proc print data=outdays noobs split='*';
var count days45 days90 gt90;
id wrkr_offc;
title2 "&TITLE";
run;
proc print data=days;
var case_id intk_id wrkr_offc decision_date deter_date days;
format decision_date deter_date mmddyy8.;
title "Detail for Determinations Made During &TITLE";
run;
*** closing the printto connection ***;
proc printto;
run;
*** EOF ***;