Extract part of a string in reference to a Key word and positional delimiter

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 115
Accepted Solution

Extract part of a string in reference to a Key word and positional delimiter

I am trying to extract part of a string available between 3rd and 4th pipe following a key word "ABC". Can anybody please assist me to move forward. 

data HAVE;
length id $3 comment $50 ;
input id comment $ ;
datalines;
101 ABC|X01|X02|2001|DEF|X01|X02
102 X02|ABC|X001|X002|2002|DEF
103 X04|X02|ABC|X0001|X0002|2003|DEF|X01|X02
104 X05|X02|X02|ABC|X00001|X00002|2004|DEF
105 X02|ABC|X01|X02|2005|DEF
;
RUN;


data WANT;
length id $3 comment $50 ;
input id comment $ ;
datalines;
101 2001
102 2002
103 2003
104 2004
105 2005
;
RUN;

Thank you in advance for your kind guidance.

Swain

Accepted Solutions
Solution
‎03-09-2018 01:18 PM
Super User
Super User
Posts: 8,987

Re: Extract part of a string in reference to a Key word and positional delimiter

Posted in reply to DeepakSwain

Why do you have multiple data items in one variable, this is not a good method of storing or processing data - hence why you have a few questions on this topic.  Split your data up.  As for your question, its exactly the same as before, except you have another substr():

data HAVE;
length id $3 comment $50 ;
input id comment $ ;
datalines;
101 ABC|X01|X02|2001|DEF|X01|X02
102 X02|ABC|X001|X002|2002|DEF
103 X04|X02|ABC|X0001|X0002|2003|DEF|X01|X02
104 X05|X02|X02|ABC|X00001|X00002|2004|DEF
105 X02|ABC|X01|X02|2005|DEF
;
RUN;

data want (drop=ext_str);
set have;
length ext_str want $200;
ext_str=substr(comment,index(comment,"ABC")+3);
want=scan(ext_str,3,"|");
run;

note, you can combine the two steps.

View solution in original post


All Replies
Solution
‎03-09-2018 01:18 PM
Super User
Super User
Posts: 8,987

Re: Extract part of a string in reference to a Key word and positional delimiter

Posted in reply to DeepakSwain

Why do you have multiple data items in one variable, this is not a good method of storing or processing data - hence why you have a few questions on this topic.  Split your data up.  As for your question, its exactly the same as before, except you have another substr():

data HAVE;
length id $3 comment $50 ;
input id comment $ ;
datalines;
101 ABC|X01|X02|2001|DEF|X01|X02
102 X02|ABC|X001|X002|2002|DEF
103 X04|X02|ABC|X0001|X0002|2003|DEF|X01|X02
104 X05|X02|X02|ABC|X00001|X00002|2004|DEF
105 X02|ABC|X01|X02|2005|DEF
;
RUN;

data want (drop=ext_str);
set have;
length ext_str want $200;
ext_str=substr(comment,index(comment,"ABC")+3);
want=scan(ext_str,3,"|");
run;

note, you can combine the two steps.

☑ This topic is solved.

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

Discussion stats
  • 1 reply
  • 92 views
  • 1 like
  • 2 in conversation