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;
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;