Monday, June 12, 2017

Hack 5.3 Saving the Source Code of Compiled Macros




SAS Programming Professionals,

Did you know that you can save the source code for compiled macros in the same macro catalog entry as the compiled code for later use?

The SOURCE option of the %MACRO statement directs SAS to save the macro source statements along with the compiled macro code in the macro catalog entry.  Thereafter, you can use the %COPY macro statement to print the macro’s source code to the SAS log, or have it written to a file.  Here is an example:

libname maclib "c:\temp";

options mstored sasmstore=maclib;

%MACRO BIGMAC / store source;

proc print data=sashelp.class;
    var name age;
run;

%MEND BIGMAC;


%COPY BIGMAC / source out='c:\temp\bigmac.sas';

We begin this example by allocating a directory to house the macro catalog via a LIBNAME statement.  The two options specified in the OPTIONS statement declare that all stored macros are to be stored in the macro catalog (which SAS will name SASMACR)  in the previously allocated directory.

Next, we create the %BIGMAC macro.  The STORE option directs SAS to store the compiled macro in a catalog entry.  The SOURCE option tells SAS to store the source code for the macro—everything between %MACRO and the final semicolon on the %MEND statement—in the same catalog entry.

We suddenly realize that we need to create a separate SAS program from the BIGMAC macro catalog entry.  So, we use the %COPY macro statement and the SOURCE option, and create the BIGMAC.SAS program file.  If we had not specified the OUT= option, SAS would have simply written the macro source code to the log.

The ability to store macro SOURCE code in the macro catalog entry was introduced in version 9 of SAS.  It obviates the old tried-and-true method of storing the source code of compiled macros in separate directories.  Though many of us have done that for years and have it down-pat, there is always the possibility that the macro catalog and the source code directory could somehow lose sight of one another.  Having the source code living in the same Dwelling Unit as the compiled macro provides an extra layer of integrity to the clever SAS macros that you write!

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, June 5, 2017

Hack 5.2 Identifying Variables That Do Not Have Labels



SAS Programming Professionals,

Did you know that you can easily create a report showing which variables in a SAS data set are missing labels?

You can do this by accessing the COLUMNS table in the SAS Dictionary tables.  Here is an example:

libname ProdData "G:\Data Delivery\ImportantData";

%macro varlmss(LIBname,DSName);

title "Variables with Missing Labels in Data Set &DSName.";

proc sql;
      select name label="Variable Name"
            from dictionary.columns
                  where libname = "&LIBNAME"
                  and memname = upcase("&DSName")
                  and memtype = "DATA"
                  and label=" ";
quit;

%mend;

%VARLMSS(ProdData, Delivery01);

The example begins with a LIBNAME statement which identifies the SAS data library of our target data set:  Delivery01.  That is the data set we want to check for missing variable labels.

Next, we have the VARLMSS macro which accepts two parameters:

  •        LIBname – the libname of the SAS data library that holds the target data set
  •        DSName – the name of the target data set


The macro uses PROC SQL to access the COLUMNS dictionary table which holds information about every variable in every SAS data set allocated to our program.  We subset the COLUMNS table by reading only rows that match the LIBNAME, MEMNAME (target data set name), and have missing values for LABEL.  (Don’t worry about memtype = "DATA"; that is just to ensure that we only access information about SAS data sets; not views or other objects).  The result is a nice report of all of the variables in Delivery01 that do not have labels.

This is a great QC check to use for SAS data sets you intend to deliver to a client!

Best of luck in all your SAS endeavors!

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)