topic Re: Selecting observations from character variable that contain specific string in SAS Programming

Selecting observations from character variable that contain specific string

Caetreviop543 — Fri, 03 Apr 2020 22:04:18 GMT

I have a character variable that looks like this:

Var1
Order1
order2 OR order 3
Something else
Order3 or Order5
Text3
Order4 Or Order8

How do I select just the observations that contain 'or' in the middle of the text string, which could between two, three, or four words? Moreover, is there a way for it not to be case sensitive? I tried proc sql:

proc sql;
select * from data 
where var1 like '%OR%' or var1 like '%or%'
or var1 like '%Or%;
quit;

But that includes records which have the word 'Order', which I don't want. I thought maybe SAS would recognize the Or if I put a space around it:

proc sql;
select * from data
where var1 like '% OR %' or var1 like '% or %'
or var1 like '% Or %';
quit;

But it doesn't. I really don't want to write the text out in full, because it's very long. I appreciate any help.

Re: Selecting observations from character variable that contain specific string

novinosrin — Fri, 03 Apr 2020 22:14:49 GMT



data have;
input Var1 $30.;
cards;
Order1
order2 OR order 3
Something else
Order3 or Order5
Text3
Order4 Or Order8
;

data want;
 set have;
 if upcase(scan(var1,int(median(1,countw(var1,' '))),' '))='OR' then output;
run;
proc print noobs;run;

Var1
order2 OR order 3
Order3 or Order5
Order4 Or Order8

Re: Selecting observations from character variable that contain specific string

LeonidBatkhan — Fri, 03 Apr 2020 22:14:50 GMT

I believe you just need

where find(upcase(var1),' OR ')

Re: Selecting observations from character variable that contain specific string

novinosrin — Fri, 03 Apr 2020 22:15:48 GMT

data want;
 set have;
 want=upcase(scan(var1,int(median(1,countw(var1,' '))),' '));
 if want='OR';
run;
proc print noobs;run;

Var1	want
order2 OR order 3	OR
Order3 or Order5	OR
Order4 Or Order8	OR

Re: Selecting observations from character variable that contain specific string

PGStats — Fri, 03 Apr 2020 22:18:36 GMT

Try regular expression matching:

where prxmatch("/\w\s+or\s+\w/i", var1)

Note : The "i" makes the match case insensitive.

Re: Selecting observations from character variable that contain specific string

DavePrinsloo — Fri, 03 Apr 2020 22:46:46 GMT

Use the find function, works in data step or sql:

data want;
 set have;
 if find(upcase(var),' OR ');  /* OR has a blank before and after in quotes */
run;

Re: Selecting observations from character variable that contain specific string

Tom — Sat, 04 Apr 2020 00:35:52 GMT

Why didn't the version with the spaces work?

Anyway use INDEXW() or FINDW() if you only want to match full words.

Use UPCASE() function or the i modifier of the FINDW() function if you want to match OR, or, Or or oR.

Re: Selecting observations from character variable that contain specific string

Caetreviop543 — Sat, 04 Apr 2020 02:50:01 GMT

Thanks! The find function with upcase didn't work for some reason. It only selected observations with lower case 'or'.