using macro variable for proc sql like condition

Accepted Solution Solved
Reply
New Contributor nov
New Contributor
Posts: 4
Accepted Solution

using macro variable for proc sql like condition

Is it possible to use a macro variable for proc sql like condition?

Such as :

proc sql;

   create table key as

   select * from c.numkeys

    where upcase(esc) like  '%&&name&i.%' ;

          run;

The name and i are macro variables.


Accepted Solutions
Solution
‎11-18-2014 01:38 PM
Super User
Super User
Posts: 6,315

Re: using macro variable for proc sql like condition

I think you need triple & if I understand your macro variable structure.

You can use  %QUOTE() to generate a protected percent sign so that SAS doesn't think you want to call macro.

Or you can just use the CONTAINS operator instead of the LIKE operator.

%let i=1;

%let name=value;

%let value1=A ;

proc sql  ;

select * from sashelp.class

where upcase(name) like "%quote(%%)&&&name&i%"

;

%put &sqlobs;

select * from sashelp.class

where upcase(name) contains "&&&name&i"

;

%put &sqlobs;

View solution in original post


All Replies
Esteemed Advisor
Esteemed Advisor
Posts: 7,195

Re: using macro variable for proc sql like condition

Well, I personally would avoid using several macro variables like that, i.e. &name, and &I.  It becomes very messy with all those ampersands and de-referencing.  Never actually found a time when I needed to do that.

As for your question, the like would need to have double quotes:

"%&&name&i.%"

Otherwise the macro variable won't de-reference.  As for &&this and the other...

New Contributor nov
New Contributor
Posts: 4

Re: using macro variable for proc sql like condition

The double quotes were used before. The warning messages are:

WARNING: Apparent invocation of macro  value1 not resolved.

WARNING: Apparent symbolic reference NAME not resolved.

WARNING: Apparent invocation of macro value1 not resolved.

WARNING: Apparent symbolic reference value1value1 not resolved.

The value1 is name's value. Is it a way to get out the warnings?

Esteemed Advisor
Esteemed Advisor
Posts: 7,195

Re: using macro variable for proc sql like condition

Exactly, parts of the macro variables are being de-referenced at differing times.  TBH I am not going to try to work out which order they do it as I really don't like lines of ampersands.  Post some example code and test data/output and will see what I can come up with tomorrow.

Solution
‎11-18-2014 01:38 PM
Super User
Super User
Posts: 6,315

Re: using macro variable for proc sql like condition

I think you need triple & if I understand your macro variable structure.

You can use  %QUOTE() to generate a protected percent sign so that SAS doesn't think you want to call macro.

Or you can just use the CONTAINS operator instead of the LIKE operator.

%let i=1;

%let name=value;

%let value1=A ;

proc sql  ;

select * from sashelp.class

where upcase(name) like "%quote(%%)&&&name&i%"

;

%put &sqlobs;

select * from sashelp.class

where upcase(name) contains "&&&name&i"

;

%put &sqlobs;

Grand Advisor
Posts: 9,568

Re: using macro variable for proc sql like condition

Why not use SYMGET() ?

%let value1=A ;
proc sql  ;
select * from sashelp.class
where upcase(name) contains symget('value1');
;
quit;

Xia Keshan

Senior User
Posts: 1

Re: using macro variable for proc sql like condition

Thanks so much Xia - this is just what I needed
- in particular notice the use of the plain variable rather than the variable with $ prefix didn't work inside the proc sql :-)
☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 4403 views
  • 1 like
  • 5 in conversation