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.
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.