topic Re: using macro variable for proc sql like condition in SAS Programming

using macro variable for proc sql like condition

nov — Tue, 18 Nov 2014 16:05:12 GMT

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.

Re: using macro variable for proc sql like condition

RW9 — Tue, 18 Nov 2014 16:23:35 GMT

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

Re: using macro variable for proc sql like condition

nov — Tue, 18 Nov 2014 16:47:13 GMT

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?

Re: using macro variable for proc sql like condition

RW9 — Tue, 18 Nov 2014 18:09:19 GMT

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.

Re: using macro variable for proc sql like condition

Tom — Tue, 18 Nov 2014 18:38:47 GMT

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;

Re: using macro variable for proc sql like condition

Ksharp — Wed, 19 Nov 2014 13:47:36 GMT

Why not use SYMGET() ?

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

Xia Keshan

Re: using macro variable for proc sql like condition

loweowen — Fri, 19 May 2017 03:24:20 GMT

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 🙂