topic Re: Use of 'case when + contains/like' when i have number such as 50 and 500 in SAS Enterprise Guide

Use of 'case when + contains/like' when i have number such as 50 and 500

Primohunter — Mon, 28 Nov 2022 15:11:19 GMT

Hello everyone,
i m trying to create a new column (using case when and contains/like) depending on the context of another string column, but i find it troublesome when it comes to containing numbers such as 50 and 500 for example:

HAVE DATASET

CPP

blabla DP50_L

blablablu DP 50 L

ble DP50

blx DP50_500

blibli DP500_L

bliblibla DP 500 L

bliblible DP500

blublu DP500 50

WANT DATASET

CPP SPEED

blabla DP50_L 50

blablablu DP 50 L 50

ble DP50 50

blx DP50_500 50

blibli DP500_L 500

bliblibla DP 500 L 500

bliblible DP500 500

blublu DP500 50 500

As a result, i have faulty classification. What is the proper way to create the column of the example above?

Thanks alot for your time and insights!

Re: Use of 'case when + contains/like' when i have number such as 50 and 500

LinusH — Mon, 28 Nov 2022 15:34:47 GMT

Data cleansing is a cumbersome job, and sometimes it's hard to get it 100& perfect.

My take would to find starting position of string "DP", substring the following 4 characters, remove any non numerical character, and then input that result to a numerical column.

Re: Use of 'case when + contains/like' when i have number such as 50 and 500

mkeintz — Mon, 28 Nov 2022 15:47:14 GMT

I think there is some ambiguity in your sample WANT. Namely, what is the rule you intend to apply to get the speed values? In particular, is it the first number in the text of CPP? Is it the first number after the string 'DP'?

If it's the earlier (first number in the text of CPP):

data have;
  input cpp $char25. ;
datalines;
blabla DP50_L
blablablu DP 50 L
ble DP50
blx DP50_500 
blibli DP500_L
bliblibla DP 500 L
bliblible DP500
blublu DP500 50
run;

data want;
  set have;
  *find position of first numeric character,  call it _char1 ;
  _char1=anydigit(cpp);

  *generate substring length until first non-numeric character after _char1, call it _length;
  _length=notdigit(substr(cpp,_char1))-1;

  *Read that substring into a numeric variable;
  speed=input(substr(cpp,_char1,_length),best32.);
run;

Edited note: This works only if the numeric value is a non-negative integer.

Re: Use of 'case when + contains/like' when i have number such as 50 and 500

Ksharp — Tue, 29 Nov 2022 02:05:07 GMT

data have;
input CPP $40.;
cards;
blabla DP50_L
blablablu DP 50 L
ble DP50
blx DP50_500 
blibli DP500_L
bliblibla DP 500 L
bliblible DP500
blublu DP500 50
;

proc sql;
select cpp,case when cpp like '%50%' then 
case when substr(cpp,find(cpp,'50'),3)='500' then 500 else 50 end
           else . end as want 
 from have;
quit;