Delimited String from Dataset

[deleted_user]

I for the life of me can't figure this out..
and i know i'm missing something simple (surely).

I've got a dataset along the lines of:

Patient_ID Result
123 Z100
123 A100
124 B100
124 Z100
127 H119
127 J112
127 Z100

What i want to be able to do is create a data set that contains the following:

Patient_ID Result
123 Z100,A100
124 B100,Z100
127 H119,J112,Z100

I was attempting to use a Data Step but couldn't appear to reference the previously used variable.

If it was a numeric value and i was calculating a total then it seems to work, but trying to concatenate strings is making things more difficult.

I guess i could transpose the dataset and then concat the resulting columns, but that seems like an overly complicated method

[Patrick]

HTH
Patrick

data want(drop=Patient_ID);
input Patient_ID Result $;
datalines;
123 Z100
123 A100
124 B100
124 Z100
127 H119
127 J112
127 Z100
;
run;

data want;
set have;
by Patient_ID;
retain result_arr;
length result_arr $ 1000;

if first.patient_id then
result_arr=result;
else
result_arr=cats(result_arr,',',result);

if last.patient_id then output;
run;

proc print data=want;
run;

[art297]

I agree with Patrick's approach but, as many of us have at some time, he apparently responded before drinking enough coffee!

In his example, make sure that the first file is called have, not want and don't drop Patient_ID.

Plus, I don't know if IFC is any more efficient than a combination of if then statements, but I prefer it. E.g.,

data have;
input Patient_ID Result $;
datalines;
123 Z100
123 A100
124 B100
124 Z100
127 H119
127 J112
127 Z100
;
run;

data want;
set have;
by Patient_ID;
retain result_arr;
length result_arr $ 1000;
result_arr=ifc( first.patient_id,result,
cats(result_arr,',',result));
if last.patient_id then output;
run;

HTH,
Art

[chang_y_chung_hotmail_com]

...
> I guess i could transpose the dataset and then concat
> the resulting columns, but that seems like an overly
> complicated method
...
*Overly* complicated? Is it? 🙂
[pre]
/* test data */
data one;
input id code $;
datalines;
123 Z100
123 A100
124 B100
124 Z100
127 H119
127 J112
127 Z100
;
run;

/* one obs per id, codes delimited with a comma and space */
proc transpose data=one out=two;
var code;
by id;
run;
data three;
set two;
length codes $200; /* set long enough length */
codes = catx(", ", of col:);
keep id codes;
run;

/* check */
proc print data=three;
run;
/* on lst
Obs id codes
1 123 Z100, A100
2 124 B100, Z100
3 127 H119, J112, Z100
*/
[/pre]

[deleted_user]

Thanks guys, It appears i was closer then i originally thought.
All sorted!