我有一个示例表:
data data;
length code $30;
input code$;
datalines;
PPPES
PPPEW
pppESS
saf
xwq3
er32
ddES
ea9ESS
asesEo
ewlEa
;
run;
我想过滤以ES、ESS或EW结尾的行。我尝试了以下操作,但没有成功:
proc sql;
create table revised as
select *
from data
where code like ("%ES", "%ESS", "%EW")
quit;
如果一个变量以可能的字符串值列表结尾,有没有方法进行筛选?
这是我想要的输出:
data data1;
length code $30;
input code$;
datalines;
PPPES
PPPEW
pppESS
ddES
ea9ESS
;
run;
否。
显式测试每个字符串。
where code like '%ES' or code like '%ESS' or code like '%EW'
在数据步骤中,您可以使用以下任一项:
if left(reverse(code)) in: ('SE','SSE','WE');
where left(reverse(code)) in: ('SE','SSE','WE');
PROC SQL不支持:修饰符指定的截断比较。但是您可以使用WHERE=数据集选项
from data(where=(left(reverse(code)) in: ('SE','SSE','WE')))
使用"或";和简单的引号:
data data;
length code $30;
input code$;
datalines;
PPPES
PPPEW
pppESS
saf
xwq3
er32
ddES
ea9ESS
asesEo
ewlEa
;
run;
proc sql;
create table revised as
select *
from data
where code like ('%ES') or code like ('%ESS') or code like ('%EW');
quit;
在某些情况下,您可能希望用数据cross join
您的搜索项(作为数据(,或者对您的数据进行生存测试。
data endings;
length target $10;
input target $char10.;
datalines;
ES
ESS
EW
;
data have;
length code $30;
input code $char30.;
datalines;
PPPES
PPPEW
pppESS
saf
xwq3
er32
ddES
ea9ESS
asesEo
ewlEa
;
run;
* cross join;
proc sql;
create table want as
select distinct code
from have
cross join endings
having code like '%'||target
;
quit;
* existential test;
proc sql;
create table want as
select distinct code from have
where exists (
select * from endings
where code like '%'||target
);
quit;
您可能还需要通过在数据值上加大小写来处理不区分大小写的搜索。