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
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;
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;
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.