Help using Base SAS procedures

Splitting Text into different variables

Accepted Solution Solved
Reply
Contributor
Posts: 46
Accepted Solution

Splitting Text into different variables

[ Edited ]

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

 


Accepted Solutions
Solution
‎05-15-2018 07:24 AM
Respected Advisor
Posts: 3,066

Re: Splitting Text into different variables

[ Edited ]

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


All Replies
Solution
‎05-15-2018 07:24 AM
Respected Advisor
Posts: 3,066

Re: Splitting Text into different variables

[ Edited ]

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
Super User
Posts: 23,778

Re: Splitting Text into different variables

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

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