topic Re: Value returned in a macro in SAS Programming

Value returned in a macro

juanvg1972 — Sun, 13 May 2018 21:25:12 GMT

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

Re: Value returned in a macro

kiranv_ — Sun, 13 May 2018 21:37:22 GMT

macro language is basically a text generator. You should try

%let max = &max_reg;

Re: Value returned in a macro

PaigeMiller — Sun, 13 May 2018 21:43:58 GMT

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.

Re: Value returned in a macro

juanvg1972 — Sun, 13 May 2018 21:55:44 GMT

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

Re: Value returned in a macro

juanvg1972 — Sun, 13 May 2018 21:56:21 GMT

I know it.

This is not my question

Re: Value returned in a macro

PaigeMiller — Sun, 13 May 2018 22:18:09 GMT

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;

Re: Value returned in a macro

SASKiwi — Sun, 13 May 2018 22:39:49 GMT

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.

Re: Value returned in a macro

Reeza — Sun, 13 May 2018 22:43:56 GMT

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

Re: Value returned in a macro

novinosrin — Sun, 13 May 2018 23:20:45 GMT

@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

Re: Value returned in a macro

SASKiwi — Mon, 14 May 2018 00:13:34 GMT

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;

Re: Value returned in a macro

novinosrin — Mon, 14 May 2018 02:44:24 GMT

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

Re: Value returned in a macro

juanvg1972 — Mon, 14 May 2018 14:03:11 GMT

No, I prefer a macro