Matching observations by ID and date

[SarahW13]

Hello all,

Can someone guide me how to match different rows of data by ID and date? Below is an example.

This is what I have:

ID          Med            Date

1            A                 1/10/10

1            B                 1/10/10

1            C                 3/10/10

2            B                 4/15/15

2            C                 4/15/15

This is what I want:

ID          Med            Date

1            A+B            1/10/10

1            C                 3/10/10

2            B+C            4/15/15

So since ID #1 received both A and B on the same date, I want to consolidate that into one row. And similarly since ID # 2 received B and C on the same date, trying to consolidate that into one row.

Any tips?

[japelin]

Hi, SarahW13

How about this code?

data sample;
  infile datalines dlm=' ';
  length id 8 med $10 date $10;/* assign med length as necessary */
  input id med $ date $;
  key=catx('-',id,date);
datalines;
1 A 1/10/10
1 B 1/10/10
1 C 3/10/10
2 B 4/15/15
2 C 4/15/15
;
run;

proc sort data=sample;
  by key;
run;
data output;
  length med2 $10;/* assign med2 length as necessary */
  set sample;
  by key;
  retain med2;
  if first.key then med2='';
  else med=catx('+',med2,med); 
  med2=med;
  if last.key;
  drop med2 key;
run;

[novinosrin]

data have;
input ID          Med $           Date :$10.;
cards;
1            A                 1/10/10
1            B                 1/10/10
1            C                 3/10/10
2            B                 4/15/15
2            C                 4/15/15
;

data want;
 do until(last.date);
  set have;
  by id date;
  length _med $20;
  _med=catx('+',_med,med);
 end;
 drop med;
run;

[BrianB4233 · Accepted solution]

*Sort by ID and Date;

proc sort data = have;

by ID Date;

run;

* Use RETAIN to accumulate Med by ID and Date;

data want;

length med_add $6;

set have;

by ID Date

retain med_add;

/* Take care of records that only occur once, e.g., obs # 3 */

if first.Date & last.Date then med_add = Med;

/* Else if not the 1st & last date for that ID, RETAIN 1st value of 'Med' */

else if first.date then med_add = med;

/* Use CATX (concatenate) to add to the RETAINed variable */

else do;

 if med_add ^= med then med_add = catx(' + ',med_add,med);

end;

/* When last.Date is equal to '1', then output */

if last.date;

run;

if