BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
altijani
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller

View solution in original post

2 REPLIES 2
PaigeMiller
Diamond | Level 26

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;
--
Paige Miller
Reeza
Super User

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;

sas-innovate-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

Register now!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 1475 views
  • 2 likes
  • 3 in conversation