Extract value of a string after a specific string

[daradanye]

Hi,

I have a dataset like this:

ID1	ID2
33567	23258:1， 33567:4， 55765:10
11267	20135:2，55367:5， 54765:1

What I want to do is: (1) determine if ID2 contains ID1; (2) if so, extract the number after it. Following is what I want:

ID1	ID2	Num
33567	23258:1， 33567:4， 55765:10	4
11267	20135:2，55367:5， 54765:1	0

It seems that scan and index cannot work directly here.  That would be great if someone can help here.

Thanks.

[ballardw · Accepted solution]

One way

data have;
   infile datalines dlm='|';
   input id1 $ id2:$30.;
datalines;
33567|23258:1 33567:4, 55765:10
11267|20135:2，55367:5, 54765:1
;


data want;
   set have;
   pos = findw(id2,strip(id1),' :,','E');
   if pos>0 then num = input(scan(id2,pos+1,' :,'),f5.);
   else num=0;
   drop pos;
run;

If your ID1 variable is actually numeric you need to convert it character for the FINDW. Replace Id1 with: put(id1,best6. -L)

The FINDW used this way returns the number of the word in the string of the first match or 0 if the value is not found in Id2.

The uses Scan to extract the word following in use the Input function to convert to numeric.

Note: if there is nothing following the found "word" you may get an invalid data from scan function or possibly the start of the next id:num pair.