topic Re: Updating global macro variable using macros in SAS Enterprise Guide

Updating global macro variable using macros

ayin — Fri, 02 Jun 2017 04:20:36 GMT

%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!

Re: Updating global macro variable using macros

kiranv_ — Fri, 02 Jun 2017 04:48:00 GMT

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';

Re: Updating global macro variable using macros

Kurt_Bremser — Fri, 02 Jun 2017 05:44:33 GMT

Post the log.

Re: Updating global macro variable using macros

RW9 — Fri, 02 Jun 2017 08:26:04 GMT

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.

Re: Updating global macro variable using macros

ballardw — Mon, 05 Jun 2017 16:50:41 GMT

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?

Re: Updating global macro variable using macros

Patrick — Sat, 03 Jun 2017 02:04:48 GMT

@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?

Re: Updating global macro variable using macros

Tom — Sun, 04 Jun 2017 04:49:33 GMT

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=

Re: Updating global macro variable using macros

ayin — Sun, 04 Jun 2017 23:22:10 GMT

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