DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

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

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 NumberTransIDRelated
COMM0001560COMM0001, COMM0002
COMM0002560COMM0001, COMM0002
COMM00031727COMM0003, COMM0004
COMM00041727COMM0003, COMM0004
COMM00053032COMM0005, COMM0006
COMM00063032COMM0005, COMM0006
COMM00073591COMM0007, COMM0008, COMM0009, COMM0010
COMM00083591COMM0007, COMM0008, COMM0009, COMM0010
COMM00093591COMM0007, COMM0008, COMM0009, COMM0010
COMM00103591COMM0007, COMM0008, COMM0009, COMM0010
COMM00113971COMM0011, COMM0012, COMM0013, COMM0014, COMM0015
COMM00123971COMM0011, COMM0012, COMM0013, COMM0014, COMM0015
COMM00133971COMM0011, COMM0012, COMM0013, COMM0014, COMM0015
COMM00143971COMM0011, COMM0012, COMM0013, COMM0014, COMM0015
COMM00153971COMM0011, COMM0012, COMM0013, COMM0014, COMM0015

Accepted Solutions
Solution
‎03-23-2012 12:38 PM
Respected Advisor
Posts: 3,124

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

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

View solution in original post


All Replies
Solution
‎03-23-2012 12:38 PM
Respected Advisor
Posts: 3,124

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 207 views
  • 1 like
  • 2 in conversation