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,
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;
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;
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
Thanks a lot.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.