DATA Step, Macro, Functions and more

Extract content between pipes

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 120
Accepted Solution

Extract content between pipes

Hi there,

I have a data element having values between multiples pipes. I want to extract the value contained between 7th and 8th pipe of each record. 

data have;
id=101; report="|1||S17-13284|11529-5|||20170602|||54295|"; output;
id=102; report="|1||S17-13285|11529-5|ABC||20170603|||54296|"; output;
id=103; report="CDE|1||S17-13286|11529-5|||20170604|||54297|"; output;
run;

data want;
id=101; report_date="20170602"; output;
id=102; report_date="20170603"; output;
id=103; report_date="20170604"; output;
run;

/*sascode tried*/
data got ;
 set have;
 length report report_date $50;
 report_date=scan(report,7,"|");
run;

Thank you in advance for your kind reply. 

 

With regards,

Swain

Accepted Solutions
Solution
‎03-21-2018 12:19 PM
Super User
Posts: 23,773

Re: Extract content between pipes

Posted in reply to DeepakSwain

Use the m modifier in SCAN:

 

m or M

specifies that multiple consecutive delimiters, and delimiters at the beginning or end of the string argument, refer to words that have a length of zero. If the M modifier is not specified, then multiple consecutive delimiters are treated as one delimiter, and delimiters at the beginning or end of the string argument are ignored.

 

data got ;
 set have;
 array check (20) $30.;
 x=countc(report, '|');
 do i=1 to x;
 check(i)=scan(report,i,"|", 'm');
 end;
run;

View solution in original post


All Replies
Solution
‎03-21-2018 12:19 PM
Super User
Posts: 23,773

Re: Extract content between pipes

Posted in reply to DeepakSwain

Use the m modifier in SCAN:

 

m or M

specifies that multiple consecutive delimiters, and delimiters at the beginning or end of the string argument, refer to words that have a length of zero. If the M modifier is not specified, then multiple consecutive delimiters are treated as one delimiter, and delimiters at the beginning or end of the string argument are ignored.

 

data got ;
 set have;
 array check (20) $30.;
 x=countc(report, '|');
 do i=1 to x;
 check(i)=scan(report,i,"|", 'm');
 end;
run;
Super Contributor
Posts: 282

Re: Extract content between pipes

Posted in reply to DeepakSwain

Hi,

 

I know this is marked as solved, but here is an alternative solution, if you are looking for a particular pattern

data want(keep=id report_date);
set have;
  pos = prxmatch('/\d{8}\|\|\|/',report);
  if (pos) then
  	report_date = substr(report,pos,8);
run;

Ahmed

Frequent Contributor
Posts: 120

Re: Extract content between pipes

Posted in reply to AhmedAl_Attar

Thanks a lot. 

Swain
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 135 views
  • 4 likes
  • 3 in conversation