Help using Base SAS procedures

combine observations by separator

Reply
Not applicable
Posts: 0

combine observations by separator

Hi, Below is the input dataset1 which needs to be transformed to dataset2.
Is there a simpler way to do this, please comment.

dataset1
======
KEY VALUE
100 VALUE1,VALUE1A
100 VALUE2,VALUE2A
100 VALUE3,VALUE3A
200 VALUE11,VALUE1B
200 VALUE12,VALUE2B
300 VALUE23,VALUE1C

dataset2
======
KEY NEW_VALUE
100 VALUE1,VALUE1A,VALUE2,VALUE2A,VALUE3,VALUE3A
200 VALUE11,VALUE1B,VALUE12,VALUE2B
300 VALUE23,VALUE1C
SAS Super FREQ
Posts: 9,368

Re: combine observations by separator

Posted in reply to deleted_user
Hi:
There are some very good PROC TRANSPOSE examples in the documentation -- they wouldn't get you to this output immediately (initially, you'd have COL1, COL2, COL3, COL4, etc, etc instead of NEW_VALUE), but then you could use an ARRAY to create the new variable. Or you could just use BY group processing with the RETAIN statement to construct dataset2 from dataset1.

cynthia
Not applicable
Posts: 0

Re: combine observations by separator

Posted in reply to deleted_user
Hi Kumar,

Please try this code.It will be giving exact output.

data one;
input KEY VALUE$ 15.;
cards;
100 VALUE1,VALUE1A
100 VALUE2,VALUE2A
100 VALUE3,VALUE3A
200 VALUE11,VALUE1B
200 VALUE12,VALUE2B
300 VALUE23,VALUE1C
;
run;
proc transpose data=one out=two;
var value;
by key;
run;

data three(drop=_Name_ col1 col2 col3);
set two;
new_column=col1||","||col2||","||col3;
run;
Ask a Question
Discussion stats
  • 2 replies
  • 118 views
  • 0 likes
  • 2 in conversation