Desktop productivity for business analysts and programmers

Updating global macro variable using macros

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 77
Accepted Solution

Updating global macro variable using macros

%let macroValue = 3;
%macro test(); %global macroValue; proc sql noprint; select name into: macroValue separated by ' ' from dictionary.columns where libname = 'WORK' and memname contains 'TEST'; quit; %mend test;
%test; %put _all_; run;

I'm trying to update the value of the macro variable 'macroValue' using a macro named 'test'

the dataset work.test have columns like 'V1' 'V2', so the updated value for that macro variable should be 'V1 V2', but apparently it's still '3'.

 

Can anyone help fix it up? Thanks!


Accepted Solutions
Solution
‎06-04-2017 06:21 AM
Super User
Super User
Posts: 6,313

Re: Updating global macro variable using macros

[ Edited ]

If you want to update the macro variable when no results match the SQL query you can test the automatic macro variable SQLOBS.

Let's update your macro to make it a little more robust.

%macro test(memname,libname=work,mvar=namelist);
%local dummy ;
%if not %symexist(&mvar) %then %global &mvar ;
proc sql noprint;
  select varnum, name
    into :dummy,:&mvar separated by ' '
  from dictionary.columns
  where libname = %upcase("&libname")
    and memname = %upcase("&memname")
  order by varnum
  ;
%if 0=&sqlobs %then %let &mvar=;
quit;
%mend test;

Now let's test it with an dataset that exists.

data test;
  length v1 v2 $8 ;
  array c _character_;
  stop;
run;

%test(test);
%put &=namelist;

So we get the expected result:

5530  %put &=namelist;
NAMELIST=v1 v2

Now let's try it with a dataset that doesn't exist.

proc delete data=test; run;
%test(test);
%put &=namelist;

And you can see that &NAMELIST is now empty.

5534  %put &=namelist;
NAMELIST=

View solution in original post


All Replies
Regular Contributor
Posts: 223

Re: Updating global macro variable using macros

[ Edited ]

I did exactly same thing and it worked for me. below is the code

 

%let macroValue = 3;
%macro test();
%global macroValue1;
proc sql print;
select name into :macroValue separated by ' '
from dictionary.columns
where upcase(libname) = 'SASHELP'
and upcase(memname) ='CARS';
quit;
%mend test;
%test
%put &macrovalue;

 

output i get is 

Make Model Type Origin DriveTrain MSRP Invoice EngineSize Cylinders Horsepower MPG_City MPG_Highway Weight Wheelbase

 

I am not sure whether below mentioned is working or not

memname contains 'TEST';

 

  

 

Esteemed Advisor
Posts: 6,636

Re: Updating global macro variable using macros

Post the log.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Esteemed Advisor
Esteemed Advisor
Posts: 7,190

Re: Updating global macro variable using macros

Why?  Two reasons, firstly macro language does nothing on its own, hence knowing when to use it is a big part of it.  What you have presented here does nothing - i.e. you can put this bit:

select name from dictionary.columns
where libname = 'WORK' and memname contains 'TEST';

Into a sub-query and use it in normal base SAS code, keeping your code nice and simple and straight forward.

Secondly, scope is there for a reason.  If you start defining global variables within macros, or altering macro variables outside their scope, you can end up with a truly awfull mess to try debug.  

Grand Advisor
Posts: 10,196

Re: Updating global macro variable using macros

[ Edited ]

Since we do not have the contents of your library it is extremely hard to actually determine why your code may not work.

When I use the SASHELP library I get the explected result from your code though:

%let macroValue = 3;
%macro test();
%global macroValue;
proc sql noprint;
select name into: macroValue separated by ' '
from dictionary.columns
where libname = 'SASHELP' and memname contains 'CLASS';
quit;
%mend test;
%test;

%put &macrovalue;

Yields:

 

Name Sex Age Height Weight Name Sex Age Height Weight predict lowermean uppermean lower upper

as expected.

 

BTW why is there a RUN statement in your code example? No proc or data step was used. Perhaps you did not share all of the code and the bit you didn't show also modifies Macrovalue?

Respected Advisor
Posts: 3,823

Re: Updating global macro variable using macros

@ayin

The code you've posted works as such. 

I can replicate what you describe when I use values in the SQL where clause which don't return a single row. In such a case the macro variable doesn't get altered (I'd prefer if it would be set to missing but that's not what SAS does).

 

Check your log. You should see something like: 

NOTE: No rows were selected.

 

 

27         %let macroValue = 3;
28         
29         %macro test();
30           %global macroValue;
31         
32           proc sql noprint;
33             select name into: macroValue separated by ' '
34               from dictionary.columns
35                 where libname = 'WORK' and memname contains 'TEST';
36           quit;
37         
38         %mend test;
39         
40         %test;
NOTE: No rows were selected.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

41         %put &=macroValue;
MACROVALUE=3

 

The moment your SQL returns rows you'll get the result you'd expect

27         %let macroValue = 3;
28         
29         %macro test();
30           %global macroValue;
31         
32           proc sql noprint;
33             select name into: macroValue separated by ' '
34               from dictionary.columns
35                 where libname = 'SASHELP' and memname contains 'CLA';
36           quit;
37         
38         %mend test;
39         
40         %test;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds
      

41         %put &=macroValue;
MACROVALUE=Name Sex Age Height Weight Name Sex Age Height Weight predict lowermean uppermean lower upper

 

What above log also shows you: Be careful with contains like used in and memname contains. Using contains can match with multiple table names and though return a list of variable names from multiple tables. Is that what you want to happen?

 

Solution
‎06-04-2017 06:21 AM
Super User
Super User
Posts: 6,313

Re: Updating global macro variable using macros

[ Edited ]

If you want to update the macro variable when no results match the SQL query you can test the automatic macro variable SQLOBS.

Let's update your macro to make it a little more robust.

%macro test(memname,libname=work,mvar=namelist);
%local dummy ;
%if not %symexist(&mvar) %then %global &mvar ;
proc sql noprint;
  select varnum, name
    into :dummy,:&mvar separated by ' '
  from dictionary.columns
  where libname = %upcase("&libname")
    and memname = %upcase("&memname")
  order by varnum
  ;
%if 0=&sqlobs %then %let &mvar=;
quit;
%mend test;

Now let's test it with an dataset that exists.

data test;
  length v1 v2 $8 ;
  array c _character_;
  stop;
run;

%test(test);
%put &=namelist;

So we get the expected result:

5530  %put &=namelist;
NAMELIST=v1 v2

Now let's try it with a dataset that doesn't exist.

proc delete data=test; run;
%test(test);
%put &=namelist;

And you can see that &NAMELIST is now empty.

5534  %put &=namelist;
NAMELIST=
Frequent Contributor
Posts: 77

Re: Updating global macro variable using macros

The problem has been found. Should've used double marks: "memname contains upcase("&fileName")", rather than "memname contains upcase('&fileName'). Thank you guys!
☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 204 views
  • 5 likes
  • 7 in conversation