topic Re: Combine records in the same dataset. in SAS Studio

Combine records in the same dataset.

Wken1122 — Fri, 29 Sep 2017 02:50:48 GMT

Hi, I am looking for a way to combine 2 records in a single dataset.
These 2 records have duplicate IDs but i can't figure out how to make them into a single row.

I am hoping to make them into something like this;

POLICYID RIDER_LISTS
1 I100786-4599318572 RTWMR4,RTWWP1,RTWMR2,RTWHB1

Thanks in advance!

Re: Combine records in the same dataset.

mkeintz — Fri, 29 Sep 2017 03:35:51 GMT

By what rule do you know they are from the same id?

Does the number of records per policy vary? Or is it always 2?

Re: Combine records in the same dataset.

PGStats — Fri, 29 Sep 2017 03:41:44 GMT

Here is an example

data have;
input id $ list $;
datalines;
123 ab,cd
123 ef,ghj
012 abcd
;

data want;
length newList $200; /* Make new list large enough to hold the longer lists */
retain newList;
set have;
by id notsorted;
if first.id then newList = list;
else newList = catx(",", newList, list);
if last.id then output;
rename newList=list;
drop list;
run;

Re: Combine records in the same dataset.

Wken1122 — Fri, 29 Sep 2017 03:48:50 GMT

From the excel tables. But unsure why it became null..