Returning a value from another macro from within a macro

[CPAZ]

Hi,

I know this might seem trivial, but I have tried several methods for this. The problem is this, I want to call a simple macro (prodrow_lookup) from within the (main_macro) to perform a simple lookup and return the value as a macro variable, or just return the value period. (see code below). I need to keep the data _null_ statement inside the (main_macro) due to some other constraints. But I get the following result from the %put statement:

**** returnCode =

**** returnCode =

I should (would like to) get:

**** returnCode = 9

****returnCode = 4

Any help will be greatly appreciated.

Thanks.

%global returnCode;

%let returnCode=;

data prods;

length product_type $ 12;

input count product_type $;

datalines;

9 Firebird

4 Trails

;

run;

data outputdata;

length product_type $ 12;

input product_type $;

datalines;

Firebird

Trails

;

run;

%macro prodrow_lookup(prod=);

%let myvar=%str(%'&prod%');

proc sql; select count into :returnCode from prods where product_type = %unquote(&myvar);quit;

%mend prodrow_lookup;

%macro main_macro(data=); 

%local dsid rc rownum rows cols;

%let dsid = %sysfunc(open(&data));

%let rows=%sysfunc(attrn(&dsid,nobs));

%let cols=%sysfunc(attrn(&dsid,nvars));

%do %while (%sysfunc(fetch(&dsid)) = 0); /* outloop across rows */

    %do c = 1 %to &cols; /* inner loop acrodd columns of a given rows */

      %local v t &v;

      %let v = %sysfunc(varname(&dsid,&c));

      %let t = %sysfunc(vartype(&dsid, &c));

      %let &v = %sysfunc(getvar&t(&dsid, &c));

      if &c = 1 then do; /* if I find a specific column, then execute macro prodrow_lookup */

       call execute('%prodrow_lookup(prod=&&&v)');

       %put **** returnCode = &returnCode; /* print the output of prodrow_lookup */

      end;
   %end;

%end;

%let rc = %sysfunc(close(&dsid));

run;

quit;

%mend main_macro;

%main_macro(data=work.outputdata);

[Vince28_Statcan]

If I understood properly the limitations of macro functions vs macro routines in Macro II course, since you are retrieving the macro variable value from non-macro code, you have to go with a routine (as your current code is designed).

However, unless specified otherwise, all macro variables inside a macro are local macro variables. Thus, even though you have

%global returnCode;

%let returnCode=;

outside the definition of %prodrow_lookup, the one created inside the macro is only local to the macro (and indirectly restrain the access to the global returncode from within the macro because a local scope variable of the same name exists).

%macro prodrow_lookup(prod=);

  %global returnCode;

%let myvar=%str(%'&prod%');

proc sql; select count into :returnCode from prods where product_type = %unquote(&myvar);quit;

%mend prodrow_lookup;

The above *should* fix your issue. Another alternative would be to make %prodlow_lookup be a macro function that only parses the code

proc sql; select count into :returnCode from prods where product_type = %unquote(&myvar);quit;

into your outer macro effectively creating the :returnCode locally to the outer macro rather than the inner.

Vincent

[sandyming]

%global returnCode;

*delete the %let returncode=;

data prods;

length product_type $ 12;

input count product_type $;

datalines;

9 Firebird

4 Trails

;

run;

data outputdata;

length product_type $ 12;

input product_type $;

datalines;

Firebird

Trails

;

run;

%macro prodrow_lookup(prod=);

proc sql noprint; select count into :returnCode from prods where strip(product_type) = "&prod";quit;

%mend prodrow_lookup;

%macro main_macro(data=);

%local dsid rc rownum rows cols;

%let dsid = %sysfunc(open(&data));

%let rows=%sysfunc(attrn(&dsid,nobs));

%let cols=%sysfunc(attrn(&dsid,nvars));

data _null_;

%do %while (%sysfunc(fetch(&dsid)) = 0); /* outloop across rows */

    %do c = 1 %to &cols; /* inner loop acrodd columns of a given rows */

      %local v t &v;

      %let v = %sysfunc(varname(&dsid,&c));

      %let t = %sysfunc(vartype(&dsid, &c));

      %let d = %sysfunc(getvar&t(&dsid, &c));

      %if &c = 1 %then %do; /* if I find a specific column, then execute macro prodrow_lookup */

       call execute('%prodrow_lookup(prod=&d)');

       %put **** returnCode = &returnCode; /* print the output of prodrow_lookup */

      %end;

   %end;

%end;

%let rc = %sysfunc(close(&dsid));

run;

quit;

%mend main_macro;

%main_macro(data=work.outputdata);

[Tom]

This doesn't look like a macro issue.  Instead it is the issue of timing of execution of code generated by CALL EXECUTE.  When you submit code to run with CALL EXECUTE it will run after the current step has finished.  So there is no way that the currently executing data step can see the results of your PROC SQL code which will actually run after the data step has finished.

[Quentin]

Agree with Tom.

I appreciate CPAZ posting good sample data.  I'm confused by the design.

Seems to me you've gone to a lot of work to mak %main_macro be close to a macro function.  So you use SCL functions to loop through the dataset.  But it can't be a true macro function, because it generates SAS language statements (CALL EXECUTE) with a SAS language semicolon.

There are of course lots of ways to do this lookup (could be a merge, or a format, or whatever).  Might be helpful if you could describe more of the big picture of your goal, and why you are taking this approach rather than doing a more traditional lookup.

--Q.

[Astounding]

Well, I know we're only seeing part of the picture.  After all, there is nothing about your code that requires the data _null_ statement to remain in place.  (CALL EXECUTE could be converted to a direct call to the macro if the DATA statement were removed.)  With that in mind, this solution would work but you'll have to decide if it does what you need in context.

Move the %PUT statement.  %put **** Return code = &returnCode;

Make it the last statement in %PRODROW_LOOKUP instead of being part of the main macro.

If you have other constraints that make that impossible, you'll have to spell them out.

Good luck.

[SveinE]

I agree that Call Execute is the main problem here. It is a timing problem where the macro variable are resolved prior to the execution. So the best option is maybe to avoid call execute. But it can probably be solved by adding %nrstr to the call.like this:

call execute('%nrstr(%prodrow_lookup(prod=&d))');

Then calling a macro from call execute  will behave almost like an ordinary macro call.

/Svein Erik

[SveinE]

Sorry, forget about this %nrstr suggesting. In this contex it did not work.

Maybe this will do:

%if &c = 1 %then %do; /* if I find a specific column, then execute macro prodrow_lookup */

  *call execute('%prodrow_lookup(prod=&&&v)');

  %prodrow_lookup(prod=&&&v);

  %put **** returnCode = &returnCode; /* print the output of prodrow_lookup */

  %end;