🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 05-20-2020 07:06 AM
(975 views)
How to append data of column into new variable separated by comma.
Input Table:
Type | Car |
Sedan | Verna |
Sedan | HondaCity |
Sedan | Altis |
SUV | Fortuner |
SUV | Scorpio |
Hatchback | Santro |
Hatchback | Micra |
Required Output:
Type | Car |
Sedan | Verna, HondaCity, Altis |
SUV | Fortuner,Scorpio |
Hatchback | Santro,Micra |
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi @sasuser6001
Please try this, using the catx() function and a RETAIN statement:
data have;
input Type:$20. Car:$20.;
datalines;
Sedan Verna
Sedan HondaCity
Sedan Altis
SUV Fortuner
SUV Scorpio
Hatchback Santro
Hatchback Micra
;
run;
proc sort data=have;
by type;
run;
data want;
set have;
by type;
length _car $200.;
retain _car;
if first.type then _car=car;
else _car = catx(",",_car,car);
if last.type then output;
drop car;
rename _car=Car;
run;
Best,
5 REPLIES 5
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Hi @sasuser6001
Please try this, using the catx() function and a RETAIN statement:
data have;
input Type:$20. Car:$20.;
datalines;
Sedan Verna
Sedan HondaCity
Sedan Altis
SUV Fortuner
SUV Scorpio
Hatchback Santro
Hatchback Micra
;
run;
proc sort data=have;
by type;
run;
data want;
set have;
by type;
length _car $200.;
retain _car;
if first.type then _car=car;
else _car = catx(",",_car,car);
if last.type then output;
drop car;
rename _car=Car;
run;
Best,
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here is another option, using the PROC TRANSPOSE procedure and the CATX() function:
data have;
input Type:$20. Car:$20.;
datalines;
Sedan Verna
Sedan HondaCity
Sedan Altis
SUV Fortuner
SUV Scorpio
Hatchback Santro
Hatchback Micra
;
run;
proc sort data=have;
by type;
run;
proc transpose data=have out=have_tr (drop=_:);
var car;
by type;
run;
data want;
set have_tr;
car = catx(",",of col:);
drop col:;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You can try something like this:
data want;
do until(last.Type);
set have;
by Type notsorted;
length _car $200;
call catx(', ',_car,car);
end;
drop Car;
rename _car=Car;
run;
I used the NOTSORTED option on the BY statement, as your data appears to be grouped by Type, but not sorted. Alternatively, you should sort the data first.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input Type $ 1-10 Car $ 12-22;
datalines;
Sedan Verna
Sedan HondaCity
Sedan Altis
SUV Fortuner
SUV Scorpio
Hatchback Santro
Hatchback Micra
;
data want(drop=_:);
length Car $ 100;
do until (last.type);
set have(rename=Car=_Car);
by type notsorted;
Car = catx(', ', Car, _Car);
end;
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
data have;
input Type:$20. Car:$20.;
datalines;
Sedan Verna
Sedan HondaCity
Sedan Altis
SUV Fortuner
SUV Scorpio
Hatchback Santro
Hatchback Micra
;
run;
data want;
do _n_=1 by 1 until(last.type);
set have;
by type notsorted;
array t(999) $32 _temporary_;
t(_n_)=car;
end;
length cars $100;
cars=catx(',',of t(*));
call missing(of t(*));
drop car;
run;