“Automagic” SAS Tricks
Bill McKirgan
Database Admin/Develop II
University of Iowa
Department of Psychiatry
[email protected]
“Automagic” SAS Tricks
Before we start…
Beginning SAS programmers
should practice the basics
before trying to use this stuff.
One should have a grasp of
SAS Base before learning
how to automate processes or
generate code.
“Automagic” SAS Tricks
Acknowlegement…
There are many ways of doing the same thing in
SAS and other languages. ’Automagic’ SAS
Tricks is just one toolkit for generating code.
In October 2006, Michael Brainard presented,
How to use SAS to generate code at the
IASUG meeting. His presentation provides
valuable examples of how to use PROC SQL to
generate SAS and other code.
http://www.iowasasuser.org/CodeGenerationISUG200610.ppt
(URL for Michael’s ISUG presentation)
“Automagic” SAS Tricks
Stuff we will discuss today
What is “automagic”?
Basic tools
– Clued-in to %INCLUDE
– Using ‘autoexe.sas’: what, how & why
– The power of %LET
– Fun with %SYSFUNC
• Generating lists of files to import
• Using file lists to automate import
• Questions
“Automagic” SAS Tricks
What is “automagic”?
• In simple terms, “Automagic” is a
chopped combination of the words
‘automatic’ and ‘magic’.
• Automating a process is generally
good, and for folks who do not
understand or need to know the
details the end result is magical.
“Automagic” SAS Tricks
What is “automagic”?
• Many programs are ‘static’ often only
good for a single run.
• The next time the program is needed
certain details typically need to be
changed:
– Report dates
– File names
– Titles, subtitles, and footnotes
“Automagic” SAS Tricks
What is “automagic”?
• SAS programmers have a vast set of
tools for automating processes and
making programs more dynamic:
– %LET, system functions, and SAS macro
(stuff we will do today)
– SAS SCL and PROC SQL are other options,
but are not used in this presentation
“Automagic” SAS Tricks
Basic tools
Clued-in to %INCLUDE
• As we expand our repertoire of SAS tricks we usually
get to a point where we discover repetition and how to
‘copy/paste’ certain standard program elements.
• As a beginning SAS programmer I wrote each program
as a ‘one-off’ and would typically repeat libname
statements, formats, and code for deriving and labeling
variables in each new program.
• Then I got “clued-in” to a way of storing these handy
chunks of programs in separate files and calling upon
them using %include.
syntax: %INCLUDE “c:\temp\program_chunk.sas”;
“Automagic” SAS Tricks
Basic tools
Clued-in to %INCLUDE
• One of the first steps in using %include to automate
SAS programs is to write the programs to be included in
such a way that they can be executed independently.
– Examples:
%include ‘c:\temp\my_formats.sas’;
should probably have no datastep code, titles, or other extraneous
stuff.
%include “s:\project_a\standard_libs_fmts.sas”;
again, should have nothing extraneous.
Craft the %include-able programs so they are generalized and
can be called upon as needed in other programs.
“Automagic” SAS Tricks
Basic tools
Clued-in to %INCLUDE
NOTE: Not all programs need to be written this
way, but the stuff you want to make generally
useful from other SAS programs should be
written with this in mind:
– Take care not to over-write or “clobber”
•
•
•
•
Libnames
Macro variable names
Dataset names
Other stuff???
“Automagic” SAS Tricks
Basic tools
Using ‘autoexe.sas’: what, how & why
• First of all, WHAT is ‘autoexec.sas’?
– It’s a special SAS program file name for the SAS
user
– The SAS user/administrator must create it
– The SAS user can program it to do things at startup
• HOW do I use it?
– it will be executed every time SAS is started as long
as it is placed in the proper directory:
Example –
C:\Program Files\SAS\SAS 9.1\autoexec.sas
“Automagic” SAS Tricks
Basic tools
Using ‘autoexe.sas’: what, how & why
• WHY would I need one?
– If you have many custom SAS libraries
• You can define them in your autoexec.sas instead of using
the library/libname icon in the SAS graphic user interface
– If you have datasets you want refreshed from other
database applications or formats
• SAS import and translation programs can be executed via.
%include statement in your autoexec.sas
– If you want to pre-load custom formats, or macro
variables
• Again, the autoexec.sas can store these statements or run
them from other batch-capible programs via. %include
“Automagic” SAS Tricks
Basic tools
The power of %LET
• %LET is the most basic of all SAS macro
constructs. It is used to define a macro variable
name, and to store a string of characters in the
macro variable defined.
%let whatpath=c:\longpath\that\i\dont\want\to\type\again;
• Here, above, we define WHATPATH and load it with a
long directory path name that can be “called” later in the
program by using the ampersand character:
infile “&WHATPATH\mytextfile.txt”;
“Automagic” SAS Tricks
Basic tools
The power of %LET
• Storing a string of characters
for later use is a powerful
thing especially if it is a long
string and used frequently
later in the program.
“Automagic” SAS Tricks
Basic tools
The power of %LET
My Criteria for %LETting
something
1. The string is so long retyping
could result in errors
2. The string is repeated more than
once or twice
3. A combination of 1 and 2
“Automagic” SAS Tricks
Basic tools
Fun with %SYSFUNC
• The macro function %SYSFUNC
gives us access to the macro
processor and many datastep and
SCL functions.
• %SYSFUNC has been around since
SAS version 6.12.
“Automagic” SAS Tricks
Basic tools
Fun with %SYSFUNC
• Common uses:
– changing the format of macro
variables
(covered in this presentation)
– reading SAS datasets, catalogs, and
external files
•
(www.cyassociates.com/sysfunc.html is a good source for
%SYSFUNC examples).
“Automagic” SAS Tricks
Basic tools
Fun with %SYSFUNC
Changing macro variable formats
• I used to archive dataset and report files by hand
– that is I would: copy/paste and then rename files to include a
“_YYYYMMDD” datestamp at the end
• After discovering macro I tried to automate this by
concatenating file names using “today()”, but the
SASDATE number was confusing.
• I wished I’d discovered %SYSFUNC sooner, because it
does this and many other tasks to save the programmer
from typing
“Automagic” SAS Tricks
Basic tools
Fun with %SYSFUNC
In this example we use %LET to create the macro
variable DATESTAMP which is the YYYYMMDDformatted version of the current SAS system date.
%let datestamp = %sysfunc(today(),yymmddN8.);
%put &datestamp;
-- log output -SYMBOLGEN: Macro variable DATESTAMP resolves to 20061211
20061211
“Automagic” SAS Tricks
Basic tools
Fun with %SYSFUNC
Over weeks and months
of use, the automated
DATESTAMP helps keep
similar datasets neatly
grouped in the directory
where it is stored.
“Automagic” SAS Tricks
Generating lists of files
SAS has several great ways of helping us avoid typing and
looking up stuff we may need for our programs:
• X is a command that passes text to the operating
system
– Example: X dir c:\temp
• Will cause a “DOS” window to open and display the screen output
– Example: X dir c:\temp > c:\temp\tempfiles.txt
• Will cause a “DOS” window to open and direct the output to the
file, ‘tempfiles.txt’
– In each case the DOS window will remain open unless you
preceed the X command with the option:
Options noxwait;
“Automagic” SAS Tricks
Generating lists of files
The following options are helpful:
• symbolgen – causes the resolved macro variable to display in the log
• noxwait – allows SAS to execute while a command window is open;
otherwise, it will pause until the user closes the window.
options nocenter symbolgen noxwait ;
%let drv=c;
/* Name the directory FILE SEARCH will branch-off from, below.
%let progpath=&drv:\temp\IASUG Presentations\Automagic SAS Tricks;
%let whatpath=&progpath\de_identified_data;
%let therepth=&progpath\final_data;
run;
“Automagic” SAS Tricks
*/
Generating lists of files
/* Make list of all files found under WHATPATH */
filename filelist pipe "dir ""&whatpath""\*IGT_* /S ";
run;
This piped command selects only the files that
have names with ‘IGT_’ or ‘igt_’ in them.
It took me much trial & error work to get
&WHATPATH to resolve correctly.
“Automagic” SAS Tricks
Generating lists of files
Here’s a look at some of the output that will become
part of our list:
Volume in drive C has no label.
Volume Serial Number is 9052-5591
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data
12/12/2006
11:03 AM
1 File(s)
1,225 igt_list1.sas
1,225 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_112233_ew
12/21/2004
12:39 PM
1 File(s)
6,291 igt_112233.txt
6,291 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_1122_by
01/30/2006
01:09 PM
1 File(s)
6,291 igt_1122.txt
6,291 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_223344_cjp
09/28/2006
04:47 PM
1 File(s)
“Automagic” SAS Tricks
6,291 IGT_223344.txt
6,291 bytes
Generating lists of files
We will read the directory info from some lines
Volume in drive C has no label.
Volume Serial Number is 9052-5591
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data
12/12/2006
11:03 AM
1 File(s)
1,225 igt_list1.sas
1,225 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_112233_ew
12/21/2004
12:39 PM
1 File(s)
6,291 igt_112233.txt
6,291 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_1122_by
01/30/2006
01:09 PM
1 File(s)
6,291 igt_1122.txt
6,291 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_223344_cjp
09/28/2006
04:47 PM
1 File(s)
“Automagic” SAS Tricks
6,291 IGT_223344.txt
6,291 bytes
Generating lists of files
…and the file information from other lines
Volume in drive C has no label.
Volume Serial Number is 9052-5591
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data
12/12/2006
11:03 AM
1 File(s)
1,225 igt_list1.sas
1,225 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_112233_ew
12/21/2004
12:39 PM
1 File(s)
6,291 igt_112233.txt
6,291 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_1122_by
01/30/2006
01:09 PM
1 File(s)
6,291 igt_1122.txt
6,291 bytes
Directory of C:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_223344_cjp
09/28/2006
04:47 PM
1 File(s)
“Automagic” SAS Tricks
6,291 IGT_223344.txt
6,291 bytes
Generating lists of files
data fileprep;
retain IND_ID ;
infile filelist truncover ;
input
filedate :mmddyy10.
timestr
sizestr
filename $ 40-90
The following datastep reads the
output in a sloppy way:
$ 22-38
$ 13-21
lines
$
1-100;
format filedate mmddyy10.;
filetime = input(compress(timestr," "),??time9.);
format filetime timeampm8.;
filesize = input(sizestr,??comma8.);
First by trying to read each line as if
it was a line with file details.
if index(lines,"Directory of ")=1 then do;
DIRECTORY = substr(lines,14,200);
IND_ID=INPUT(SCAN(DIRECTORY,4,'_'),BEST8.);
retain DIRECTORY IND_ID ;
end;
And later, it reads directory details
only from the lines that contain the
string, “Directory of “.
if filedate ^= .; /* Discard output not related to files */
LABEL
FILEDATE = "Date file was created"
FILETIME = "Time file was created"
FILESIZE = "Size file"
FILENAME = "Name of file"
DIRECTORY= "Name of source directory"
drop timestr sizestr LINES; /* Drop junk strings */
“Automagic” SAS Tricks
IF INDEX(UPCASE(FILENAME),".TXT");
RUN;
;
Generating lists of files
A closer look
data fileprep;
retain IND_ID ;
infile filelist truncover ;
input
filedate :mmddyy10.
timestr
sizestr
filename $ 40-90
$ 22-38
$ 13-21
lines
$
format filedate mmddyy10.;
filetime = input(compress(timestr," "),??time9.);
format filetime timeampm8.;
filesize = input(sizestr,??comma8.);
if index(lines,"Directory of ")=1 then do;
DIRECTORY = substr(lines,14,200);
IND_ID=INPUT(SCAN(DIRECTORY,4,'_'),BEST8.);
retain DIRECTORY IND_ID ;
end;
“Automagic” SAS Tricks
1-100;
Generating lists of files
A closer look
data fileprep;
retain IND_ID ;
infile filelist truncover ;
input
filedate :mmddyy10.
timestr
sizestr
filename $ 40-90
$ 22-38
$ 13-21
lines
$
1-100;
example of Log NOTEs
resulting
from input
of lines without file info
format
filedate
mmddyy10.;
= input(compress(timestr,"
"),??time9.);
NOTE: Invalidfiletime
data for filedate
in line 1 2-7.
RULE:
----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8
format
timeampm8.;
1
Volume in drive
C hasfiletime
no label. 32
IND_ID=. filedate=. timestr=rive C ha sizestr=s no label. filename=
= input(sizestr,??comma8.);
lines=Volume filesize
in drive C has
no label. filetime=. filesize=. DIRECTORY= _ERROR_=1 _N_=1
NOTE: Invalid data for filedate in line 2 2-7.
if index(lines,"Directory of ")=1 then do;
.
.
DIRECTORY = substr(lines,14,200);
.
ERROR: Limit set by ERRORS= option reached. Further errors of this type will not be
IND_ID=INPUT(SCAN(DIRECTORY,4,'_'),BEST8.);
printed
retain DIRECTORY IND_ID ;
end;
“Automagic” SAS Tricks
Generating lists of files
A closer look
data fileprep;
retain IND_ID ;
infile filelist truncover ;
input
filedate :mmddyy10.
timestr
sizestr
filename $ 40-90
$ 22-38
$ 13-21
lines
$
format filedate mmddyy10.;
filetime = input(compress(timestr," "),??time9.);
format filetime timeampm8.;
filesize = input(sizestr,??comma8.);
if index(lines,"Directory of ")=1 then do;
DIRECTORY = substr(lines,14,200);
IND_ID=INPUT(SCAN(DIRECTORY,4,'_'),BEST8.);
retain DIRECTORY IND_ID ;
end;
“Automagic” SAS Tricks
1-100;
Generating lists of files
/* fileprep datastep continued… */
if filedate ^= .; /* Discard output not related to files */
LABEL
FILEDATE = "Date file was created"
FILETIME = "Time file was created"
FILESIZE = "Size file"
FILENAME = "Name of file"
DIRECTORY= "Name of source directory"
drop timestr sizestr LINES; /* Drop junk strings */
IF INDEX(UPCASE(FILENAME),".TXT");
RUN;
“Automagic” SAS Tricks
;
Generating lists of files
And here is our viewtable of the file list
Now we have what is needed to fish-out files to import
from many different directories.
“Automagic” SAS Tricks
Using file lists to automate
import
• Importing files can be easy when they are all in
one directory, but this is not always the case
• Automating a routine import process is helpful
– When files span different subdirectories
– When files have different names
– When filenames contain information needed in the
data
• We will examine one method for doing this
“Automagic” SAS Tricks
Using file lists to automate
import
• Macro is handy for doing the “list
processing” we need for import
• The classic macro loop defines the
macro name and parameters that
relate to a chunk of SAS code. The
parameters are the elements that
change with each macro call.
“Automagic” SAS Tricks
Using file lists to automate
import
• This is the classic macro loop:
Macro definition (parameters)
… template of SAS statements
Macro end
Call macro 1
Call macro 2
“Automagic” SAS Tricks
Using file lists to automate
import
• This is the classic macro loop:
%macro runem(a, b, c);
data this; set &a;
id=&b;
type=&c;
%mend runem;
%runem(fred, 99, 1);
%runem(barney, 22, 7);
“Automagic” SAS Tricks
Using file lists to automate
import
First generate the macro call statements and write them to a
file called “igtlist1.sas” for later %INCLUDE use.
options ls=200;
data makecode; set fileprep;
lines='%runem( '||trim(left(ind_id))||' , '||trim(left(filedate ))||' , '||trim(filename)
||" , "||trim(directory)||' );'
;
file "&whatpath\igtlist1.sas";
put lines;
run;
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
1122 ,
112233
223344
334455
445566
556677
667788
778899
889900
990011
16831 ,
, 16426
, 17072
, 16531
, 16456
, 17013
, 16476
, 16668
, 17002
, 16510
igt_1122.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_1122_by );
, igt_112233.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_112233_ew );
, IGT_223344.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_223344_cjp );
, igt_334455.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_334455_eew );
, igt_445566.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_445566_eew );
, igt_556677.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_556677_by );
, igt_667788.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_667788_ccp );
, IGT_778899.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_778899_cjp );
, igt_889900.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_889900_eew );
, IGT_990011.txt.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_990011_cjp );
“Automagic” SAS Tricks
Using file lists to automate
import
Next generate a block of filenames that will be used for a SET
statement to put all the data together. This list of file names is
written to ‘igtlist2.sas’ and will later be %INCLUDEd.
data makecode; set fileprep;
lines='raw.igt_'
||trim(left(ind_id))
;
file "&whatpath\igtlist2.sas";
put lines;
run;
raw.igt_1122
raw.igt_112233
raw.igt_223344
raw.igt_334455
raw.igt_445566
raw.igt_556677
raw.igt_667788
raw.igt_778899
raw.igt_889900
“Automagic” SAS Tricks
raw.igt_990011
Using file lists to automate
import
LIBNAME RAW "&whatpath\rawsas"; run;
%macro runem(whatid, whatdate, whatfile, whatpath);
define macro
DATA raw.IGT_&whatid;
IND_ID = &whatid;
infile "&whatpath\&whatfile"
firstobs=23;
template of SAS statements
IGT_DATE=&whatdate;
FORMAT IGT_DATE MMDDYY10.;
input Trial
Deck $
Borrow
Time_ms;
Win
Lose
Score
run;
%mend runem;
%include
"&whatpath\igtlist1.sas";
run; quit;
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
%runem(
1122 ,
112233
223344
334455
445566
556677
667788
778899
889900
990011
16831 ,
, 16426
, 17072
, 16531
, 16456
, 17013
, 16476
, 16668
, 17002
, 16510
Here we see how
the macro call is
executed with a
%INCLUDE
igt_1122.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_1122_by );
, igt_112233.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_112233_ew );
, IGT_223344.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_223344_cjp );
, igt_334455.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_334455_eew );
, igt_445566.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_445566_eew );
, igt_556677.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_556677_by );
, igt_667788.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_667788_ccp );
, IGT_778899.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_778899_cjp );
, igt_889900.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_889900_eew );
, IGT_990011.txt.txt , c:\temp\IASUG Presentations\Automagic SAS Tricks\de_identified_data\n4_990011_cjp );
“Automagic” SAS Tricks
Using file lists to automate
import
Gambling Task V.2.0
c2002
Now we can cycle through each file and import
the raw data with the same input statement.
By:
Antoine Bechara
Department of Neurology
University of Iowa
Based on the Gambling Task as published in:
LIBNAME RAW "&whatpath\rawsas"; run;
%macro runem(whatid, whatdate, whatfile, whatpath);
DATA raw.IGT_&whatid;
Bechara, A., Damasio, A. R., Damasio, H., &
Anderson, S. W. (1994). Insensitivity to
future consequences following damage to human
prefrontal cortex. Cognition, 50, 7-15.
IND_ID = &whatid;
Bechara, A., Tranel, D., & Damasio, H. (2000).
Characterization of the decision-making
impairment of patients with bilateral lesions
of the ventromedial prefrontal cortex.
Brain, 123, 2189-2202.
infile "&whatpath\&whatfile"
firstobs=23;
IGT_DATE=&whatdate;
FORMAT IGT_DATE MMDDYY10.;
input Trial
Borrow
Deck $
Win
Time_ms;
run;
%mend runem;
%include
"&whatpath\igtlist1.sas";
run; quit;
“Automagic” SAS Tricks
Lose
Score
Trial
1
2
3
4
5
6
7
8
9
10
11
12
Deck
A'
A'
A'
A'
A'
B'
B'
B'
B'
B'
B'
B'
Win
100
120
80
90
110
100
80
110
120
90
100
90
Lose
0
0
-150
0
-300
0
0
0
0
0
0
0
Score
2100
2220
2150
2240
2050
2150
2230
2340
2460
2550
2650
2740
Borrow
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
2000
Time(ms)
159447
1646
676
1583
831
3973
836
103
178
243
2121
1799
Using file lists to automate
import
When all raw data files
are ‘SASified’ we can use
the next ‘call-list’ file to
complete the file names
needed in the SET
statement.
This is followed by
%including the program
that summarizes the data.
“Automagic” SAS Tricks
libname there "&therepth";
run;
data there.igt_data;
set
%include "&whatpath\igtlist2.sas";
;
run;
%include
"&progpath\summarize_igt_example.sas";
run;
Using file lists to automate
import
In the end we have each
line of raw data for each
subject in one file, and a
summary record for each
subject in another file.
“Automagic” SAS Tricks
Review
I hope this spoiled some of the “magic” of
code generation / list processing using SAS
functions and macro.
There are many ways of doing the same thing
in SAS. This is just one method based on
my own experiences trying to manage data
in a dynamic environment.
Areas I intend to study and practice for similar
tricks are:
• SAS/SCL
• PROC SQL
“Automagic” SAS Tricks
Questions
and
Discussion
“Automagic” SAS Tricks
Descargar

Automagic” SAS Tricks