Date: Fri, 19 Jan 2007 21:27:49 +0000
Reply-To: iw1junk@COMCAST.NET
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ian Whitlock <iw1junk@COMCAST.NET>
Subject: Re: How can one eliminate Error statement in the following code?
Summary: A general look at generating SAS code with Art's example.
#iw-value=3
Art,
Let's look at the problem. You want to avoid writing code by having some
mechanism of generating it. This has the advantage that it is more
interesting, more efficient use of your time, and more maintainable because
it is shorter and modifications can take place in one place instead of
being repeated all over. To be specific you want obtain something like
PROC EXPORT data=WORK.file1
outfile="s:\file1"
DBMS=EXCEL2000 REPLACE;
run;
PROC EXPORT data=WORK.file2
outfile="s:\file2"
DBMS=EXCEL2000 REPLACE;
run;
PROC EXPORT data=WORK.file3
outfile="s:\file3"
DBMS=EXCEL2000 REPLACE;
run;
Writing the code for this example is not much work but in the real world
you have many more files to handle. The first thing to note is that most
of the code doesn't change. Hence it is not like trying to write a novel
with PUT statements.
In version 79.6 (around 1981) there were two choices - the old macro
language (a bear) and PUT statements to a file using the new %INCLUDE
command. Today one would write
filename code temp ; /* relatively recent keyword TEMP */
data _null_ ;
input filename $char32. ;
file code ;
put 'proc export data = work.' filename
/ ' out="s:\' filename +(-1) '"'
/ ' dbms=excel2000 replace;'
/ 'run ;'
;
cards ;
file1
file2
file3
;
%inc code ;
The key point here is that the text is data, but becomes code with the
INCLUDE. Consequently it becomes possible for SAS programs to manipulate
code at execution time as opposed to a programmer writing the code at
planning time. This means a leap forward in flexibility as well as
repeatability.
With version 82.4 (around 1983) one could package a unit of code in a macro
%macro exprt ( filename= ) ;
proc export data = work.&filename
out="s:\&filename"
dbms=excel2000 replace;
run ;
%mend exprt ;
The advantage here is that there is less extraneous code making it more
readable, but there is no mechanism provided to repeat this unit using
data. A common solution was to add more macro.
data _null_ ;
input filename $char32. ;
call symput ( "f" || put(_n_,5. -l), filename ) ;
cards ;
file1
file2
file3
;
%drvr (n = 3 ) ;
%local i ;
%do i = 1 %to &n ;
%exprt ( filename=&&f&i )
%end ;
%mend drvr ;
%drvr ()
While the older
data _null_ ;
input filename $char32. ;
file code ;
put '%exprt ( filename=' filename || ")" ;
cards ;
file1
file2
file3
;
%inc code ;
was often shorter and simpler.
Around 1985 version 5 came to the rescue with
data _null_ ;
input filename $char32. ;
call execute ( '%exprt (filename=' || filename || ')' ) ;
cards ;
file1
file2
file3
;
In those days the older PUT/%INC was more efficient. Today efficiencies
in hardware and software have largely wiped out the distinction.
Then version 6 (around 1988) stole CALL EXECUTE for a while but returned it
(6.07), and latter (6.11) added the SQL ability to move a column of data
into a macro variable. So one could have
data w ;
input filename $char32. ;
cards ;
file1
file2
file3
;
proc sql noprint ;
select '%exprt ( filename=' || filename || ")"
into: todo separated by " "
from w ;
quit ;
&todo
Comparing the last two methods, we see that the CALL EXECUTE can be done in
one DATA step from CARDS while the SQL code requires a preceding DATA step.
Sometimes the advantage of having the full abilities of the DATA step at
the point of CALL EXECUTE makes it preferable and sometimes having the
abilities of SQL make it preferable. Often it doesn't matter and many
times the source of the data is already in a SAS data set so the
distinction is largely a matter of style.
The ability of SQL to export a column of data values to a macro variable as
a list dramatically changed the nature of macro programming away from
arrays of macro variables, but CALL EXECUTE also accomplished much the same
thing. Compare
%let list = ;
proc sql noprint ;
select column into :list separated by " "
from w ;
quit ;
&list
with
%let list = ;
data _null_ ;
set w ;
call execute ( '%let list = &list ' || column || ';' ) ;
run ;
&list
In either case the ability to package the repeated unit of code as a macro
is probably a good idea in all but the most extremely simple cases and even
then maintenance considerations may suggest that the simplicity is an
illusion.
There is one important distinction in how the final code is generated. With
CALL EXECUTE the macro generates code during the execution of the DATA
step. This means that all steps of a macro are generated without execution
and then sequentially executed, while the normal invocation of a macro
causes each step to execute before the next step is generated.
Consequently code that converts data to macro information at data execution
time will not be available. For example:
%macro bad ;
data _null_ ;
call symput ( "title" , "Nifty Stuff" ) ;
run ;
title "&title" ;
%mend bad ;
will not make the correct title when invoked by CALL EXECUTE, but is fine
with normal invocation. As another example,
1 %macro NiftyStuff ;
2 %global x ;
3 %let x = nifty ;
4 %mend NiftyStuff ;
5
6 %macro bad ;
7 data _null_ ;
8 call execute ( '%NiftyStuff' ) ;
9 run ;
10 /* code using the fact that the macro NIFTYSTUFF has already
11 executed will not work correctly, e.g.
12 */
13 %put x=&x ;
14 %mend bad ;
15
16 data _null_ ;
17 call execute ( '%bad' ) ;
18 run ;
WARNING: Apparent symbolic reference X not resolved.
x=&x
Note if the code is executed a second time in the same session it will
appear to work correctly, but, of course, it doesn't work correctly the
second time either. While this looks like a serious argument for sticking
with PROC SQL or PUT statements, in practice, it often doesn't matter.
Moreover, when it does, you can use
call execute ( '%nrstr(%mac(...))' ) ;
In this case, %MAC does not execute during the execution of the housing
DATA step because the %NRSTR hides it from the macro facility at DATA step
execution time. Consequently it is just a normal invocation when the macro
facility sees the invocation of MAC as generated code after the DATA step
finishes.
In general all three basic solutions have something to contribute and some
disadvantages in readability.
All of the above involve only SAS/BASE. When non-programming staff are
involved one can argue that a gui interface provided by SAS/AF or another
language is a valuable addition to generating code based on user input.
Moreover, some languages are designed for general text manipulation and
system interaction than SAS. So there is also a valid argument for
considering still other languages that can interact with SAS. On the other
hand, the ease of generating code aspect my be more a statement about the
abilities of most SAS programmers rather the abilities of the language when
one considers the PRX functions. Moreover, there are also good arguments
for keeping it in SAS/BASE when you have SAS/BASE programmers and are not
dealing with SAS naive users.
None of the code above involving PROC EXPORT has actually been tested,
since I do not have access to SAS/ACCESS. Consequently it is probably
buggy in implementation, but not in plan.
Ian Whitlock
=================
Date: Wed, 17 Jan 2007 20:09:41 -0500
Reply-To: Arthur Tabachneck <art297@NETSCAPE.NET>
Sender: "SAS(r) Discussion"
From: Arthur Tabachneck <art297@NETSCAPE.NET>
Subject: Re: How can one eliminate Error statement in the
following code?
Comments: To: Ya Huang <ya.huang@AMYLIN.COM>
Ya,
As always, your interpretation and suggestion, was invaluable and
definitely solved the problem. My final code is shown below.
But with it, I still have to ask the other part of the question,
namely: "When would 'call execute' be a better solution and why
?"
Art
-------
Final code:
%macro buildfiles;
%do i=1 %to 3;
data file&i.;
x=&i.;
output;
run;
%end;
%mend;
%buildfiles
data have;
input filenames $;
cards;
file1
file2
file3
;
run;
proc sql noprint;
select 'PROC EXPORT data=WORK.'||trim(filenames)||
' outfile="s:\'||trim(filenames)||'" '||
'DBMS=EXCEL2000 REPLACE;run;'
into : filenames separated by ' '
from have;
quit;
&filenames.;
--------------
<snip>
