Scan fields to link to other table and create new fields

podarum — Thu, 27 Oct 2016 20:59:25 GMT

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.

Re: Scan fields to link to other table and create new fields

Ksharp — Fri, 28 Oct 2016 06:05:15 GMT

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;

Re: Scan fields to link to other table and create new fields

LinusH — Fri, 28 Oct 2016 06:40:28 GMT

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.

topic Scan fields to link to other table and create new fields in SAS Programming

Scan fields to link to other table and create new fields

Re: Scan fields to link to other table and create new fields

Re: Scan fields to link to other table and create new fields