using macro variable for proc sql like condition

[nov]

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.

[RW9]

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...

[nov]

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?

[RW9]

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.

[Tom · Accepted solution]

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;

[Ksharp]

Why not use SYMGET() ?

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

Xia Keshan

[loweowen]

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 🙂