Help using Base SAS procedures

like operand imprecision

Accepted Solution Solved
Reply
Occasional Learner
Posts: 1
Accepted Solution

like operand imprecision

Hi,


I'm trying to sort out certain instances of a character variable.  The data in the variable is in the form either: 

 

data_1

dataI_1

 

The difference being the roman numeral "I' after data.  The numbers "1" can vary.  I want only data_, and to drop instances of dataI_.  Thus:

 

proc sql; create table info

as select *

from data

where var like 'data_%'

;quit;

 

Surprisingly, this does not drop instances of dataI_

 

Can anyone recommend a fix?


Accepted Solutions
Highlighted
Solution
3 weeks ago
Super User
Super User
Posts: 6,842

Re: like operand imprecision

[ Edited ]

Underscore is a special character to the LIKE operator used to match any single character. So your query will match any string of five or more characters that start with 'date'.

 

You will need to escape it if you want LIKE to treat it as a literal underscore.

var like 'data^_%' escape '^'

 

View solution in original post


All Replies
Frequent Contributor
Frequent Contributor
Posts: 132

Re: like operand imprecision

I suspect this won't work with your real data.

 

data stuff;
   input myvar $20.;
datalines;
data_1
dataI_1
data_2
dataI_2
data)_1
;
run;


proc sql; 
	create table only_no_Is
	as select *
	from stuff
	where substr(myvar, 5, 1) not in ("I");
quit;

Yeilds

 

myvar
data_1
data_2
data)_1

prxmatch would probably be better. I would use it.

 

data no_Is;
	set stuff;
	if not prxmatch("m/I/",myvar);
run;
Highlighted
Solution
3 weeks ago
Super User
Super User
Posts: 6,842

Re: like operand imprecision

[ Edited ]

Underscore is a special character to the LIKE operator used to match any single character. So your query will match any string of five or more characters that start with 'date'.

 

You will need to escape it if you want LIKE to treat it as a literal underscore.

var like 'data^_%' escape '^'

 

Super User
Posts: 5,352

Re: like operand imprecision

If you're allowed to use a DATA step, the problem is trivial:

 

data want;

set have;

if var = : 'datal_' then delete;

run;

Valued Guide
Posts: 947

Re: like operand imprecision

IN the data step there is the '=:' operator, which first shortens the longer operand to lenght of the shorter, then compare for equality:

 

data want;
   set have;
   where   var =: 'data_';
run;

 

PROC SQL does not support '=:' but it does have EQT (and GET and LET) "truncated string comparison operators":

 

data have;
  var='dataI_1';output;
  var='data_1'; output;
run;

proc sql;
  create table info2
  as select *
  from have
  where var EQT 'data_';
quit;
Occasional Contributor
Posts: 6

Re: like operand imprecision

Hi,

 

I don't have an answer for the "like" issue, but the following works fine:

 

data info;

    Set data;

    if LowCase(var) =: "data_";

run;

 

 

 

:-)

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 285 views
  • 5 likes
  • 6 in conversation