data step calling a macro that runs sql into macro variable

[imarosel]

One that has me chasing my tail. This is a building block to a more complicated program I'm writing and something I found myself wishing I could do on more than one occasion.

I want the data step to pass in a variable value into a macro that runs a query on another dataset, retrieves a value that can then be used in the data step. I'm trying to run the following to put the retrieved value in its own column. I'm using SAS enterprise guide and its printing out the results of the SQL in an HTML file which are what I expect, but the values in the output data set are not, every row has the result that the variable was set to on the first run, its like the macro variable isn't updating on the subsequent calls.

%MACRO TESTMACRO(gen);
PROC SQL;
SELECT max(intervalindex) into :TESTVAR FROM EOC where gen_code="&gen";
QUIT;
%MEND TESTMACRO;

DATA TEST;
SET EOC;
CALL SYMPUT('gen',GEN_CODE);
CALL EXECUTE('%TESTMACRO(&gen)');
TESTNUM1=&TESTVAR;
RUN;

 

[kdt82]

Try the following to dynamically pass datastep variables to a macro call.

%MACRO TESTMACRO(gen);
    PROC SQL;
    SELECT max(intervalindex) into :TESTVAR FROM EOC where gen_code="&gen";
    QUIT;
%MEND TESTMACRO;

DATA TEST;
    SET EOC;
    CALL EXECUTE('%TESTMACRO('||GEN_CODE||')');
    TESTNUM1=&TESTVAR;
RUN;

 

[ChrisW75]

Nickdel had the exact same problem here:-

http://www.tek-tips.com/viewthread.cfm?qid=1493727&page=1

I'm pretty sure that it's impossible to call a macro that uses a datastep or procedure inside another datastep for the reasons I outlined there.
Basically, when SAS executes the macro, it effectively substitutes the code into the place where the macro is called. A proc or data statement effectively ends the datastep in which it is called.
This is the way I've always understood it anyway.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[imarosel]

Well you could be right, using kdt82 suggestion gets me the exact same results.

 

[ChrisW75]

An alternative method, if you're comfortable with Macros and macro variables if to load the info into macro variables beforehand, then loop through them. It's the way I usually handle this kind of scenario.


Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.

 

[imarosel]

I don't follow. I'm not too familiar with macros or macro variables so that is problem number 1 with me not following.

If i'm on a datastep and want to write a value to the vector that is based on a lookup to another dataset that is dependent on a variable in my current vector I'm not sure what I would have loaded up before hand in the macro variables. My data set has about 6,000 rows with about 600 unique values in it that I'm using to lookup values from the other dataset. I don't see what I can load up ahead of time.

My explanation might be too vague, do you have some sample code of what you are talking about?

Thanks,
Austin

 

[ChrisW75]

It sounds to me like you actually need to use a join here:-

* Set up your MAX variables beforehand *;
proc sql;
  create table maxes as
  select GEN_CODE
        ,max(intervalindex)  as testvar
  from EOC
  group by gen_Code
  ;
quit

* Sort the original data *;
proc sort data=eoc;
  by gen_code;
run;

* Join the maximums back onto the original data for testing. *;
DATA TEST;
    merge EOC(in=in1)
          maxes(in=in2);
    by gen_code;
RUN;

There you go, no macro code required at all, and that should prodice exactly what you were trying to produce.
It might be possible to condense this down to 1 single SQL step, but this way is much easier to follow I think.

The Macro Loop code is something I use for when I want to do something to specific groups of records in a dataset, looping through each set, it doesn't actually apply in this scenario.

Chris
Business Analyst, Code Monkey, Data Wrangler.
SAS Guru.