converting mutliple observations to 1 record of distinct data

Accepted Solution Solved
Reply
Contributor
Posts: 20
Accepted Solution

converting mutliple observations to 1 record of distinct data

Hi I have data some thing like below

 

 

accountid
9434542
9434526
9434518
9434534
9434542
9434518

 

 

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

 

accountnew id
9434542,26,18,34

 

 


Accepted Solutions
Solution
‎07-17-2017 10:03 AM
Trusted Advisor
Posts: 1,570

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;

View solution in original post


All Replies
Solution
‎07-17-2017 10:03 AM
Trusted Advisor
Posts: 1,570

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;
Trusted Advisor
Posts: 1,137

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
  • 142 views
  • 0 likes
  • 3 in conversation