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 |
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;
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;
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.