This code handled the different positions and formats in my test data. Note the little cheat: The code creates a string of all digits found after ID, but it is placed in a variable 8 chars long, so it is only the 8 digits from the identifier that ahr kept, and following digits from telephone number etc. are discarded,
* Test data;
data have;
str = "John Smith was called at telephone number (111)-222-3333 he was notified of his update. ID: 1234-5678"; output;
str = "John Smith was called at telephone number (111)-222-3333 he was notified of his update. ID 12 345678 "; output;
str = "ID: 1234-5678 John Smith was called at telephone number (111)-222-3333 he was notified of his update."; output;
str = "ID 12 345678. John Smith was called at telephone number (111)-222-3333 he was notified of his update."; output;
str = "John Smith ID:1234 5678 was called at telephone number. (111)-222-3333 he was notified of his update."; output;
run;
* Data step with intermediate results - easier to maintain;
data want1 (drop=s1 s2);
set have;
Length Identifier $8;
s1 = translate(str,' ','.-:,');
s2 = substr(s1,index(lowcase(s1),'id '));
Identifier = compress(s2,,'kd');
run;
* Proc sql with function calls nested for use as an expression;
* Beware! nested function calls in proc sql have a habit of returning max 200 bytes regardless of input length;
proc sql;
create table want2 as
select
str,
compress(substr(translate(str,' ','.-:,'),index(lowcase(translate(str,' ','.-:,')),'id ')),,'kd') as Identifier length=8
from have;
quit;
@aasdfafafsdfsaf wrote:
Thanks for the reply. is there a way we can find a sequence of numbers after the mention of ID: in the text? I'm noticing in some of the text ID is mentioned twice where the 1234-5678 is only mentioned after the second ID
If the sample data used in below code doesn't cover all your cases then please provide amended sample data that represents the different patterns and tell us what you'd like as result (like populating multiple variables or creating multiple rows?).
Below code populates additional variables and is based on SAS Docu sample found here.
data have;
infile datalines truncover;
input str $200.;
datalines;
John Smith was called at telephone number (111)-222-3333 he was notified of his update. ID: 1234-5678
John Smith was called at telephone number (111)-222-3333 he was notified of his update. ID 12 345678
ID: 1234-5678 John Smith was called at telephone number (111)-222-3333 he was notified of his update.
ID 12 345678. John Smith was called at telephone number (111)-222-3333 he was notified of his update.
John Smith ID:1234 5678 was called at telephone number. (111)-222-3333 he was notified of his update.
John Smith ID:1234 56789 was called at telephone number. (111)-222-3333 he was notified of his update.
John Smith ID:1234 56789 was called at telephone number. There is also ID: 1234-9999 available
John Smith ID:1234 5678 was called at telephone number. There is also ID: 1234-9999 available
;
data want(drop=_:);
set have;
array pat_id {3} $8;
_prxid=prxparse('/\bid[: ]+(\d([\d- ]){6,10}\d)\b/oi');
length _pat_id $9 _i _start _stop _pos _len 8;
_start=1;
_stop=length(str);
call prxnext(_prxid, _start, _stop, str, _pos, _len);
do while (_pos > 0);
_pat_id = compress(substr(str, _pos, _len),,'kd');
/* only populate pat_id if identified string contains exactly 8 digits */
if length(_pat_id) = 8 then
do;
_i=sum(_i,1);
pat_id[_i]=_pat_id;
end;
call prxnext(_prxid, _start, _stop, str, _pos, _len);
end;
run;
proc print data=want;
run;
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 webinar
Classroom Training Available!
Select SAS Training centers are offering in-person courses. View upcoming courses for:
-
1239 views
-
2 likes
-
-
