Splitting Text into different variables

[altijani]

Greetings Community,

I have the following variable that does not contain a delimiter. It can be as each of the following combinations, and there are infinity combinations:

Variable1

123456789

123456789CLOSED

123456-P

123456-PCLOSED

123456

123456CLOSED

987654321

25648985

Whenever there is an observation that ends with CLOSED, then there must be another observation without CLOSED at the end.

Thanks

Altijani

[PaigeMiller · Accepted solution]

UNTESTED CODE

data want;
    set have;
    where_closed = find(variable1,'closed','i');
    if where_closed>0 then id=substr(variable1,1,where_closed-1);
    else id=variable1;
run;
proc sql;
    create table want2 as select variable1 from want
    group by id
    having closed=max(closed);
quit;

[Reeza]

I'm pretty lazy, I'd probably use COMPRESS with KD and KA modifiers to separate the letters and numbers.

data have;
input Text $30.;
cards;
123456789
123456789CLOSED
123456-P
123456-PCLOSED
123456
123456CLOSED
987654321
25648985
;
run;

data want;
set have;

ID = compress(text, , 'kd');
ID_NUM = input(ID, 8.);
Text = compress(text, , 'ka');
run;