BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
Patrick
Opal | Level 21
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
Opal | Level 21
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
Obsidian | Level 7
...
> 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
Not applicable
Thanks guys, It appears i was closer then i originally thought.
All sorted!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 678 views
  • 0 likes
  • 4 in conversation