topic Re: Delimited String from Dataset in SAS Programming

Delimited String from Dataset

deleted_user — Wed, 22 Sep 2010 06:41:48 GMT

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

Re: Delimited String from Dataset

Patrick — Wed, 22 Sep 2010 09:57:37 GMT

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;

Re: Delimited String from Dataset

art297 — Wed, 22 Sep 2010 12:05:57 GMT

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

Re: Delimited String from Dataset

chang_y_chung_hotmail_com — Wed, 22 Sep 2010 14:18:41 GMT

...
> 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]

Re: Delimited String from Dataset

deleted_user — Wed, 22 Sep 2010 23:25:42 GMT

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