DATA Step, Macro, Functions and more

Concatenate

Accepted Solution Solved
Reply
Regular Contributor
Posts: 199
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
‎05-15-2018 03:38 AM
Super User
Posts: 10,574

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
‎05-15-2018 03:38 AM
Super User
Posts: 10,574

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
Regular Contributor
Posts: 199

Re: Concatenate

Posted in reply to KurtBremser

May you send it please by code?

Thanks 

Regular Contributor
Posts: 199

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: 10,574

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
Regular Contributor
Posts: 199

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: 10,574

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
Super User
Posts: 2,061

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: 10,574

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,400

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
  • 230 views
  • 3 likes
  • 6 in conversation