BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ronein
Meteorite | Level 14

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User
  • 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

View solution in original post

11 REPLIES 11
Kurt_Bremser
Super User
  • 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
Meteorite | Level 14

May you send it please by code?

Thanks 

Ronein
Meteorite | Level 14

Hello

Should I use proc sql with group by?

 

FloT
Fluorite | Level 6

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
Super User

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

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
Super User

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

@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
Super User

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

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
Fluorite | Level 6

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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