Concatenate

[Ronein]

Hello

I have a raw data table that each customer appreas in a few rows.

I want to create a new table that each customer apperars in 1 row only and create a  new field that have vector of values with comma delmiter between them.

For exmaple:

Data aaa;
Input ID Stop $;
Cards;
1 S1
1 S9
1 S15
1 S22
2 S3
2 S7
2 S12
;
Run;

Desired table

1  S1,S9,S15,S22

2  S3,S7,S12

[Kurt_Bremser · Accepted solution]

• use a data step with by id;
• retain a character variable and define it with sufficient length
• at first.id, set it to empty
• use catx(',',stops,stop) to concatenate
• at last.id, output

[Ronein]

May you send it please by code?

Thanks 

[Ronein]

Hello

Should I use proc sql with group by?

[FloT]

Hello,

Something like:

proc sort data=aaa;
    by ID;
run;

data bbb;
	length ID 8 concat $200;
	set aaa;
	retain concat;
	by ID;
	if first.ID then concat = "";
	concat = catx(',',Stop,concat);
	if last.ID then output;
	drop Stop;
run;

I hope this helps,

FloT

[Kurt_Bremser]

---

@Ronein wrote:

May you send it please by code?

Thanks 

---

I will gladly help you with your code, but if you want me to do your work for you, you need to hire me. Write code along my suggestions, and post it (along with the log) if you encounter problems.

[Ronein]

Thanks a lot

proc sort data=aaa;By ID Stop;Run;

data bbb;

set aaa;

retain Vect;

by ID Stop;

length concat $30.;

if first.ID  then Vect="";

Vect=catx('/',trim(Vect),trim(stop));

if last.ID then output;

run;

[Kurt_Bremser]

Three issues:

• the by statement only needs id
• set the length for Vect instead of concat
• catx does the trimming (actually stirpping) of variables on its own, so you don't need to do it

Since stop is not needed anymore in the output, you can drop it.

[novinosrin]

@Kurt_Bremser Good morning , That's so neat and textbook style. My mates at my college took those notes printed for their future reference. Just an acknowledgement for all of us. 

[Kurt_Bremser]

---

@novinosrin wrote:

@Kurt_Bremser Good morning , That's so neat and textbook style. My mates at my college took those notes printed for their future reference. Just an acknowledgement for all of us. 

---

I feel flattered.

[PeterClemmensen]

Do something like this

Data aaa;
Input ID Stop $;
Cards;
1 S1
1 S9
1 S15
1 S22
2 S3
2 S7
2 S12
;
Run;

data want;
	length ID 8 ConcStop $100;
	set aaa;
	by ID;
	if first.ID then ConcStop = "";
	ConcStop = catx(',',Stop, ConcStop);
	if last.ID;
    retain ConcStop;drop Stop;
run;

[rajiv436]

Hi Ronein,

Below code help you to get desired output.

data Desired;
length cat $50.;
do until (last.ID);
set aaa;
by ID notsorted;
cat=catx(',',cat,Stop);
end;
drop Stop;
run;