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.
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;
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...
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?
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.
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;
Why not use SYMGET() ?
%let value1=A ; proc sql ; select * from sashelp.class where upcase(name) contains symget('value1'); ; quit;
Xia Keshan
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.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.