Solved
Contributor
Posts: 30

converting mutliple observations to 1 record of distinct data

Hi I have data some thing like below

 account id 94345 42 94345 26 94345 18 94345 34 94345 42 94345 18

and i want something like this with distinct data in the id field after concatenating,Can any one help me with this !

 account new id 94345 42,26,18,34

Accepted Solutions
Solution
‎07-17-2017 10:03 AM
Posts: 1,837

Re: converting mutliple observations to 1 record of distinct data

``````proc sort data=have out=temp nodupkey;
by account id;
run;

data want;
set temp;
by account;
length new_id \$100;  /* addapt to max length needed */
retain new_id;
if first.account then new_id = ' ';
new_id = catx(',',new_id,id);
if last.account then output;
run;``````

All Replies
Solution
‎07-17-2017 10:03 AM
Posts: 1,837

Re: converting mutliple observations to 1 record of distinct data

``````proc sort data=have out=temp nodupkey;
by account id;
run;

data want;
set temp;
by account;
length new_id \$100;  /* addapt to max length needed */
retain new_id;
if first.account then new_id = ' ';
new_id = catx(',',new_id,id);
if last.account then output;
run;``````
Posts: 1,147

Re: converting mutliple observations to 1 record of distinct data

Alternatively

``````proc sort data=have out=temp nodupkey;
by account id;
run;

proc transpose data=have out=trans;
by account;
var id;
run;

data want;
set trans;
new_id=catx(',',of col:);
drop col: _name_;
run;``````
Thanks,
Jag
☑ This topic is solved.

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

Discussion stats
• 2 replies
• 161 views
• 0 likes
• 3 in conversation