BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DeepakSwain
Pyrite | Level 9

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
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

3 REPLIES 3
Reeza
Super User

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;
AhmedAl_Attar
Rhodochrosite | Level 12

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

DeepakSwain
Pyrite | Level 9

Thanks a lot. 

Swain

sas-innovate-2024.png

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.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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