like operand imprecision

[tellmeaboutityo]

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?

[HB]

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;

[Tom · Accepted solution]

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 '^'

[Astounding]

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

data want;

set have;

if var = : 'datal_' then delete;

run;

[mkeintz]

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;

[RexDeus9]

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;

🙂