DATA Step, Macro, Functions and more

Value returned in a macro

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 131
Accepted Solution

Value returned in a macro

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


Accepted Solutions
Solution
a week ago
Super User
Posts: 3,764

Re: Value returned in a macro

[ Edited ]
Posted in reply to novinosrin

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;

 

View solution in original post


All Replies
PROC Star
Posts: 499

Re: Value returned in a macro

Posted in reply to juanvg1972

macro language is basically a text generator. You should try 

 

%let max = &max_reg;
Frequent Contributor
Posts: 131

Re: Value returned in a macro

I know it.

 

This is not my question

Respected Advisor
Posts: 2,647

Re: Value returned in a macro

Posted in reply to juanvg1972

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.

--
Paige Miller
Frequent Contributor
Posts: 131

Re: Value returned in a macro

Posted in reply to PaigeMiller

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

Respected Advisor
Posts: 2,647

Re: Value returned in a macro

Posted in reply to juanvg1972

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;
--
Paige Miller
Frequent Contributor
Posts: 131

Re: Value returned in a macro

Posted in reply to PaigeMiller

No, I prefer a macro

Super User
Posts: 3,764

Re: Value returned in a macro

Posted in reply to juanvg1972

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.

Super User
Posts: 22,823

Re: Value returned in a macro

Posted in reply to juanvg1972

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


 

PROC Star
Posts: 1,296

Re: Value returned in a macro

Posted in reply to juanvg1972

@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

Solution
a week ago
Super User
Posts: 3,764

Re: Value returned in a macro

[ Edited ]
Posted in reply to novinosrin

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;

 

PROC Star
Posts: 1,296

Re: Value returned in a macro

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 162 views
  • 3 likes
  • 6 in conversation