BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ayin
Quartz | Level 8
%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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

7 REPLIES 7
kiranv_
Rhodochrosite | Level 12

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

 

  

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.  

ballardw
Super User

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?

Patrick
Opal | Level 21

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

 

Tom
Super User Tom
Super User

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=
ayin
Quartz | Level 8
The problem has been found. Should've used double marks: "memname contains upcase("&fileName")", rather than "memname contains upcase('&fileName'). Thank you guys!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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