topic Extract content between pipes in SAS Programming

Extract content between pipes

DeepakSwain — Wed, 21 Mar 2018 15:55:14 GMT

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,

Re: Extract content between pipes

Reeza — Wed, 21 Mar 2018 16:11:44 GMT

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;

Re: Extract content between pipes

AhmedAl_Attar — Wed, 21 Mar 2018 16:35:02 GMT

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

Re: Extract content between pipes

DeepakSwain — Wed, 21 Mar 2018 19:26:29 GMT

Thanks a lot.