How To Drive Your SAS Programs On Cruise Control

Here is a link to the PDF version of this article: How To Drive Your SAS Programs On Cruise Control

ABSTRACT

This paper introduces a simple, robust and efficient system to automate almost any SAS program or project, requiring submitting series of SAS programs. The reader will be exposed to the system’s big picture and technical details on how to build and apply it.

When created and applied on the first project, the new system provided the following results:

• Programmer’s time: reduced from 13 hours to 13 minutes.
That is 60 times or 6,000% improvement!
• Programmer’s efforts: from 9 to 3, on a scale from zero to 10.
Three times or 300% improvement!
• Programmer’s errors: reduced to zero.
Chances for human errors are eliminated!
• Programmer’s experience: from stressful to joyful.
Immeasurable improvement in the quality of work and life!

The system continues to provide similar results on all of its subsequent applications to different projects and even single programs.




PROBLEM

The usual workload of a SAS programmer frequently includes resubmitting a previously created program or series of programs to generate an outcome and deliver it to the customer. This work usually requires changes of certain parameters like dates, dataset names, variables names, titles and footnotes. Most of the time these tedious duties are done under pressure to deliver high quality results ASAP. Surfing through the program(s) to update them properly, being under pressure, very often ends in frustrating results due to increased number of human errors like: skipping a line for an update, typos, wrong dates, title errors, not reported in the SAS Logs and the list goes on.

The system, described here, eliminates all of the above problems.

One of its numerous advantages is that there is no need to have a very detailed knowledge of the SAS programs to which the system is applied. The first application of this system happened during the very beginning of a new contract. At that time, only general impression of what the programs are doing was available.




SOLUTION

SAS ON CRUISE CONTROL SYSTEM’S BIG PICTURE is described in the following four steps:

1. Find out the Main Steps in the process/project
2. Create a DRIVING Program, containing those steps
3. Create a SERVICE Program for each step
4. Create EXECUTION Programs for each step

Here is a flow-chart explaining how the system works:

Explanation of the Four Steps:

1. Find out the Main Steps in the Process.

A typical SAS project will contain some if not all of the following operations:
• Data Downloading
• Data Manipulations
• Generating Outcomes(Analyses, Modeling, Forecasting)
• Delivering Outcomes
• Data Archiving
• Email Management

2. Create a DRIVING Program, containing steps relevant to your project:

The Driving program is the WHEEL in the SAS on Cruise Control system
This is a SAS code where the programmer:
• Pushes the button “to select the desired speed” by entering one or several macro variables,
• Controls the entire process by triggering the Project’ Steps, one by one or all at once, when applicable.

The DRIVING program is the only program to handle with ones the system is properly built!


EXAMPLE: Driving Program

%LET DM=Jan2010; *Cruise Control PUSH BUTTON;


**************************************************************;
*** STEP ONE ***;
*** DOWNLOADING MONTHLY EXTRACTS ***;
**************************************************************;

%include "C:\Programs\DataDownloading.sas"; * Service Program1;

**************************************************************;
*** 1. Monitor the downloading process ***;
*** 2. Check the file size ***;
**************************************************************;
*** END of STEP ONE ***;
**************************************************************;


**************************************************************;
*** STEP TWO ***;
*** DATA MANAGEMENT, DATA MANIPULATIONS ***;
**************************************************************;

%include "C:\Programs\DataManagement.sas";* Service Program2;

**************************************************************;
*** 1. Check the Log for ERRORS ***;
*** 2. Check the updated file size ***;
**************************************************************;
*** END of STEP TWO ***;
**************************************************************;



3. Create a SERVICE Program for each step

The SERVICE programs perform “the fine tuning of the SAS engine”
They are SAS programs where:
• All of the required parameters for that particular Project Step are generated and stored into macro variables: dates, datasets names, variable names
• All new folders and libnames are generated

EXAMPLE: Service Program

* Creating global macro variables;
data _null_;
dmfirst=intnx('month',"01&DM"d,0);
dmlast=intnx('month',dmfirst,0,'end');
call symput('DMYYYYMM', put(dmfirst, YYMMN6.));
call symput('YDM', trim(left(put((dmfirst), YEAR4.))));
run;

%put _user_; * Checking the new macro variables;

* Creating a folder for the Stat Pack Reports;
filename papka PIPE "md ""&thepath\&DMYYYYMM""";

data _null_; infile papka; run;

* Triggering the programs for creating the reports;
%include "&thepath\Programs\XLAllCasesReportsNEW.sas";


4. Create EXECUTION Programs for each step

The EXECUTION Program represents “the running engine” in the SAS on Cruise Control system.
This is a SAS program where part or all of the Project’ Step outcomes are produced.

EXAMPLE: Execution Program
(This is just a part of the program, showing how the Service program macro variables are used.)

proc sql;
create table TMONTH&CY as
select distinct a.CASE, a.PGM, a.BENMONTH,
from chip.BENFY&BYDM a
where a.PGM=&FI AND
a.ISSDATE LE &DMLD AND
a.BENMONTH=&DMONTH ;
quit;



Applying the System into a small project:

Here is a flow chart showing how to apply the system to a single SAS program:

Explanation of the flow chart above:
The SAS on Cruise Control System can be applied to even a single SAS program in order to use the system’s benefits.

The top part of the program should contain the elements of the DRIVING program and mostly the ‘Cruise Control Push Button’, the macro variable(s). They are needed to generate all of the parameters in the SERVICE part. All efforts should be made to squeeze the number of those initial macro variables to one or two. Most of our projects need one macro variable, only. However, if a specific project requires 25 macro variables, for example, they should be there.

The next part of the program should perform all of the functions of the SERVICE program. Which means all of the required parameters to run the program properly should be generated.

At the end, the EXECUTION part should produce the desired outcome, using all of the macro variables from the SERVICE program.

The above format gives a simplicity and clarity to all of the ad-hocs requests a SAS programmer often has to respond. In addition, they can be easily recycled with minimum time and efforts in the future.



SAS ENGINE TUNING TIPS

Initial development of the system will require finding solutions of unusual problems which may pop up along. Solving those problems is the fun part of the game, which will widen your skill and give you the courage to go to the end of the task.

Here is a list of the problems we found out how to solve directly from SAS:

Control the flow of the SAS program with macros
Create custom time periods like state and fiscal year
Create new dataset at the beginning of a new time period
Debug a variable in a dataset
Download customer’s data hands free
Upload the outcomes to the client’s site
Archive the data
Manage emails
Quality control on proper execution of all of the steps

Some of the above problems required immediate solution, before the system could come into operation. Some of them, which were not so crucial for the process, we manage to solve later. Those solutions may give a hint or at least inspire you into finding yours.

All of the above solutions are available at: http://sasarticles.blogspot.com/.



HOW TO DEVELOP THE SYSTEM? STEP-BY-STEP GUIDE:

Applying SAS on Cruise Control system to Existing Project:
The easiest way to start with is to apply the system to an existing project as you can compare the results and see the benefits, as soon as the system is tuned properly. Before doing any changes, make sure that all of the programs you are about to modify are copied into new folder and properly renamed to avoid confusions in the future:

1. Identify the major steps in the process
2. Find out the what would serve as a PUSH BUTTON value to create all of the parameters you need to update the programs
3. Create the initial version of the DRIVING program with those steps and insert the PUSH BUTTON value into a macro variable

EXAMPLE: DRIVING Program

%LET DM=Jan2010; *Cruise Control PUSH BUTTON = Data Month;

4. Group the existing programs, according to the steps, outlined above
5. Create the SERVICE program for the first step by:
a. open the first EXECUTION program in the first step,
b. locate the first place where change needs to be made,

Example: EXECUTION program
data extract;
set master;
where '01jan2010'd LE issdate LE '31jan2010'd;
run;

c. go to the SERVICE program and generate macro variables containing the values to be replaced

Example: SERVICE program
data _null_;
dmfrst=intnx('month',"01&DM"d,0); * data month first day;
dmlast=intnx('month',dmfirst,0,'end'); * data month last day;
call symput('DMF', put((dmfrst), DATE9.)); * macro variable first day;
call symput('DML', put((dmlast), DATE9.)); * macro variable last day;
run;

d. copy the macro variable from the SERVICE program into the EXECUTION program

Example: EXECUTION program
data extract;
set master;
where "&DMF"d LE issdate LE "&DML"d;
run;

e. repeat the above for all of the necessary changes in the first EXECUTION program
f. repeat the above for all EXECUTION programs in the first step
6. Create the SERVICE programs for the next steps by repeating the above
7. Select and submit each step from the DRIVING program, remove the errors and make sure the outcomes are generated properly
8. Incorporate as many quality control steps as you need to make the entire process of verification simple and easy.
9. Compare the outcomes with the ones from the old process.
10. Once you are completely confident that the new system works, move it into production area.


Applying SAS on Cruise Control system to a New Project:
The only difference between applying the system into existing and new projects will be that the EXECUTION programs have to be written and brought to work in a regular way, first. Once this is done, you can apply the steps for Existing Projects above. However, having the experience from former applications of the system will inevitably give you the insights of what and how to be done for easy handling of those steps.



CONCLUSION
This system is a very powerful tool for introducing significant improvements in any area of application of SAS programming, if applied properly.

Wrapping every SAS program into the system becomes an invaluable skill, which gives you tremendous advantage on the work place and boosts your confidence in what you do.

The time one gains from applying the system, can be used to advance far ahead into your area of knowledge, expertise and career.

Any company manager can see how the system can boosts the productivity, significantly, by applying the information shared in this short article.



DISCLAIMER
The content of this paper is the work of the author and do not necessarily represent the opinions, recommendations, or practices of the organization he works for.



RECOMMENDED READING

1 Teo Gamishev, “How to Optimize Existing SAS® Programs for Generating Standard Reports”, http://sasarticles.blogspot.com/2010/06/how-to-optimize-existing-sas-programs.html

2 Teo Gamishev, “Affordable SAS® Tips”, available at
http://sasarticles.blogspot.com/2010/06/affordable-sas-tips.html

3 Teo Gamishev, “Hands Free Data Transfer Using SAS®”, available at
http://sasarticles.blogspot.com/2010/03/hands-free-data-transfer.html

4 Teo Gamishev, “How to Manage Emails from SAS®”, available at
http://sasarticles.blogspot.com/2010/03/how-to-manage-emails-from-sas.html



CONTACT INFORMATION
Teo Gamishev, M.S. in Statistics,
mailto:Teodor.Gamishev@ORS.SC.gov
SC Budget and Control Board,
Office of Research and Statistics
Columbia, SC 29201

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 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.

Hands Free Data Transfer

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

Download the PDF version from here

ABSTRACT
The first step in any data management process is transferring the data from the customer. There are many ways to do that and the most common one is to use proper FTP(File Transfer Protocol) software to navigate to the customer’s site and get the data. While this process is a fun in the beginning of your relationship with the customer, it may become just another boring step in your data management process. There is a way to reduce that step to a single click of the mouse. This article will show you how to use SAS to transfer data, located on a different type of servers.

INTRODUCTION
If you are dealing with multiple projects, requiring downloading data from the customer and uploading back reports on a regular basis, you definitely are looking for ways to automate that step and make it less time consuming for you. Here you’ll find several examples of how this could be done. Hopefully, they will work for you or inspire you to do your research and find a proper solution.

PROBLEMS
Different projects are using different servers, which demands different FTP applications to access their data. Accessing different folders and transferring the data into proper folders may become a hustle and creates an environment for errors and mishaps.

SOLUTIONS
There is a very simple way to eliminate all these activities and reduce them into a single click of the mouse: Delegate the entire data transfer to SAS. A SAS program can do the access, login, folder navigation and copying the data in the proper folder on your site.

The following 3 examples will give you a hint on how that could be done:


EXAMPLE #1: Accessing DASD area on a Mainframe
The data in the DASD area on the Mainframe is normally accessed via Hummingbird FTP. Here is one way to access the data trough SAS, without SAS ACCESS:

*** 1. Creating a batch file;
data _null_;
file 'C:\Data\Downloads\getDATA.bat' * the batch file;
noprint notitles;
put "ftp -s:%0";
put "goto END";
put "open 144.4.40.44";
put "&tsoID";
put "&tsoPW";
put "CD 'MY.DATA'";
put "LCD C:\Data\Downloads\MyData";
put "GET FILE138 EMPR";
put "GET FILE150 T1F1";
put "GET FILE152 T1F2";
put "GET FILE153 T1F3";
put "GET FILE157 T1F4";
put "bye";
put ":END";
run;

*** 2. Running the batch file to FTP the new data from the Mainframe;
options xwait xsync; * synchronizing the downloading process and SAS;
x " C:\Data\Downloads\getDATA.bat ";

*MONITOR THE DOWNLOADING THROUGH THE DOS PROMPT WINDOW POPPING UP *;

*** 3. Deleting the batch file;
Filename delit PIPE "del C:\Data\Downloads\getDATA.bat";
Data _null_; Infile delit; Run;

EXPLANATION NOTES
A batch file is created in the Step #1. The user ID and the password are passed to the server as macro variables. Those macro variables should be created in your AUTOEXEC.sas file or the access to the SAS program should be limited to you, only. The CD(Change Directory) command is navigating to the data folder on the Mainframe. The LCD (Local Change Directory) command is pointing to your destination folder.
"GET FILE150 T1F1" copies FILE150 from the Mainframe, renames it to T1F1 and saves it into the local data folder.
Once the batch file is created, SAS can activate the file using X command (Step #2). The option XWAIT forces SAS to wait for you to type EXIT on the COMMAND window, before moving to the next step. I like to watch the COMMAND window, which pops up after the X command. When sure that everything is executed properly, I will type EXIT on the Command window and then the SAS program will move to the next step. Automation is good, however quality control needs to be applied on each step to make sure that nothing is messed up. Here comes the role of the human eye to verify that and then move forward.
The next option XSYNC causes the operating environment command to run synchronously with your SAS session. That is, control is not returned to the SAS System until the command has completed.
In step #3 the batch file is deleted using the Filename PIPE statement. Considering the fact that the batch file contains a sensitive data like your login ID and Password, this step is mandatory one.

EXAMPLE #2: Accesing a Secure FTP server via psftp

*** 1. Creating a text file with all commands;
data _null_;
file "C:\Data\UploadReports.txt"
noprint notitles;
* uploading reports and data for Client1;
put "cd /chroot/home/client1/Reports";
put "del *.*";
put "lcd C:\Reports\StatPack";
put "mput *.*";
put "cd /chroot/home/client1/Data";
put "del *.zip";
put "lcd C:\Zipfiles";
put "mput *.zip";
* uploading reports and data for client2;
put "cd /chroot/client2/Reports";
put "del *.*";
put "lcd C:\Reports\StatPack";
put "mput *.*";
run;

*** 2. Executing the commands above;
options xwait xsync; * synchronizing the downloading process and SAS steps;
x "C:\sshtools\psftp.exe -P 2251 -b C:\Data\ UploadReports.txt
-l &sftpID -pw &sftpPW courier.state.gov";

*** 3. Deleting the text file;
Filename delit PIPE "del C:\Data\Uploads\UploadReports.txt";
Data _null_; Infile delit; Run;

EXPLANATION NOTES:
The steps here are very similar to the steps in the Example#1. The Step 1 we are creating a text file, containing all commands the software need to execute. In this case we are uploading the final reports to a secure server. In case of several clients receiving different set of reports, that example could be very helpful.
In Step 2, SAS is triggering the PSFTP.exe file via X command. The PSFTP software should be installed in advance. It is free application for secure file transfer, available on Internet. The X command contains the path to the PSFTP.exe and the name of the server to connect to. The following options are needed to complete the task:
-P: specify a port number
-l: specify the user name to log in – in this case the log in is in a macro variable
-pw: specify a password – stored in a macro variable, also
-b: display batch commands as they are run – in this case they are stored in the text file, created in Step 1
The macro variables, containing the user ID and the password can be created within the AUTOEXEC.sas to avoid hard coding them in the program.

Step 3 deletes the text file with the batch commands to clear the space.


EXAMPLE #3: Accessing WS server:

*** transferring the data;
options xwait xsync; * synchronizing downloading and SAS steps;
X "c:\progra~1\coreftp\coreftp.exe -site CAPS -d /*.txt -p D:\data\capss";

EXPLANATION NOTES:
This is the shortest example of how to access a WS server, using Core FTP Light free client software for windows. In this case Core FTP has to be installed and a session, containing all prerequisites to connect to the server (user ID, password, port number), has to be established and saved using a particular name. In our example that session is named CAPS. The X command is triggering the coreftp.exe and the following options are in use:
-site: site session/profile name as stored in the Core FTP - CAPS
-d: remote directory to download files from – in this case we are downloading all test files - /*.txt
-p: destination directory - D:\data\capss

If you need to upload data or reports, then use the following example:
*** transferring the data;
options xwait xsync; * synchronizing downloading and SAS steps;
X "c:\progra~1\coreftp\coreftp.exe -site mysite -u /upload/data.txt -p /remote/store";

Other available options are:

-ZIP: zipping the file(s)
-AUTONAME: naming the files after zipping
-output and -log parameter are for information about transfers
-s: silent mode – the application window doesn’t open
-o: overwrite if file exists
-oa: overwrite *all* if transfering a folder or directory of files.
-on: overwrite files that are newer than existing files.
-og: overwrite destination file if source file size is greater.
-ol: overwrite destination file if source file size is less (smaller).
-os: skip file if exists
-r: resume if file exists
-ra: resume *all* files that exist.

Here is an example of zipping the files before uploading them:
X "c:\progra~1\coreftp\coreftp.exe -O -ZIP -AUTONAME -u c:\IBM -site mysite -p /public_html/store/ -output c:\temp\output.log -log d:\temp\log.log –s";

-output and -log parameter are for information about transfers. They are optional but listed for informational purposes.

AUTOMATING THE ENTIRE PROCESS is a preferred way to go when dealing with repetitive tasks. In this case the entire downloading and uploading programs can be saved and called from within another DRIVING program. Here is an example of the triggering code in the DRIVING program:

EXAMPLE#4: Downloading Driving program:

***************************************************************************;
*** STEP ONE ***;
*** DOWNLOADING MONTHLY EXTRACTS ***;
***************************************************************************;

**** MAKE SURE THAT THE ID AND PASSWORD ARE SUBMITTED FIRST ***;
%include "C:\Programs\getDATA.sas";

*** MONITOR THE DOWNLOADING THROUGH THE DOS PROMPT WINDOW POPPING UP ***;
*** CHECK THE FILE SIZE ***;

EXPLANATION NOTES:
The first step in each data project is to get the data from the customer. Once the SAS program dealing with that process is ready and tested for proper work, it can be saved and called from within another DRIVING SAS program. All the programmer needs to do is to select and submit the part of the code, triggering the data downloading process. Now the entire focus is on the quality and all the tedious typing and coding is removed. The best part is that the room for coding errors is practically zero.

EXAMPLE#5: Uploading Driving program:

***************************************************************************;
*** STEP FIVE ***;
*** UPLOADING THE REPORTS AND THE SAS TABLES INTO SFTP Server ***;
***************************************************************************;


%include "C:\Programs\UploadingReports.sas";

*** CHECK ALL NEW FILES ARE UPLOADED IN THEIR RELEVANT FOLDERS ***;

EXPLANATION NOTES:
Once we are done with the data manipulations and reports are ready to upload to different customers, the whole task can be triggered again from within the DRIVING program. Select and submit that part of the code invoking the uploading program, dealing with reports and data distribution and focus on quality control. No mishaps, no errors, no boring job to do. Enjoy the power of automation and focus on quality control.


CONCLUSION
This paper offers one of the many possible solutions on how to remove the repetitive downloading and uploading tasks by using the power of SAS. The above examples are giving hints on how to deal with different servers. In addition examples are given on how the whole process can be automated. It can be very practical and time saving to implement those examples in the daily routine.
SAS Global Forum 2007 Coders’ Corner

REFFERENCES
SAS Institute Inc. 2002-2008 “SAS OnlineDoc® 9.1.3” Available at: http://support.sas.com/onlinedoc/913
Gary Sargent 2006 “Core FTP LE Client Instructions”
Available at:http://www.ic.nhs.uk/webfiles/Services/Datasets/MHMDS/MHBScoreftp.doc
Simon Tatham 2001 “PuTTY Manual”
Available at: http://the.earth.li/~sgtatham/putty/0.52/htmldoc/Chapter6.html


ACKNOWLEDGMENTS
Special heartfelt thanks to Jim Moore, IT Technician at BCB-ORS for his helpful hints and guidance.


CONTACT INFORMATION

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

Teo Gamishev
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 Manage Emails from SAS

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

Download the PDF version from here

ABSTRACT
Setting up part of your email workload on autopilot from within SAS can eliminate the need of redundant work and significantly increase your job efficiency when dealing with repetitive tasks. SAS has built in emails generating engine, which when tuned properly, works very well with every email provider. Here you’ll find an example of fully automated process of emailing reports to several customers, using SAS.

INTRODUCTION
If you are emailing reports to your customers on a regular basis, then you definitely can benefit from the material laid out in this article. At least, it will motivate you to start testing, tweaking and having fun reading your first, successfully delivered emails. At the end you may end up using and enjoying this fully automated emailing system.

PROBLEMS
Here is a list of the problems covered on the following pages:
How to tune up your program to successfully deliver emails to a list of customers and supervisors;
How to generate the emails;
How to automate the entire process;

SOLUTIONS
TUNING UP SAS EMAIL ENGINE is not a rocket science, however I found it was not easy to locate the proper instructions on that. So, here is how it can be done:

EXAMPLE:
OPTIONS EMAILAUTHPROTOCOL=NONE
EMAILHOST=”mail.my.state”
EMAILID=”My.Name@MyCompany.com”
EMAILSYS=SMTP;

Email providers should be accessible from within the company without necessity of logging in and using a password. If that is not possible, then you should change the
EMAILAUTHPROTOCOL=LOGIN

After failing many times when trying to connect to my email provider, using the login protocol, our IT person gave me the hint of using a different host name to connect to it, without using user ID and password. You need to do your research and type the right hostname under EMAILHOST option, in order to assure smooth operation.

The EMAILID option contains your email address. If you just type a name or invalid email that may cause the email providers to filter out your messages or force them into the junk box. On another side, our email provider recognizes the fact that the sender and one of the recipients under the CC list are the same and refuses to deliver a copy of the emails to my Inbox. Your email provider may not be so smart, but you can always be creative here and use another email of yours to email a copy to yourself, if so needed.

The EMAILSYS option should be adjusted to SMTP(Simple Mail Transfer Protocol). The other possibilities are: MAPI(Messaging Application Program Interface) or VIM(Vendor Independent Messaging) . Those two may require login and password to access the server.

Here is how you tune your program for that:
OPTIONS EMAILAUTHPROTOCOL=LOGIN
EMAILHOST=”ciomail.my.state”
EMAILID="MyLogin" /* your email login */
EMAILPW="MyPass" /* your email password */
EMAILSYS=MAPI;

The fact that I have to type and leave sensitive information in my program doesn’t make me feel good about that. My advice is to move that entire OPTIONS statement into your AUTOEXEC.SAS file. However, make sure that your profile is in use every time when open a new SAS session. Not all servers, where SAS has been installed, are set up that way. Most of them will read the information in your AUTOEXEC.SAS during the first session and will skip it for the next sessions. Read the message into your log right after opening the second session. If you see the following message posted there, you will know that the OPTIONS statement and all other statements in the AUTOEXEC.SAS has not been activated. Copy and submit them from within your session, first.

NOTE: Unable to open SASUSER.PROFILE. WORK.PROFILE will be opened instead.
NOTE: All profile changes will be lost at the end of the session.

Once all of the above tuning is done, it is a good idea to check the options by submitting the following code:
proc options group=email; run;

GENERATING THE EMAILS from within SAS is an easy task to do. Let’s take a look at the example below:
EXAMPLE:
FILENAME outbox EMAIL
TO=”Perfect.Client@agency.gov”
CC=”Smart.Manager@company.com”
SUBJECT="Monthly Reports Uploaded"
;

data _null_;
file outbox;
put "Good Morning, ";
put " ";
put "The monthly Reports are uploaded";
put "into your Secure FTP folder.";
put " ";
put "Let me know if you have any questions. ";
put "Thank you and have a nice day";
put " ";
put "My Name";
put "ph:898-9999";
put "My.Email@company.com";
run;
There are two ways to address the recipient of the email:
By using the TO= option
FILENAME outbox EMAIL
TO=”Perfect.Client@agency.gov”
or
TO="My Customer "

Or without using the TO= option and listing the recipient as an argument right after the EMAIL device type in the FILENAME statement:
FILENAME outbox EMAIL ”Perfect.Client@agency.gov”

Listing multiple recipients can be done by using the TO= option:
FILENAME outbox EMAIL
TO= (”Perfect.Client@agency.gov”
”Dream.Client@agency.gov”
”Polite.Client@agency.gov”)
In a similar way, multiple recipients can be listed under CC=:
CC= (”Best.Supervisor@company.gov”
”Smart.Manager@company.gov”
”Awesome.Boss@company.gov”)
Other useful options are:
FROM= /* your email */
BCC= /* recipient(s) that you want to receive a blind copy */
REPLYTO= /* who will receive replies */
SUBJECT= /* subject of the message */
ATTACH= /* physical name of the file(s) to be attached */

Make sure to enclose the SUBJECT= into double quotes, If the subject contains special characters or more than one word (that is, it contains at least one blank space).
EXAMPLE:
SUBJECT=Sales
or
SUBJECT="June Sales Report"
Note: If you do not enclose a one-word subject in quotation marks, it is converted to uppercase.
One or more files can be attached to the email:
EXAMPLE:
ATTACH=u/myfiles/report.txt"
or
ATTACH=('D:\Reports\June2001.txt' 'C:\Reports\July2001.txt')
or
ATTACH="user.misc.pds(member)"

AUTOMATING THE ENTIRE PROCESS can give you the flexibility to modify and the power to send the email(s) with a single click. All the variables can be controlled within a one program (driving program) and the execution of the emailing can be done from another program (work program). Below is an example on how that can be achieved:
1. The following code is located in the DRIVING program:

**********************************************************************;
*** STEP X ***;
*** EMAILING TO MY CUSTOMERS ***;
**********************************************************************;

*** PLEASE CHANGE THE DATA BELOW, AS NECESSARY;

*** reduces the hustle, when the programs are moved to a new server***;
%let thepath=D:\Projects\FS;

%let myemail=My.Name@MyCompany.com;
%let myname=My Name;
%let myphone=(803)888-7777;

%let clients=”Perfect.Client@agency.gov”
”Dream.Client@agency.gov”
”Polite.Client@agency.gov”;
%let CCline=”Best.Supervisor@company.gov”
”Smart.Manager@company.gov”
”Awesome.Boss@company.gov”;

*** The program below emails the notifying message to all clients;
%include "&thepath\Programs\EmailsFS.sas";

*** SELECT and SUBMIT the above step;

**********************************************************************;
*** END OF STEP X ***;
**********************************************************************;
The driving program manages the values of all variables as the email sender’s data, clients’ data by storing them into macro variables.
We do not recommend emailing sensible data using emails and that’s why the attachment option is omitted here.
Once all those variables are handled, the execution is done by the work program EmailsFS.sas.
Here is how it may look:
***************************************************************************;
*** TUNING THE EMAIL OPTIONS and EMAIL DEVICE ***;
*** CREATING THE MESSAGE and EMAILING TO ALL CLIENTS ***;
***************************************************************************;

*** check all clients and other recipients are properly recorded ***;
%put _user_;

*** setting up the EMAIL options ***;
options
EMAILHOST="mymail.server.com"
EMAILID="&myemail"
EMAILSYS=SMTP;

*** check the log for all options being properly set up ***;
proc options group=email;run;

*** tuning the EMAIL device options ***;
FILENAME outbox EMAIL
TO=(&clients)
CC=(&CCline)
SUBJECT="Monthly CHIP Reports Uploaded"
;

data _null_;
file outbox;
put "The monthly Reports are uploaded";
put "into your Secure FTP folder.";
put "&myname";
put "&myphone";
put "&myemail";
run;
*** EOF ***;
All of the macro variables are plugged into the proper options and that assures correct and immediate execution.
Once the values are correctly stored into the driving program, all of the work is reduced to a single click of the mouse.

CONCLUSION
This paper offers one of the many possible solutions on how to use the SAS EMAIL device. It can be very practical and time saving to implement it in the daily routine. Automation of the tedious work tasks is always fun and motivates for more creativity at work.
SAS Global Forum 2007 Coders’ Corner

REFFERENCES
SAS Institute Inc. 2002-2008 “SAS OnlineDoc® 9.1.3” Available at: http://support.sas.com/onlinedoc/913


ACKNOWLEDGMENTS
Special heartfelt thanks to Jim Moore, IT Technician at BCB-ORS for his helpful hints and guidance.


CONTACT INFORMATION

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

Teo Gamishev
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.