Date:         Wed, 24 Jan 2007 21:01:28 +0000
Reply-To:     toby dunn <tobydunn@HOTMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         toby dunn <tobydunn@HOTMAIL.COM>
Subject:      Re: Macro Variable Problem (or should I say, coder problem)
Comments: To: bnz6@CDC.GOV
In-Reply-To:  <51A3B7155A485548A47D068A9793C4FF16F4E941@m-ncid-1.ncid.cdc.gov>
Content-Type: text/plain; format=flowed

Mark ,

I bet the following would be a better approach in yoru overall program than
running N Proc SQL's.

%Macro GetVal ( Data = , Where = , GetVar = ) ;
%Local RC DSNID VN MyVal ;

%Let DSNID = %Sysfunc( Open( &Data ( Where = ( &Where ) ) , i ) ) ;

%Let RC    = %Sysfunc( Fetch(   &DSNID , NoSet   ) ) ;
%Let VN    = %Sysfunc( VarNum(  &DSNID , &GetVar ) ) ;

%If ( %SysFunc( VarType( &DSNID , &VN ) ) = N ) %Then %Do ;
  %Let MyVal = %Sysfunc( GetVarN( &DSNID , &vn     ) ) ;
%End ;
%Else %Do ;
  %Let MyVal = %Sysfunc( GetVarC( &DSNID , &vn     ) ) ;
%End ;

%Let RC    = %Sysfunc( Close(   &DSNID           ) ) ;

&MyVal

%Mend GetVal ;


%Put >>>%GetVal( Data   = SASHELP.Class  ,
                            Where  = Name = "Alice" ,
                             GetVar = Age             ) ;




Toby Dunn

To sensible men, every day is a day of reckoning. ~John W. Gardner

The important thing is this: To be able at any moment to sacrifice that
which we are for what we could become. ~Charles DuBois

Don't get your knickers in a knot. Nothing is solved and it just makes you
walk funny. ~Kathryn Carpenter






From: "Lamias, Mark (CDC/CCID/OD) (CTR)" <bnz6@CDC.GOV>
Reply-To: "Lamias, Mark (CDC/CCID/OD) (CTR)" <bnz6@CDC.GOV>
To: SAS-L@LISTSERV.UGA.EDU
Subject: Macro Variable Problem (or should I say, coder problem)
Date: Wed, 24 Jan 2007 15:21:34 -0500

All,

I am working on a piece of macro code that uses "call execute" to loop
through a dataset and pass parameters to a macro.  The macro should then
execute proc sql statements and place the selected variable into a new
macro variable (via a select into statement).  Then the new macro
variable should be used throughout the remainder of the macro to be used
in data manipulations, etc.

The problem I am running into is that the macro variable that is created
during the proc sql statement does not seem to change during each call
to the macro.  However, the macro variable DOES seems to change if I
just hard-code several calls to the macro statement.

For your reference, I have provided a simplified version of what I am
trying to do (using the sashelp.class for this example).

%macro AgeChange(name);
proc sql noprint;
select age into :MVage
from
sashelp.class
where
name="&name";
quit;

%put &MVage;
%mend;

data work.looptable;
set sashelp.class;
run;

data _null_;
set work.looptable;
call execute('%AgeChange(' || compress(name) || ');' );
run;

If I hard code
% AgeChange(Alfred);
% AgeChange(Alice);

Instead of executing the data _null_ block of code above, I get the
following as expected in the log:
14
13

However, if I run the data _null_ block of code which should be making
the same "%AgeChange()" calls to the macro, I do not get these results.

It appears the macro variables created in the select into statement are
not resolving correctly after each iteration through the looptable.  So
my question is: how can I get them to resolve correctly (or at least the
way I'm intending them to) at each iteration?

Thanks for your help.

Mark J. Lamias

_________________________________________________________________
Check out all that glitters with the MSN Entertainment Guide to the Academy
Awards®   http://movies.msn.com/movies/oscars2007/?icid=ncoscartagline2