%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!
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=
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 ¯ovalue;
output i get is
I am not sure whether below mentioned is working or not
memname contains 'TEST';
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.
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 ¯ovalue;
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?
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?
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=
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.