Monday, May 15, 2017

Hack 5.1 Encrypting SAS Macros




SAS Programming Professionals,

Did you know that you can encrypt SAS macros so that nobody can see the source code?

This can be handy if cases where you have SAS macros that you must deliver to a client but do not want to divulge the source code because it employs proprietary corporate algorithms.  The client can execute the macros, but cannot see the source code, even if MPRINT and MLOGIC are enabled!

Here is an example that you can enter into a SAS session to take for a test drive:

libname mylib "c:\temp";

options mstored sasmstore=mylib;

%macro secretmc/store secure; /* This macro is encrypted */
    data _null_;
      x=1;
      put "This data step was generated from a secure macro.";
    run;
  %mend secretmc;

options mprint mlogic symbolgen;

  %secretmc

In the example, we first allocate c:\temp as a SAS data library, and then tell SAS to store any compiled macros in a catalog in that library.  Next, we create a compiled macro named SECRETMC.  The STORE option specifies that we are to compile the macro and save it.  SAS refers to the MSTORED option and stores the macro in a catalog at the location that it specifies.  The SECURE option directs SAS to encrypt the macro as it compiles it.

Next, we try to be clever and specify several options that would normally reveal SAS macros that are executed.  Then, we invoke the SECRETMC macro to see the source code.  What we see in the log is:

1    libname mylib "c:\temp";
NOTE: Libref MYLIB was successfully assigned as follows:
      Engine:        V9
      Physical Name: c:\temp
2
3    options mstored sasmstore=mylib;
4
5    %macro secretmc/store secure; /* This macro is encrypted */
6        data _null_;
7          x=1;
8          put "This data step was generated from a secure macro.";
9        run;
10     %mend secretmc;
11
12   options mprint mlogic symbolgen;
13
14     %secretmc

This data step was generated from a secure macro.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

Note that the SECRETMC macro was executed and we got the message it generates, but we were unable to surface the source code.

Being clever SAS programmers, we think of a more nefarious way to surface the macro code and run the following simple DATA step:

  filename maccat catalog 'mylib.sasmacr.secretmc.macro';

  data _null_;
    infile maccat;
    input;
    list;
  run;

This program is designed to read each line of the macro stored in the SAS macro catalog and write it to the SAS log.  However, because the macro was encrypted, all we get is this:

16     filename maccat catalog 'mylib.sasmacr.secretmc.macro';
17
18     data _null_;
19       infile maccat;
20       input;
21       list;
22     run;

NOTE: The infile MACCAT is:
      Catalog Name=MYLIB.SASMACR.SECRETMC.MACRO,
      Catalog Page Size=4096,
      Number of Catalog Pages=8,
      Created=Saturday, May 14, 2015 12:51:00 PM,
      Last Modified=Monday, June 23, 2015 06:16:19 PM,
      Filename=c:\temp\sasmacr.sas7bcat,
      Release Created=9.0202M3,
      Host Created=W32_VSPRO

RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+-

1   CHAR  ......SECRETMC..........................°I..9.2     ....................°... ...u.....
    ZONE  0000005445454400000000000000000000000000B4003232222200000000000000000000B0002000700000
    NUMR  100080353254D30000000000000000000000004009009E2000000000000000000000100000000000500030

      87  ..¨.......>................... 116
    ZONE  00A000100030000000000000000000
    NUMR  008000C000E0000000000000000000

2   CHAR  †xf2<  .T9OT:á€{..…¯&(¾[+NìA.ÞÕ}0±.[#×.ä=5½"X[£%.e.¹%·.¶!¡(œ.ýð.'x.é.†‘u.àŸ..ÏŽ8..rBU½
    ZONE  87633AA053453E87108A22B524E40DD73B152D1E33B255A2161B2B1B2A290FF1270E08970E911C8301745B
    NUMR  6862C00149F4A10BD65F68EBBEC1BE5D01AB37E4D5D28B3535F957E6118C7D0678C9861580FEBFE8BC225D

      87  ¹æTgŸ)=xvØO.Wá#.B..÷ÀÛB.9OC™±.5më.YÈHMS¦Gë.×f.Jža‰WøG§.¢)w.iIºS,ݬ.gÿV
    ZONE  BE5692377D425E21401FCD403449B136E05C445A4E0D6149685F4A1A27164B52DA16F5
    NUMR  9647F9D868FE71302AE70B209F391A5DB0988D367BF761AE1978772297899A3CDC77F6

3   CHAR  +r.#6O.¦!MÂR 12
    ZONE  2702340A24C5
    NUMR  B2136F661D22

4   CHAR  qÚ+.=¤.Æ.^šj.«Î–9ÌŠ.8ýXÅ%éT¬ 28
    ZONE  7D203A2C15960AC93C813F5C2E5A
    NUMR  1AB3D4E63EAA3BE69CAE8D85594C
NOTE: 4 records were read from the infile MACCAT.
      The minimum record length was 12.
      The maximum record length was 156.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds

…which means that the SECRETMC SAS macro is secure and all attempts to reveal the source code have been thwarted!

There is obviously a lot more about compiling, storing, and securing SAS macros than can be covered in this text.  Access the SAS online documentation at support.sas.com for further reading.


Best of luck in all your SAS endeavors!

---MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)
Author of the new cult classic for computer programmers:  It Only Hurts When I Hit <ENTER>
Print edition:  http://tinyurl.com/z8bzx2e 
Kindle edition: http://tinyurl.com/zypgqa7 

The hack above is an excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

Monday, May 8, 2017

Hack 4.21 Using the WAKEUP Function to Put SAS Programs to Sleep


SAS Programming Professionals,

Did you know that you can use the WAKEUP function to put a SAS program to sleep and specify when it is to resume executing?

The WAKEUP function directs SAS to put your program to sleep for the duration that you specify within the function.  When that duration has passed, your SAS program wakes up and continues to execute, starting with the statements found directly after the statement containing the WAKEUP function. 

You can specify a date-time value, a time (in hours), or the number of seconds before/after midnight for SAS to wait until waking up and running the rest of your program.  Here is an example of each:

data _null_;
dozeuntil = wakeup("04JUL2017:12:00:00"dt);
put "The 4th of July picnic is just starting.";
run;


data _null_;
dozeuntil = wakeup("22:30:00"t);
put "Time for the Jimmy Kimmel Live show!";
run;


data _null_;
dozeuntil = wakeup(-7200);
put "It is 2-hours until midnight!";
run;


data _null_;
dozeuntil = wakeup(7200);
put "It is 2-hours past midnight!";
run;


The first DATA _NULL_ step will continue executing on July 4, 2017 at 12-noon.  The second will execute every night at 11:30pm.  The third DATA _NULL_ step will execute every evening at 10:00pm.  The last one will execute every morning at 2:00am.

There are more nuances to the WAKEUP function than can be covered in this publication; including limits on the durations that you can specify.  Check out the online documentation on support.sas.com to get more information on the WAKEUP function.

Best of luck in all your SAS endeavors!

---MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)
Author of the new cult classic for computer programmers:  It Only Hurts When I Hit <ENTER>
Print edition:  http://tinyurl.com/z8bzx2e 
Kindle edition: http://tinyurl.com/zypgqa7 

The hack above is an excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

Monday, May 1, 2017

Hack 4.20 Using File Functions to Get the Attributes of a File


SAS Programming Professionals,

Did you know that you can use a set of SAS file functions to get the attributes of a file?

The FILENAME, FOPEN, FOPTNUM, FOPTNAME, FINFO, and FCLOSE functions can be used in concert to read file attributes and store them in a SAS data set.  Here is an example:

data fileInfo(drop = rc fileid infonum i close);

length FileChar FileCharValue $60;

label FileChar = "File Characteristic"
      FileCharValue = "Value";

rc=filename('fileref','c:\temp\mike.xlsx');

fileid=fopen('fileref');

infonum=foptnum(fileid);

do i=1 to infonum;
   FileChar=foptname(fileid,i);
   FileCharValue=finfo(fileid,FileChar);
   output;
end;

close=fclose(fileid);

run;

proc print noobs label data=FileInfo;
title1 "File Characteristics";
run;

We start the DATA step by specifying that the intermediate variables we use to get the file attributes are to be dropped.  Next, we set the length of the FileChar variable which holds the file characteristic and the FileCharValue variable which holds the value of a particular file characteristic.  We provide nice labels for both variables.

The FILENAME function creates a fileref (conveniently named FILEREF) for the particular file we want to examine.  The FOPEN function in the next line tells SAS to open the file associated with FILEREF.  SAS does so.

Next, we use the FOPTNUM option to determine how many variable characteristics SAS can find for this data set.  That number is stored in variable INFONUM, which will be used in the following DO loop.

The DO loop iterates from 1 to INFONUM, which is the total number of variable characteristics for the given data set.  The FOPTNAME option returns the name of a file characteristic and stores it in variable FileChar.  The FINFO function returns the value of that file characteristic and stores it in variable FileCharValue.  Once we have these two pieces of information (FileChar and FileCharValue), we OUTPUT them to the FileInfo SAS data set.

We end the data step by closing the file under investigation with the FCLOSE function.  And we display our good work with a simple PROC PRINT.  Here is what the report looks like:



File Characteristics

File Characteristic
Value
Filename
c:\temp\mike.xlsx
RECFM
V
LRECL
256
File Size (bytes)
8771
Last Modified
20Jul2014:17:12:45
Create Time
14May2011:14:52:59

Note that there were six file characteristics available to SAS; each of which can be found in our spiffy report.  This type of information can be invaluable when you need to write programs that read through directories looking for such things as particular data set types (e.g. XLSX), file sizes, and the dates files were last modified.

Best of luck in all your SAS endeavors!

---MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)
Author of the new cult classic for computer programmers:  It Only Hurts When I Hit <ENTER>
Print edition:  http://tinyurl.com/z8bzx2e 
Kindle edition: http://tinyurl.com/zypgqa7 

The hack above is an excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers

Monday, April 24, 2017

The Little SAS Program's BIG Night Out















The little SAS program’s official name was Extract_Transform_Load_ 0314.sas.  But, that name was much too formal, way too long, and did not roll off of the tongue very easily at all.  So, everybody simply called her:  ETL Pi.

ETL Pi was conceived in a 2-hour project strategy meeting in conference room 427C on a Monday afternoon in early May.  She was born three days later in Susan G.’s cubicle in the west wing of the sixth floor.  Susan was a mid-level programmer/analyst with seven years of SAS programming experience.  She first crafted ETL Pi to perform only the basic functions of extracting data from the Oracle database and loading it into an Excel spreadsheet for use by the analysts.  Susan wanted to make sure that she had the basic data transportation mechanisms—the Extract and Load parts of the ETL process—correctly programmed before moving on.  So, ETL Pi began her existence as a compact 18-line SAS program that performed the bare minimum functions on test data.

After Susan was confident that she had the Extract and Load mechanisms correct, she added the lines of code that did the Transform part of the ETL process by creating constructed variables from those extracted from the Oracle database.  She sent the resulting Excel files to the analysts for review and approval.  The analysts had a lot of comments, ideas, proposals, recommendations, suggestions, and counter-suggestions; so Susan modified, changed, revised, amended, adjusted, reformed and altered ETL Pi accordingly until the analysts were finally satisfied.  This resulted in ETL Pi growing to modest 57 lines of lean, efficient SAS code.

Susan productionized ETL Pi by first creating a directory for the Excel file that would be created each night and granting permissions to that directory to the analysts.  Then, she modified ETL Pi so that it would be run on the UNIX server where the production Oracle database was housed.  Susan did this by adding five SAS/Connect statements to ETL Pi specifying how to connect to the UNIX server and specifying which portions of the SAS code would be sent to execute on the server. The additions of the OPTIONS, SIGNON, RSUBMIT, ENDRSUBMIT, and SIGNOFF statements brought ETL Pi to 62 lines of SAS code.

Finally, Susan scheduled ETL Pi to run at 10:00pm every night from her PC workstation.  To do this, she first created a .bat file that held the command to execute SAS and which also pointed at the full path to the ETL Pi SAS program.  Next, she invoked the Windows Task Scheduler and went through the several manual point-and-click steps to schedule ETL Pi to run at 10:00pm every night.  Satisfied with her preparations, Susan went home that evening anticipating a good first run and a clean SAS log.

ETL Pi was nervous as the clock slowly ticked away towards 10:00pm.  She was used to working with small amounts of data and jostling for network bandwidth during the day.  But, she didn’t know quite what to expect when running on the UNIX server where the big, important corporate programs ran.  Tonight promised to be quite an adventure.

At 10:00pm sharp, the Windows Task Scheduler executed the .bat file.  That invoked SAS in batch mode and started-up ETL Pi.  Once initiated, ETL Pi executed an OPTIONS statement that contained two SAS/Connect-oriented options.  COMMAMID=TCP specified for SAS to use the TCP communications access method, and REMOTE=UNIX427 specified that ETL Pi would be working on the UNIX427 server.  Next, ETL Pi executed a SIGNON statement that pointed to a SAS/Connect script that actually initiated the connection between Susan’s PC and the UNIX server.  The script executed and instantiated a SAS session on the UNIX server that ETL Pi could directly communicate with.

The next block of ETL Pi’s SAS code was sandwiched between RSUBMIT and ENDRSUBMIT SAS/Connect statements.  So, that block of code would be submitted directly to the UNIX server and be processed there.

Oh, goodness; here I go! ETL Pi thought.  Then,

            Wheeeeeeeeeee!

...she exclaimed as she was whizzed at dizzying speeds across the network, through half a mile of fiber-optic cable, and into the SAS session executing on the UNIX427 server sitting in the data center.

ETL Pi emerged into a very crowded UNIX server.  There were system backups, database updates, database backups, five java programs, and dozens of other SAS programs all jostling for computer resources.  Not intimidated, ETL Pi bravely elbowed her way into the mix of programs, trying to get as much memory, disk space, and processor cores as she possibly could.  I have a job to do, she thought.  And I am going to do my best to get it done.

ETL Pi executed a LIBNAME statement that had the proper connection string information to access a production Oracle database using SAS/Access Interface to Oracle.  Next she executed the SQLGENERATION=DBMS option in an OPTIONS statement.  That option allows SAS to use in-database procedures which executes select SAS procedures within the Oracle database itself in order to take advantage of the database’s native processing speed and facilities.

Next, ETL Pi submitted a PROC SUMMARY to summarize specific data in one of the large tables in the Oracle database.  Because of the SQLGENERATION option, that summary was efficiently done within the Oracle database and the small result set was returned to the SAS work library within ETL Pi’s UNIX SAS session.

That was all that ETL Pi needed to do on the UNIX server.  She finished up by using PROC DOWNLOAD to download the summarized SAS data set in the work library on UNIX to the SAS work library on the PC.

Okay, I’m going home now, she thought as the ENDRSUBMIT statement executed.

                     Wheeeeeeeeeee!

...she shouted in exhilaration as control was passed back to the SAS session running back on the PC.

Once she was back home, ETL Pi executed two ODS statements, a DATA step, and the Report procedure that created the Excel spreadsheet for Susan’s analyst clients.  Then, with a heavy sigh, she executed the SIGNOFF statement which terminated the UNIX SAS session and closed the connection with the server.

Just before ending, ETL Pi took a quick look at the SAS log.  Not a WARNING or ERROR anywhere in the log, she beamed proudly.  Susan is going to be very happy when she reviews it in the morning.  Now that was fun, and I can’t wait to do it again tomorrow night!

And with that, the little SAS program's big night out came to an end.


---MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)

Monday, April 17, 2017

Hack 4.19 Up-Coding Text Variables with the VVALUE Function


SAS Programming Professionals,

Did you know that you can use a SAS format to up-code text variables based on a numeric response variable?

The VVALUE function returns a character value that is the results of bouncing a numeric variable’s value against a format.  The format for the VVALUE function is:

VVALUE (variable)

Let’s look at an example:

/* Create potato chip format */
proc format;
      value chiptype

      1 = "Ruffles"
      2 = "Lay's"
      3 = "Kettle"
      4 = "Miss Vickies"
      5 = "Pringles"
      other = "Don't Know"
      ;
run;


/* Create example data set */
data class;
set  sashelp.class;

Chip_Response = mod(age, 10);

format Chip_Response chiptype.;

run;


/* Upcode value of Chip_Response_Text */
data class_Response;
set  class;

length Chip_Response_Text $12.;

Chip_Response_Text = vvalue(Chip_Response);

run;

proc print noobs data=class_Response;
title1 "Results of the Potato Chip Question";
var Name Age Chip_Response Chip_Response_Text;
format Chip_Response 8.;
run;

First we create the CHIPTYPE format to associate numeric responses with the related potato chip type as per the questionnaire.  The first DATA step creates the Chip_Response variable that represents the various subject’s response to the potato chip question in the survey.

The second DATA step uses the VVALUE function to assign the correct response text value to Chip_Response_Text, based on the value found in variable Chip_Response.  Since the argument to VVALUE is Chip_Response, the function looks to the CHIPTYPE format for the values that should be returned.  It does so because CHIPTYPE is the format associated with variable Chip_Response.  Chip_Response values of 1 result in Chip_Response_Text values of “Ruffles”, values of 2 result in “Lay’s”, and so forth; as per the CHIPTYPE format.

The output of the PROC PRINT looks like this:

Results of the Potato Chip Question

       Chip_
   Chip_      Response_
Name       Age    Response    Text
Alfred      14           4    Miss Vickies
Alice       13           3    Kettle
Barbara     13           3    Kettle
Carol       14           4    Miss Vickies
Henry       14           4    Miss Vickies
James       12           2    Lay's
Jane        12           2    Lay's
Janet       15           5    Pringles
Jeffrey     13           3    Kettle
John        12           2    Lay's
Joyce       11           1    Ruffles
Judy        14           4    Miss Vickies
Louise      12           2    Lay's
Mary        15           5    Pringles
Philip      16           6    Don't Know
Robert      12           2    Lay's
Ronald      15           5    Pringles
Thomas      11           1    Ruffles
William     15           5    Pringles

Note that if you do not specify a length for a variable created with the VVALUE function, the variable length will default to 200 characters.  Ouch!

Best of luck in all your SAS endeavors!

---MMMMIIIIKKKKEEEE
(aka Michael A. Raithel)
Author of the new cult classic for computer programmers:  It Only Hurts When I Hit <ENTER>
Print edition:  http://tinyurl.com/z8bzx2e 
Kindle edition: http://tinyurl.com/zypgqa7 

The hack above is an excerpt from the book:  Did You Know That?  Essential Hacks for Clever SAS Programmers