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.
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.
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.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.