topic Re: Concatenate in SAS Programming

Concatenate

Ronein — Tue, 15 May 2018 06:35:58 GMT

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

Re: Concatenate

Kurt_Bremser — Tue, 15 May 2018 07:02:05 GMT

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

Re: Concatenate

Ronein — Tue, 15 May 2018 07:03:07 GMT

May you send it please by code?

Thanks

Re: Concatenate

Ronein — Tue, 15 May 2018 07:19:19 GMT

Hello

Should I use proc sql with group by?

Re: Concatenate

FloT — Tue, 15 May 2018 07:24:53 GMT

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

Re: Concatenate

PeterClemmensen — Tue, 15 May 2018 07:29:47 GMT

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;

Re: Concatenate

Kurt_Bremser — Tue, 15 May 2018 07:32:28 GMT

@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.

Re: Concatenate

rajiv436 — Tue, 15 May 2018 07:37:11 GMT

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;

Re: Concatenate

Ronein — Tue, 15 May 2018 07:40:34 GMT

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;

Re: Concatenate

Kurt_Bremser — Tue, 15 May 2018 07:49:01 GMT

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.

Re: Concatenate

novinosrin — Tue, 15 May 2018 14:23:36 GMT

@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.

Re: Concatenate

Kurt_Bremser — Tue, 15 May 2018 14:36:23 GMT

@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.