How to append data of column into new variable separated by comma.

[sasuser6001]

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

[ed_sas_member · Accepted solution]

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,

[ed_sas_member]

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;

[s_lassen]

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.

[PeterClemmensen]

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;

[novinosrin]

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;