@Shmuel has a good solution. I was following and I wrote this code. If it adds to the discussion.
data have; /* MAIN table */
length ID 8 message_1 message_2 $40; /* adapt length to data */
infile cards truncover;
input ID message_1 $ message_2 $;
cards;
1 catcansiton:50:31123:60:nearby catcansiton:50:MJKXY32:40:nearby
2 dogcaneat:60:54637:30:fruit dogcaneat:60:DMF67XXM:40:fruit
3 beesand:30:BOTKXXM:60: beesand:30:64590:30:
4 colorofsky:40:DMF67XXM:30:blue colorofsky:40:54637:30:blue
; run;
proc format ;
value $object_correct
"MJKXY32" = "31123"
"DMF67XXM" = "54637"
"BOTKXXM" = "64590"
other = " "
;
run;
data want (Keep= ID message_1 message_2)
; /* Extract substring to check vs refernece */
/* if 0 then set refer;*/
set main;
array m[2,5] $ m11 m12 object_1 m14 m15
m21 m22 object_2 m24 m25 ;
do i=1 to 5; drop i;
m[1,i]=scan(message_1,i,':');
m[2,i]=scan(message_2,i,':');
end;
if put(object_1,$object_correct.)= object_2
then message_1=catx(":",m11, m12, object_2, m14, m15);
if put(object_2,$object_correct.)= object_1
then message_2=catx(":",m21, m22, object_1, m24, m25);
run;
If you have a whole dataset of "objects"-"object" pairs then you can follow the below example to create a format from a dataset.
SAS Help Center: Creating a Format from a CNTLIN= Data Set
... View more