combine observations by separator

[deleted_user]

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

[Cynthia_sas]

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

[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;