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.
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: