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

chandler — Fri, 23 Mar 2012 16:25:07 GMT

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

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

Haikuo — Fri, 23 Mar 2012 16:38:08 GMT

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

topic Matching or Cross-Referencing Multiple Accounts to One Transaction ID number in SAS Programming

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

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