Matching or Cross-Referencing Multiple Accounts to One Transaction ID number

[chandler]

In the attached spreadsheet you will see that there are three columns (Loan Number, TransID, and Related). What I would like for SAS to be able to do is to link every Loan Number that has the same TransID and put them in the Related column with a comma in between each one. So that if Loans COMM01 and COMM02 had the same TransID, in the Related column it would read COMM01, COMM02 for both of those loans.

Loan Number	TransID	Related
COMM0001	560	COMM0001, COMM0002
COMM0002	560	COMM0001, COMM0002
COMM0003	1727	COMM0003, COMM0004
COMM0004	1727	COMM0003, COMM0004
COMM0005	3032	COMM0005, COMM0006
COMM0006	3032	COMM0005, COMM0006
COMM0007	3591	COMM0007, COMM0008, COMM0009, COMM0010
COMM0008	3591	COMM0007, COMM0008, COMM0009, COMM0010
COMM0009	3591	COMM0007, COMM0008, COMM0009, COMM0010
COMM0010	3591	COMM0007, COMM0008, COMM0009, COMM0010
COMM0011	3971	COMM0011, COMM0012, COMM0013, COMM0014, COMM0015
COMM0012	3971	COMM0011, COMM0012, COMM0013, COMM0014, COMM0015
COMM0013	3971	COMM0011, COMM0012, COMM0013, COMM0014, COMM0015
COMM0014	3971	COMM0011, COMM0012, COMM0013, COMM0014, COMM0015
COMM0015	3971	COMM0011, COMM0012, COMM0013, COMM0014, COMM0015

[Haikuo · Accepted solution]

DOW will do, you may need to sort your table if it is not sorted.

data have;

input Loan_Number :$ TransID     ;

cards;

COMM0001     560

COMM0002     560

COMM0003     1727

COMM0004     1727

COMM0005     3032

COMM0006     3032

COMM0007     3591

COMM0008     3591

COMM0009     3591

COMM0010     3591

COMM0011     3971

COMM0012     3971

COMM0013     3971

COMM0014     3971

COMM0015     3971

;

data want (drop=Loan_Number);

do until (last.transid);

set have;

by transid;

length related $100.;

related=catx(',',related,Loan_Number);

end;

run;

proc print;run;

Regards,

Haikuo