topic Re: like operand imprecision in SAS Procedures

like operand imprecision

tellmeaboutityo — Thu, 28 Sep 2017 17:50:24 GMT

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?

Re: like operand imprecision

HB — Thu, 28 Sep 2017 18:43:31 GMT

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;

Re: like operand imprecision

Tom — Thu, 28 Sep 2017 19:12:36 GMT

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

Re: like operand imprecision

Astounding — Thu, 28 Sep 2017 19:15:15 GMT

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

data want;

set have;

if var = : 'datal_' then delete;

run;

Re: like operand imprecision

mkeintz — Thu, 28 Sep 2017 21:09:12 GMT

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;

Re: like operand imprecision

RexDeus9 — Fri, 29 Sep 2017 11:46:02 GMT

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;

🙂