topic combine observations by separator in SAS Procedures

combine observations by separator

deleted_user — Tue, 27 Jan 2009 15:59:48 GMT

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

Re: combine observations by separator

Cynthia_sas — Tue, 27 Jan 2009 17:00:23 GMT

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

Re: combine observations by separator

deleted_user — Mon, 02 Feb 2009 06:06:33 GMT

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;