BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
podarum
Quartz | Level 8

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

2 REPLIES 2
Ksharp
Super User
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
Tourmaline | Level 20
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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 webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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