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 ***;

Affordable SAS® Tips

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

Download the PDF version from here

ABSTRACT
SAS continue to impress with a large number of powerful tools, providing the user with almost unlimited possibilities to find the best solution for any programming situation. While enjoying the life of SAS programmer, you may find yourself surrounded by various problems, demanding urgent decision. This article contains some of the answers you may need and use. The following tips do not require any special training or knowledge in order to be applied into your coding arsenal. They are really affordable.

PROBLEMS
Here is a list of the problems covered on the following pages:
How to control the flow of the SAS program;
How to create custom time periods, like fiscal or state years;
How to automatically create new data set in the beginning of the new time period;
How to generate custom date FORMATs and INFORMATs;
How to read large number of datasets located in the same folder;
How to clean dataset variables from bugs;

SOLUTIONS

CONTROL THE SAS PROGRAM FLOW WITH MACROS turns to be the very effective and quick solution. You can perform a check for certain condition, for example; check the number of observations, and based on that to continue or stop execution of the program. Wrapping the program with macro is very simple and assures that the program will be executed or suspended on certain conditions.

EXAMPLE

1. *** Check for matching numbers of observations ***;
2. %let clienth=257866;
3. %macro check;
4. proc sql noprint;
5. select count(*) into :nbr_rows from tmpclih;
6. quit;
7. %if &nbr_rows = &clienth %then %do;
8. %put CHEK OK, MY_TABLE HAS CORRECT NUMBER OF OBS -- &nbr_rows ;
9. *** UPDATE MASTER FILE ***;
10. PROC APPEND BASE=DSS.CLIHIST DATA=TMPCLIH;
11. RUN;
12. %end;
13. %else %do;
14. %put * ERROR * ERROR * ERROR * ERROR * ERROR * ERROR * ;
15. %end;
16. %mend check;
17. %check

In lines #4 to #6 the number of the observations in the data set is stored in a macro variable nbr_rows, using INTO statement from within the PROC SQL. INTO statement is similar to CALL SYMPUT in a data step. The number is compared to the control number on line #7 and a statement is written to the log, if the logic is TRUE. In case of a FALSE outcome the lines from #8 to #12 are simply skipped and no appending takes place. Line #14 generates an entry in the log, flagging for discrepancies between the numbers.

CREATING CUSTOM TIME PERIODS like fiscal year, state year, semi year or quarter is an easy task to perform when using INTNX function. The form of the function is:

INTNX( interval, from, n <, alignment > )

The general form of an interval name is:

name<.shift-index>

Multiplier - specifies a multiple of the interval. It sets the interval equal to a multiple of the interval type.
For example, YEAR2 consists of two-year, or biennial, periods. Shift-index - specifies the starting point of the interval. By default, the starting point is 1. A value that is greater than 1 shifts the start to a later point within the interval. The unit for shifting depends on the interval. For example, YEAR.7 specifies yearly periods that are shifted to start on the first of July of each calendar year and to end in June of the following year.
SAS uses the arbitrary reference time of midnight on January 1, 1960, as the origin for non-shifted intervals. Shifted intervals are defined relative to January 1, 1960.
The following example shows how to create calendar, fiscal and state (beneficiary) yearly time periods.

EXAMPLE

1. data _null_;
2. cydmv=intnx('year',intnx('month',today(),-1),0); * cal year data month;
3. bydmv=intnx('year.7',intnx('month',today(),-1),1); * bene year data month;
4. fydmv=intnx('year.10',intnx('month',today(),-1),1);* fisc year data month;
5. call symput('CYDM',put(cydmv,YEAR2.)); * cal year data month;
6. call symput('BYDM',put(bydmv,YEAR2.)); * bene year data month;
7. call symput('FYDM',put(fydmv,YEAR2.)); * fisc year data month;
8. run;
9. %put _user_;

The cydmv variable, containing calendar year, is included here for the purpose of being able to compare it with bydmv for state and fydmv for fiscal years. The last value of 1 on lines #3 and #4 forces the values of the state and fiscal years one year ahead of the current calendar year. It is very convenient to use the above macro variable for creating generic dataset names like: old&BYDM indicating data set, containing state year data, here. Those generic names can be easily manipulated in SAS codes when handling with monthly or yearly datasets and reports.

AUTOMATED DATASET CREATION IN THE BEGINNING OF THE NEW TIME PERIOD
It may occur in many occasions that you need to create a new dataset in the beginning of the new time period, for example a new calendar or fiscal year. One simple solution could be found here.
In the following example, a SAS macro newyearset is comparing the year of last data month &DMYY and the year of the previous data month &BDMYY on line #3. If they happened to be different, then a new dataset DSS.ccomp&DMYY will be created, containing the data from the last month, only. It is assured by the statement if benmonth=&DMYYYYMM on line #7.
The tip here is to initially refer to the year of the month before the last data month. By this way you will be able to get the old year dataset and create the dataset for the New Year, if necessary.

EXAMPLE

1. * MACRO checking for a new year and creating a new year set *;
2. %macro newyearset;
3. %if &DMYY NE &BDMYY
4. %then %do;
5. data DSS.ccomp&DMYY;
6. set DSS.ccomp&BDMYY;
7. if benmonth=&DMYYYYMM;
8. run;
9. %end;
10. %mend newyearset;
11. %newyearset;

Wrapping the code in a macro assures full control over the flow of the program in case of a TRUE and FALSE outcomes.

CREATING THE MONTH NUMBER WITHIN A QUARTER is a task you can solve by using INTCK time interval function. It counts the number of interval boundaries between two dates or between two datetime values. The form of the INTCK function is: INTCK( interval, from, to )

In the following example X on line #3 - indicates from and Y on line #4 - indicates to variables, calculated separately
for simplicity. If the REPORT month and the FIRST MONTH of the quarter are the same, the INTCK outcome will be
zero. For that purpose 1 is added to the value of Z on line #5. The number Z is assigned to a macro variable on line
#6 and used to create a generic data set name on line #9.

EXAMPLE

1. * Creating a macro variable for # of the REPORT month within the quarter;
3
2. data _null_;
3. X=intnx('qtr',intnx('month',today(),-2),0); *from date-the first qtr day;
4. Y=intnx('month',today(),-2); * to date-the first day of the REPORT month;
5. Z=1+intck('month',x,y); * measuring the month interval b/n x and y;
6. call symput("qtrmth",trim(left(put((z),1.))));* creating macro variable;
7. run;
8. %put &qtrmth;
9. data qmth&qtrmth;
10. set regcty;
11. run;

CUSTOM DATE FORMATS AND INFORMATS are very often desperately needed. It turns out that by using them simultaneously, you can solve any unusual date format issue. The first step is to create the desired FORMAT with the powerful DIRECTIVES in PICTURE statements. Then you can use the newly created FORMAT to generate the relevant INFORMAT.

EXAMPLE

1. * creating user defined FORMAT using PICTURE statement and DIRECTIVES;
2. proc format ;
3. picture myfmt low-high = '%Y/%b/%d' (datatype = date) ;
4. run ;
5. * creating control data set for user defined INFORMAT;
6. data infmt ;
7. retain fmtname "yearmd" type "I" ;
8. do label = "01jan1999"d to "01jan2003"d ;
9. start = put(label,myfmt11.) ;
10. start = trim (left (start) ) ;
11. output ;
12. end ;
13. run ;
14. * output the custom INFORMAT;
15. proc format cntlin = infmt ;
16. run ;
17. * use the custom INFORMAT;
18. data _null_ ;
19. _txtdate = "2002/APR/17" ;
20. _sasdate = input (_txtdate,yearmd.) ;
21. put _sasdate = ;
22. run ;
23. LOG: 15447

Here is a list of the most common DIRECTIVES in use:
%A Full weekday name – Monday, Friday
%a Abbreviated weekday name – Mon, Fri
%B Full month name – April, October
%b Abbreviated month name – Apr, Oct
%Y Year with century – 2005, 2020
%y Year without century – 5, 20
%0y Year without century – 05, 20
%m Month as a decimal – 1, 11
%0m Month as a decimal – 01, 11
%U The week number of the year – 1, 52
%0U The week number of the year – 01, 52
%j The day of the year – 1, 75, 352
%0j The day of the year – 001, 075, 352
%d Day of the month as a decimal number - 1
%0d Day of the month as a decimal number - 01
%w Weekday as a decimal number – 1, 5, 7
%p The day half – AM, PM
%H The hour – 24 hrs clock – 4, 17, 23
%0H The hour – 24 hrs clock – 04, 17, 23
%I The hour – 12 hrs clock – 4, 5, 11
%0I The hour – 12 hrs clock – 04, 05, 11
%M The minute – 1, 19, 25
%0M The minute – 01, 19, 25
%S The second – 5, 15, 35
%0S The second – 05, 15, 35

In combination with the possibility of including text, as in the code below, these DIRECTIVES can produce almost any kind of date value you need.

EXAMPLE

1. proc format ;
2. picture long low-high = '%dth %B is a %A ' (datatype = date) ;
3. data _null_ ;
4. myday = "17apr2006"d ;
5. put myday : long40. ;
6. run ;
7. LOG: 17th April is a Monday

CONCATENATING MULTIPLE DATA SETS from a pool of datasets is a regular task for a SAS programmer. One simple approach to read all datasets in the pool is to store the names as a character string in a macro variable.
Whenever a new libname is assigned, SAS immediately collects all the data from the libname address. You can access that information via sashelp vtable, as shown on line #5. In the example below, the datasets names are read from the sashelp vtable and stored in a macro variable mysets, as seen on line #4, separated by blank spaces. The where statement on line #6 assures that only sets from the libname OLD will be selected. Some additional restrictions are applied on line #7. Finally, on line #9, all datasets are concatenated in OLDSETS.

EXAMPLE

1. LIBNAME OLD 'e:\dss\fipacket\applications';
2. proc sql noprint;
3. select 'OLD.'memname
4. into: mysets separated by ' '
5. from sashelp.vtable
6. where libname='OLD'
7. and memname NE substr(memname,6,3)='FLS';
8. quit;
9. DATA OLDSETS; SET &mysets; RUN;

DEBUGGING A VARIABLE IN A DATA SET is something you may need to do due to a customer or project requirements.
In some occasions, certain observations within a particular variable may contain a bug (unreadable sign) you want to get rid of. The following code is offering a simple answer to that task:

EXAMPLE

1. data mydata (rename=(cleaned_variable=my_variable));
2. set mydata;
3. cleaned_variable = my_variable;
4. do i = 1 to length(my_variable);
5. substr(cleaned_variable,i,1) =
6. compress(substr(my_variable,i,1),
7. compress(substr(my_variable,i,1), collate(32, 126)));
8. end;
9. drop i my_variable;
10. run;

No new dataset is generated here. The cleaned_variable is created for the purpose of debugging and is immediately renamed to the original variable my_variable, as you can see from lines #1 and #9 in the example. In line #7 the program is cleaning all ASCII codes with a number outside the 32-126 scope. The range of 32-126 contains all numbers and characters, only. In general, all you need to do is to replace mydata and my_variable with the name of your data set and variable to be cleaned, submit the program and the job will be done.

CONCLUSION
This paper offers solutions for common problems new SAS user may encounter during the daily programming routine. They are simple to implement and really affordable. Hopefully the above tips will reduce the amount of tedious work and leave more space to enjoy creative coding.

REFFERENCES
SAS Institute Inc. (2006). “What’s New in SAS® 9.0, 9.1, 9.1.2, and 9.1.3.” Cary, NC: SAS Institute Inc.
Venky Chakravarthy. 2002. “Have a Strange DATE? Create your own INFORMAT to Deal with Her.” Proceedings of
the Twenty Seventh Annual SAS Users Group International Conference, Orlando, FL, 101-27.

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.

How to Schedule SAS Jobs on Windows Server

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

Download the PDF version from here

ABSTRACT
Efficient use of the server’s time and managing your time in today’s environment, overloaded with data, demands implication of more and more completely automated processes running 24/7 on autopilot. For example, most of the customers chose to transfer their data using secure data transfer channels to protect it. That creates a huge load on the servers and consumes significant amount of programmer’s time. One of the solutions proposed here is to use Windows Task Scheduler and move the routine processes out of the regular work time. That will reduce the load of the server, during the normal work hours and allow the programmers to use their time in a more efficient way.

INTRODUCTION
Automating and moving the repetitive processes away from the regular work hours is a smart way to free yourself from the boring routines and concentrate on producing solutions for the task at hand. If you are dealing with multiple projects, requiring load of repetitive jobs like downloading data from the customer and uploading back reports on a regular basis, you definitely are looking for ways to automate those steps. In this article you’ll find how to use Task Scheduler on Windows server to trigger your SAS program(s). Hopefully, that example will help you or inspire you to do your research and find a proper solution for your specific situation.

PROBLEM
Repetitive processes are occupying significant amount of server’s and programmer’s time in today’s data overloaded world.

SOLUTION
Moving the routine processes away from the regular work time could be an important solution for you and your company. One way to complete that task on a Windows server is to schedule SAS jobs using Task Scheduler which comes as a standard application.

The following steps are showing how to set up your program to be triggered at a desired time using Task Scheduler on Windows Server 2008.

STEP-BY-STEP PROCESS

The plan of action:
1. Schedule a test run of the job during the first possible off-hours period, for ex: 3AM next day
2. As soon as the job scheduling is complete, force the Task Scheduler to trigger it immediately. Look for errors and bugs in the process and remove them.
3. Check the results of the overnight test run next morning.
Look for errors and remove them.
4. Schedule the job as you’d normally need it to be run.
Check the log and the quality of the program’s output for possible errors.

SAS software has endless number of procedures and tools which could be used to assure smooth and easy quality control steps and monitoring of the job done, so that we can assure the final product has been delivered. In order to keep the focus on the topic, I am not going to distract you with those additional solutions in this paper. However feel free to do your research and incorporate the results into your work.

Here are the detailed steps as done on Windows server 2008:

Open Task Scheduler by clicking Start-Administrative Tools-Task Scheduler
(click on the image to enlarge it)




Click on Create Basic Task on the top right corner




Enter the Name of the job and a short description and click Next




As you are going to test the job first, Select One time option on that window and click Next



Select the first possible day and time out of the regular work hours. For example 3AM next day. We are going to test the program immediately after the scheduling is completed, regardless of what the trigger time is




Select Start a program, however look at the other options here. They can be helpful for other projects




On the next window, shown below, Task scheduler has to be instructed on which application to open and the specific program within it:
Here is the exact command line:
"C:\Program Files\SAS\SASFoundation\9.2\sas.exe" -sysin "D:\Projects\MySASprogram.sas" -log "D:\Projects\mylog.log"

There are 3 parts in this command line:
a. The first one instructs the Task scheduler to open SAS
b. The –sysin argument specifies which SAS program to be triggered
c. The –log argument instructs where the log to be saved

There are at least two reason to include –log argument in the command line
You need to check the log for proper execution of the program
You may not have administrative access to the Programs folder on the server and that will trigger an error preventing the program from execution. Why? An attempt will be made to save the log in the Programs folder, by default, if not other settings have been done beforehand.

The double quotes make sure that if any blanks are included in the path, they will not confuse the command execution.

There should be a blank space before and after each argument.

Here is where the command line above has to be pasted.
Then click Next.




Task scheduler will ask you to confirm the program and the arguments, Click Yes.




Check the box next to Open the Properties dialog for this task and click Finish




On the Property dialog select the radio button next to Run whether user is logged on or not. If the task is going to be scheduled later on for a midnight run you may not be logged on at that particular time




Now is the time to test if the program will be triggered correctly. If you do not see the program listed under the Names, click on Task Scheduler Library on the top left corner.

Point the mouse over the program’s name as it is shown on the screen shot below and Right click. Left click on Run. The program should be triggered and the results generated properly. Check the log for errors.

You may encounter unexpected system errors and need to consult your IT department on solving those issues.




Once the first test is done, you have to wait for the results of the overnight test. On the next morning, check if the program has been triggered and all of the expected outcomes have been properly generated. Remove the errors and reschedule the next testing time for the next off-hours period.

If everything is OK, then reschedule the job for the regular times when you’d like the process to be executed. Below are the details of how this can be done.

Point the mouse over the program name, Right click and select Properties from the drop-down menu.




Click on Triggers tab




Click on Edit button below




Start editing from the top field. I use On a schedule option, however see what other choices you have here and make yours




The Settings field has four options and each one has additional sub-options as shown below. One time option is the simplest: set up the date and the time you want the program to run




The Daily option gives you the opportunity to submit the program every day, or every other day, or … you get the idea




The Weekly option gives you the choice to trigger the program based on a weekly intervals and the weekdays within those intervals




Monthly option is giving you the flexibility to select any months, days or weekdays periods




Pay attention to the Advanced settings field, where you can stop the task from running in excessively long time, set up expiration date and most importantly deactivate or activate the scheduled job




Once you are done, click on OK button, again




Now the scheduler will ask you for the password in order to confirm the authorship and save the changes




Entering the above password raises the question what will happen to the scheduled job when you are prompted by the system to change it on a certain time intervals. There are two possible solutions:
Enter your new password into the program by manipulating with the scheduled job Properties
Ask the IT technicians to create a special user with a permanent password
Here is how you can change the User:
Point the mouse over the program name, Right click and select Properties from the drop-down menu.




Click on Change User…




Type the new user name and click OK




There can be many variations of the solution shown above:
The process can be enveloped into a batch file and that program can be triggered from the Task scheduler
Several processes can be incorporated into a Windows Script File called from Task scheduler


CONCLUSION
This paper offers one of the many possible solutions on how to move the repetitive processes away from the regular work hours, reducing the load of the servers during the work time and giving yourself a chance to use your time in a more efficient way. Adding this tip into your arsenal will only increase your value as a highly productive programmer desirable for any team to be part of.


REFFERENCES
Faron Kincheloe. 2008 “While You Were Sleeping - Scheduling SAS® Jobs to Run Automatically”. Available http://www2.sas.com/proceedings/sugi27/p276-27.pdf


ACKNOWLEDGMENTS
Special thanks to Jose Santiago, IT Technician at BCB-ORS for his help.


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.