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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.