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