SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasuser6001
Calcite | Level 5

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

Input Table: 

TypeCar
SedanVerna
SedanHondaCity
SedanAltis
SUVFortuner
SUVScorpio
HatchbackSantro
HatchbackMicra

 

Required Output:

TypeCar
SedanVerna, HondaCity, Altis
SUVFortuner,Scorpio
HatchbackSantro,Micra
1 ACCEPTED SOLUTION

Accepted Solutions
ed_sas_member
Meteorite | Level 14

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,

View solution in original post

5 REPLIES 5
ed_sas_member
Meteorite | Level 14

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
Meteorite | Level 14

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
Meteorite | Level 14

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
Tourmaline | Level 20
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
Tourmaline | Level 20
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;

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 976 views
  • 4 likes
  • 5 in conversation