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
May you send it please by code?
Thanks
Hello
Should I use proc sql with group by?
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
@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.
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;
Three issues:
Since stop is not needed anymore in the output, you can drop it.
@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.
@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.
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;
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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.