DATA Step, Macro, Functions and more

Scan fields to link to other table and create new fields

Accepted Solution Solved
Reply
Super Contributor
Posts: 401
Accepted Solution

Scan fields to link to other table and create new fields

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.


Accepted Solutions
Solution
‎10-28-2016 10:36 AM
Super User
Posts: 10,020

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

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;

View solution in original post


All Replies
Solution
‎10-28-2016 10:36 AM
Super User
Posts: 10,020

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

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;
Super User
Posts: 5,424

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

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.
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 204 views
  • 2 likes
  • 3 in conversation