Date: Tue, 24 Feb 1998 09:46:20 -0500
Reply-To: WHITLOI1 <whitloi1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@UGA.CC.UGA.EDU>
From: WHITLOI1 <whitloi1@WESTAT.COM>
Subject: Re: SAS data views
Subject: SAS data views
Summary: An inconsistency in WHERE processing is revealed and used
to provide a stable solution to using execution time
resolution of macro variables in the WHERE statement of a
data view.
Respondent: Ian Whitlock <whitloi1@westat.com>
Grace La Torra <grace@NMSU.EDU> asked:
>I am creating a view of a data set using the following code:
>
>data stuall.persv / view = stuall.persv;
>set stuall.personal;
>by ssn semnum;
>where semnum <= &perssem;
>if last.ssn then output;
>run;
>
>I am trying to build a view that will change depending on the macro
>variable &perssem. In other words, when perssem is set to 60 the view
>will be different from when perssem is set to 64. The kicker here is
>that I don't want the macro variable to be resolved when the view is
>created, but when the view is accessed.
>
>Does anybody have any words of wisdom?
In the past I have simply answered
data view / view = view ;
set indata ;
where invar = symget ( 'macvar' ) ;
/* other processing code */
run ;
Imagine my surprise when Allan Page <pageal@TOTAL.NET> responded (in
part):
>The value in the where clause is set at the time the view is
>created.
Each test I have run in the past has shown the above to be a valid
solution. SYMGET should get the value of the macro variable at
execution time, hence it should be a perfectly good solution. Now I
find that it is an unstable solution! The question is - is symget (
'macvar' ) a constant? For me, it certainly is not, since the value of
MACVAR can in principle change at any time. However, SAS takes the
point of view that the expression 'MACVAR' is constant and that if
MACVAR already exists then SYMGET ( 'MACVAR' ) must also be constant.
In this case SAS does the evaluation during the compile of the view
step instead of during its execution.
Why did past testing not find a problem? I have always been careful
to choose a nonexistent macro variable and place the assignment of the
macro variable after the view step. In this case SAS cannot perform
the evaluation during the compile of the step; hence it is left to the
execution phase where it belongs. Now what happens if the code is
executed a second time (say interactively)? For the second execution
the macro variable now exists; hence the definition of the view
changes because the expression is now evaluated at compile time
instead of the intended execution time.
One might hope that the problem lies in the use of a view and not in the
WHERE processing itself. But the following log dashes that grasping at
straws.
1 data w ;
2 do x = 1 to 3 ;
3 output ;
4 end ;
5 run ;
NOTE: The data set WORK.W has 3 observations and 1 variables.
NOTE: The DATA statement used 0.59 seconds.
6
7 %let macvar = 1 ;
8 data _null_ ;
9 set w ;
10 where put ( x , 1. ) = symget ( 'macvar' ) ;
11 call symput ( 'macvar' , '3' ) ;
12 put x= ;
13 run ;
X=1
NOTE: The DATA statement used 0.11 seconds.
In contrast to the way SYMGET is handled, consider the statement
where ranuni ( 96943 ) < .1 ;
Here 96943 is a constant but SI was wise enough to realize that is
represents a changing seed; hence is should be (and is) evaluated at
execution time rather than compile time.
Now what about the macro variable problem? Well there is a second
chance to get it right because RESOLVE like SYMGET should obtain macro
variables at execution time. Here SI got it right as shown by
37 %let macvar = 1 ;
38 data _null_ ;
39 set w ;
40 where put ( x , 1. ) = resolve ( '&macvar' ) ;
41 call symput ( 'macvar' , '3' ) ;
42 put x= ;
43 run ;
X=1
X=3
NOTE: The DATA statement used 0.0 seconds.
Perhaps the moral is that SYMGET should be considered for historical
purposes only.
Ian Whitlock
