Monday, July 31, 2017

Hack 6.1 Adding Color to Report Titles with the ODS ESCAPECHAR Statement




SAS Programming Professionals,

Did you know that you can improve the look of your SAS-generated reports by adding a little color to the titles?

You can use the Output Delivery System (ODS) ESCAPECHAR statement and inline formatting to add and change colors within your report titles.  Here is an example:

ods escapechar = "^";

ods pdf file="example2.pdf";

title1 "^S={color=blue}Though this title started off blue ^S={color=red}it changed to red";
title2 "^S={color=green}This title was green ^S={}before changing to black";

proc print data=sashelp.class;
run;

ods _all_ close;

We start off with the ODS ESCAPECHAR statement, which defines a special character that will be used to signal to SAS that we want to perform inline formatting.  In our case, whenever SAS sees “^” in a title statement, it knows that ODS inline statements follow.  Thereafter, we put “^” to work in two TITLE statements; each time specifying the color for the following text.  If you copy the example above into a SAS Display Manager session and execute it, you will note the changes in the colors in the title lines.  Really makes you think about how you may be able to spruce-up those tired old reports, doesn’t it?

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, July 10, 2017

Hack 5.5 Using Macros to Comment Out Sections of SAS Programs



SAS Programming Professionals,

Did you know that you can use SAS macros as a handy way to comment out code in a program?

You can do this by enclosing portions in a SAS macro and not calling that macro.  Here is an example:

options msglevel=I;

%MACRO HIDEIT;

libname audit "H:\My Documents\My SAS Programs\Audit Trails\Data";

proc copy in=sashelp out=audit;
      select orsales;
run;

data audit.orsales;

seqno = _n_;

set  audit.orsales;

run;

%MEND HIDEIT;

data audit.orsales2;
set  audit.orsales;
stop;
run;

proc datasets library=audit nolist;
      audit orsales2;
      initiate;
      log admin_image=yes
      before_image=yes
      data_image=no
      error_image=yes;
run;
quit;

In this example, we wish to not execute (to hide) the LIBNAME statement, PROC COPY, and the DATA step as we test the latter sections of our program.  So, after they are first executed, we enclose them in the HIDEIT macro.  Thereafter, when we run this program, the aforementioned SAS constructs do not execute.  Once we are done testing the program, we can either comment out the %MACRO and %MEND statements or delete them entirely.  Pretty clever, eh?

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 26, 2017

Hack 5.4 Using A SAS Utility Macro that Creates CSV Files




SAS Programming Professionals,

Did you know that you can easily create a CSV file using a simple SAS utility macro?

The %DS2CSV utility macro allows you to create a CSV file from any SAS data set.  Here is an example:

%DS2CSV(data=sashelp.class,
 runmode=B,
csvfile=c:\temp\class.csv,
var=name age sex height,
where=sex="F");

…where:

·        data= specifies the SAS data set to be CSV-i-tized

·        runmode= use “B” to specify that you are running the macro in a non-SAS/IntrNet environment

·        csvfile= the full path name of the CSV file you want to create

·        var= optional parameter to use to list a subset of variables to output to the CSV file, otherwise all variables are written to the file

·        where= optional parameter to use if you want to subset the observations output to the CSV file

The code, above, produces the following SAS log entry:

1    %DS2CSV(data=sashelp.class, runmode=B, csvfile=c:\temp\class.csv, var=name age sex height, where=sex="F");
NOTE: CSV file successfully generated for SASHELP.CLASS.

…and produces this CSV file:

"Name","Age","Sex","Height"
"Alice","13","F","56.5"
"Barbara","13","F","65.3"
"Carol","14","F","62.8"
"Jane","12","F","59.8"
"Janet","15","F","62.5"
"Joyce","11","F","51.3"
"Judy","14","F","64.3"
"Louise","12","F","56.3"
"Mary","15","F","66.5"

Note that only the variables we specified were copied to the CSV file.  And, our subsetting the observations was also successful.

Of course, the CSV file looks a lot better if you open it using Excel:

Name
Age
Sex
Height
Alice
13
F
56.5
Barbara
13
F
65.3
Carol
14
F
62.8
Jane
12
F
59.8
Janet
15
F
62.5
Joyce
11
F
51.3
Judy
14
F
64.3
Louise
12
F
56.3
Mary
15
F
66.5

But, you already knew that, didn’t you?

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