Macro variable not refreshing at run-time

[kdp]

Hello,

I am having a problem with refreshing macro variables using proc sql inside a macro program. I have tried to resolve this 3 different times before and have given up each time, but I need this to work. Here's my code:

%macro ILikeThisMacro;
do something;

proc sql noprint;

select count(distinct(name))
into :drv_cnt
from dictionary.columns
where memname = upcase("&forecast_input_data_set")
and varnum > 11;

select distinct name
into :drvs separated by " "
from dictionary.columns
where memname = upcase("&forecast_input_data_set")
and varnum > 11;

select distinct quote(trim(left(name)))
into :drvs_name separated by ","
from dictionary.columns
where memname = upcase("&forecast_input_data_set")
and varnum > 11;

quit;

data ldp.&forecast_input_data_set (drop= i);
set ldp.&forecast_input_data_set;

array driv_cols(&drv_cnt) &drvs;
array driv_list(&drv_cnt) $ _temporary_ (&drvs_name);

do i = 1 to &drv_cnt;
if find(forecast_drivers, driv_list(i)) < 1 then driv_cols(i) = 0;
end;

run;

do something;

%mend ILikeThisMacro;

data _null_;
set filelist;
call execute %ILikeThisMacro.....;
run;

Basically, I run the "ILikeThisMacro" for multiple files and each time the proc sql should refresh the following macro variables: &drv_cnt, &drvs, and &drvs_name.
However, it seems that the macro variables are being created at compile time and then those same values are being used for every instance of the "ILikeThisMacro".

When the proc sql is not enclosed inside a macro program and is run once, it works BEAUTIFULLY!

Any suggestions?

Thanks,
kdp

[sbb]

You either must pass your macro variable values at macro invocation time, or you must declare global macro variables in your DATA step execution explicitly.

Best that you can see the logic / flow processing by turning on diagnostics with:

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC MPRINT;

This additional expanded SAS code will help with debugging your program.

The bottom-line is that a CALL EXECUTE happens "after" the DATA step completes so its up to your program to create the macro variable (value) environment with each DATA step iteration by supplying those meaningful variable values you need with each execution of your macro .

Scott Barry
SBBWorks, Inc.

[Peter_C]

kdp,
Scott is pointing at the issue.

SQL into :mvar

Inside a macro, this is declared by the SQL Macro Language Reference 9.2 as being subject to the same rules as %LET in selecting the symbol table where the value will be stored.. When referred to for the first time, a macro variable is created in the local (or nearest) symbol table.
Scott and I are guessing this is the problem here.
Rather than defining these as GLOBAL I would use %LET to define the macro variables just before you invoke %iLikeThisMacro

good luck
PeterC

[sbb]

In essence, when declaring a %LET statement outside a macro, one is defining a SAS global type macro variable - which is what I meant to convey.

Scott Barry
SBBWorks, Inc.

[Peter_C]

Scott

rather than using %GLOBAL, I prefer and recommend using %LET in the calling environment, because often a calling environment is not global.
There is added advantage (reducing risk) in avoiding the possibility of error occuring inside a macro when making global a name that is already defined with a local scope.

PeterC

[sbb]

A %LET destroys the macro variable if it already exists - %GLOBAL declaration does not. The purpose and opportunity for each approach is situation/application specific.

Scott Barry
SBBWorks, Inc.

[kdp]

Thanks Scott and Peter!

I think the lesson is that I can't use proc sql to generate macro variables inside a macro. I would have to pass them as parameters when I am invoking the macro.

However, in my case I don't think either %LET or %GLOBAL would work since I need to pass different values for the macro variables for each invocation of the macro.

I have started to write some code that would figure out the exact values to pass to the macro and I think I am almost there!

Thanks again!
kdp

[Peter_C]

kdp

no, you can develop your solution using sql, unless of course, you are changing the definition of the problem!
if the version of your macro that you posted earlier is what you expect to use, only the way you use it would need to change.
Your macro places values into 3 macro variables
DRV_CNT, DRVS and DRVS_NAME
These you use to specify the size of an array, provide the element names for the array, and provide a list of quoted names.
So I see no need for these macro variable names to vary.
define a parametrer for your macro
%macro iLikeThisMacro( forecast_input_data_set ) ;
To use your macro within a call-execute context [pre]
%let DRV_CNT = ;
%let DRVS = ;
%let DRVS_NAME = ;
data _null_ ;
set filelist ; * providing the name of forecast dataset in var DSN;
putlog 'invoke for ' dsn= ;
call execute( '%nrstr(%%iLikeThisMacro)' ); * deferring execution until after;
call execute( '(' !! dsn !! ')' ) ;
run ;[/pre]You might want to protect for the condition where your macro extracts no names so DRV_CNT becomes zero.

Many times I have used sql to fill macro variables with lists of values, while running in a macro, so I can assure you that it can be made to work.

Scott
destroying any pre-existing value in the macro variable seemed important to do, in this situation where the process needs to place a value into these macro variables, and avoid inheriting any previous iterations' values.

PeterC

[kdp]

Thanks Peter, I finally had a chance to implement your suggestion and it WORKS!!!.

I had to consult other SAS experts around me on exactly what your code was doing, but now I understand it.

Thanks for your help!
Ketan