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

http://tinyurl.com/zfd67rh

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

http://tinyurl.com/zfd67rh

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!