Scan fields to link to other table and create new fields

[podarum]

Hi.  This is no easy task for me, but I'm sure extremely easy for some here.

I have a Table_A at the account level.  Each row has a whole bunch of message codes, some can have 20, some 30 or even more. And for one account msg_1 can = 245 and in another acct msg_1 can = 567... and so on. 

In another Table_B, I have a list of the same codes but they're located vertical.  I want to match the accounts in Table_A with the accounts in Table_B and create a new column name as the message number M245 and M567, etc.. and either a 1 or a 0 if that account has it or not.  Below is a better visual:

Table_A

Acct_no   msg_1   msg_2    msg_3   msg_4

001              1           12            999        956

002              2            23            49         999

003             12           53            3           378

Table_B

CODE_ID      Status     Notes

msg_1          Decline   ABC

msg_2          Decline   CDE

msg_3          Approve  HET

........

msg_49        Decline    HDG

........

msg_999      Approve  JDT

NEED Table_C

Acct_no   msg_1   msg_2    msg_3   msg_4    Dec_msg_1   Dec_msg_2    App_msg_3 ...... Dec_msg_49   ......   Appr_msg_999 

001              1            12           999         956             1                       0                       0                               0                                 1  

002              2            23            49          999             0                       1                       0                               1                                 1

003             12           53             3           378             0                       0                       1                               0                                 0

The Dec_ and App_ in the field name in Table_C is not neccessary (however nice), so there may be a simpler way to get to Table_C strictly just from Table_A?  I don't know.

Thank you.

[Ksharp · Accepted solution]

data a;
input Acct_no   msg_1   msg_2    msg_3   msg_4;
cards;
001              1           12            999        956
002              2            23            49         999
003             12           53            3           378
;
run;
 
data b;
input CODE_ID      Status  $   Notes $;
cards;
53          Approve  HET
12          Decline   ABC
23          Decline   CDE
3          Approve  HET
;
run;
proc sql noprint;
select catx('_',substr(status,1,3),code_id) into : list separated by ' '
 from b;
quit;
data want;
 set a;
 array x{*} msg_:;
 array y{*} &list;
 do i=1 to dim(y);
  y{i}=0;
 end;
 do i=1 to dim(x);
  do j=1 to dim(y);
   if x{i}=input(scan(vname(y[j}),-1,'_'),best.) then do;
     y{j}=1;leave;
   end;
  end;
 end;
drop i j;
run;

[LinusH]

Yet another example how to complicate things by storing data non normalized in wide format.
Transpose to long and most programming tasks will be much easier.