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

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
1 ACCEPTED SOLUTION

Accepted Solutions
Haikuo
Onyx | Level 15

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

1 REPLY 1
Haikuo
Onyx | Level 15

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

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
  • 1 reply
  • 804 views
  • 1 like
  • 2 in conversation