DATA Step, Macro, Functions and more

Concatenate

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 91
Accepted Solution

Concatenate

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

 

 

 


Accepted Solutions
Solution
Tuesday
Super User
Posts: 9,548

Re: Concatenate

[ Edited ]
  • 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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
Tuesday
Super User
Posts: 9,548

Re: Concatenate

[ Edited ]
  • 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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 91

Re: Concatenate

Posted in reply to KurtBremser

May you send it please by code?

Thanks 

Frequent Contributor
Posts: 91

Re: Concatenate

Hello

Should I use proc sql with group by?

 

Occasional Contributor
Posts: 8

Re: Concatenate

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

Super User
Posts: 9,548

Re: Concatenate


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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 91

Re: Concatenate

Posted in reply to KurtBremser

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;

Super User
Posts: 9,548

Re: Concatenate

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 1,287

Re: Concatenate

Posted in reply to KurtBremser

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

Super User
Posts: 9,548

Re: Concatenate

Posted in reply to novinosrin

@novinosrin wrote:

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
PROC Star
Posts: 1,190

Re: Concatenate

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;
Occasional Contributor
Posts: 16

Re: Concatenate

Hi

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;

Thanks& Regards,
Rajiv Santosh.
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 151 views
  • 3 likes
  • 6 in conversation