DATA Step, Macro, Functions and more

Delimited String from Dataset

Reply
N/A
Posts: 0

Delimited String from Dataset

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
Respected Advisor
Posts: 4,173

Re: Delimited String from Dataset

Posted in reply to deleted_user
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;
PROC Star
Posts: 7,492

Re: Delimited String from Dataset

Posted in reply to deleted_user
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
Regular Contributor
Posts: 241

Re: Delimited String from Dataset

Posted in reply to deleted_user
...
> 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 colSmiley Happy;
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]
N/A
Posts: 0

Re: Delimited String from Dataset

Posted in reply to deleted_user
Thanks guys, It appears i was closer then i originally thought.
All sorted!
Ask a Question
Discussion stats
  • 4 replies
  • 140 views
  • 0 likes
  • 4 in conversation