Value returned in a macro

[juanvg1972]

Hi,

I am using a macro in a simliar way as a function in other languages. I want my macro to make a calculation and return value.

I need to do this in that way because I will use this calculation in many places on my code and the bes solution is a maro.

My code:

%macro obt_max;

proc sql;
select max(id_reg) into: max_reg
from tab1;
quit;

&max_reg;

%mend;

%let max = %obt_max;

I got this error:

34         %let max = %obt_max;
NOTE: Line generated by the invoked macro "OBT_MAX".
34          proc sql; select max(id_reg) into: max_reg from tab1; quit;  &max_reg;
                      ______
                      180
ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference MAX_REG not resolved.
NOTE: Line generated by the invoked macro "OBT_MAX".
34          proc sql; select max(id_reg) into: max_reg from tab1; quit;  &max_reg;
                                                                         _
                                                                         180
ERROR 180-322: Statement is not valid or it is used out of proper order.

I think that to use that way to return a value I can't use proc sql or data step code in my macro, only macrolenguage code.

Is there any other way to return a value in a macro?, any other solution?

Any advice will be greatly appreciated.

Thanks

[kiranv_]

macro language is basically a text generator. You should try 

%let max = &max_reg;

[juanvg1972]

I know it.

This is not my question

[PaigeMiller]

You are working very hard to do something very simple. Creating this macro variable &max_reg inside a macro simply isn't necessary.

Don't put this SQL in a macro at all. Then you can use &max_reg wherever you want. And then the problem of returning a macro variable value from your macro goes away.

[juanvg1972]

I know that I can put this SQL out of the macro, but I don't want to write it 50 times in my code in 50 different places, I prefer to call a macro, that's the reason I am using a macro. Also is possible that I would have to change the code of my nacro and the I only have ti change in one place not 50.

If is there any other way to return a value I will consider..

Thansk

[PaigeMiller]

So you want to execute the code below 50 times?

Why?

You will get the same answer each time.

proc sql;
select max(id_reg) into: max_reg
from tab1;
quit;

[juanvg1972]

No, I prefer a macro

[SASKiwi]

You are correct - if you want to use a macro as a function and have it embedded in another macro statement like %LET then the macro must contain only macro statements. It will work if you just pass a value back and just use it in normal SAS code.

[Reeza]

Would PROC FCMP work instead of a macro?

---

@juanvg1972 wrote:

Hi,

I am using a macro in a simliar way as a function in other languages. I want my macro to make a calculation and return value.

I need to do this in that way because I will use this calculation in many places on my code and the bes solution is a maro.

My code:

%macro obt_max;

proc sql;
select max(id_reg) into: max_reg
from tab1;
quit;

&max_reg;

%mend;

%let max = %obt_max;

I got this error:

34         %let max = %obt_max;
NOTE: Line generated by the invoked macro "OBT_MAX".
34          proc sql; select max(id_reg) into: max_reg from tab1; quit;  &max_reg;
                      ______
                      180
ERROR 180-322: Statement is not valid or it is used out of proper order.

WARNING: Apparent symbolic reference MAX_REG not resolved.
NOTE: Line generated by the invoked macro "OBT_MAX".
34          proc sql; select max(id_reg) into: max_reg from tab1; quit;  &max_reg;
                                                                         _
                                                                         180
ERROR 180-322: Statement is not valid or it is used out of proper order.

I think that to use that way to return a value I can't use proc sql or data step code in my macro, only macrolenguage code.

Is there any other way to return a value in a macro?, any other solution?

Any advice will be greatly appreciated.

Thanks

---

[novinosrin]

@juanvg1972  Very interesting question and thank you for making me want to learn something my tests below continue to fail

%macro obt_max;
%global max_reg;
%let max_reg=;
proc sql;

select max(height) into : max_reg
from sashelp.class;
quit;

%let max_reg=&max_reg ;

&max_reg

%mend;

%let max = %obt_max;

no luck

is it possible to get russ tyndall's or ron fehd attention here for greater explanation? or whomsoever can help whats going on

[SASKiwi · Accepted solution]

Calling as function doesn't work because you are trying to run normal SAS code inside a single macro statement:

%macro obt_max;

%let max_age=;

proc sql noprint;
select max(age) into: max_age
from sashelp.class;
quit;

&max_age

%mend;

%let max_age2 = %obt_max;

Printing result in %PUT or normal SAS code works:

%macro obt_max;

%global max_age;
%let max_age=;

proc sql noprint;
select max(age) into: max_age
from sashelp.class;
quit;

%mend;

%obt_max;

%put max_age= &max_age;

Example of macro that works like a function - just macro statements work OK:

%macro row_count (tablename =);
  %* OBS method uses SCL to open a table, get the row count, close it and return the row count
     so it works like a function.;
    %local dsid obs; 
    %let dsid = %sysfunc(open(&tablename)); 
    %if &dsid %then %let obs = %sysfunc(attrn(&dsid, nlobs)); 
    %let tmp_varlist = &obs;
    %let dsid = %sysfunc(close(&dsid)); 
    &obs
%mend row_count; 

%let rows = %row_count(tablename = sashelp.class);
%put rows = &rows;

[novinosrin]

@SASKiwi  Thank you sir/madam for the great illustration. That was really bugging me. Cheers!