topic Re: Like- proc sql in SAS Programming

Like- proc sql

Ronein — Wed, 23 Mar 2022 09:42:02 GMT

Hello

I want to use like operator in proc sql.

I want to ask why when I use macro variable then want2 data set cannot find the row??

Data have;
input IP $ 9.;
cards;
033948777
383888388
027441567
;
Run;


proc sql;
create table want1 as
select *
from have
where IP like '%27441567'
;
quit;


%let wanted_IP=27441567;
proc sql;
create table want2 as
select *
from have
where IP like "%&wanted_IP."
;
quit;

Re: Like- proc sql

PaigeMiller — Wed, 23 Mar 2022 09:49:51 GMT

The % sign is not interpreted as a text percent sign, it is interpreted as a macro symbol. So macro %27441567 is referenced, but doesn't exist. To force the percent sign to be interpreted as a text percent sign

where IP like "%nrstr(%%)&wanted_IP."

Re: Like- proc sql

Kurt_Bremser — Wed, 23 Mar 2022 09:57:51 GMT

Just split and concatenate the strings:

proc sql;
create table want2 as
select *
from have
where IP like '%' !! "&wanted_IP."
;
quit;