Collapsing over unique ID and retaining start and stop dates

[Carmine_Rossi]

Good day SAS community forum users,

I have a SAS data step question that I am hoping you can help me with. It concerns collapsing over observations, for drug treatment data with regimen_id’s and prescription start and stop dates.

Suppose I have the following structure:

Patient_id	Regimen_ID	Start_date	Stop_date
1	1	A0	A1
1	1	B0	B1
1	1	C0	C1
1	2	A0	A1
1	2	B0	B1

And instead, I wanted to collapse start and stop dates over each combination of patient and regimen, so that I could generate the following (note the start date is the very first one for the regimen and the stop date is the very last for the regimen):

Patient_id	Regimen	Start_date	Stop_date
1	1	A0	C1
1	2	A0	B1

How would this be done in SAS? I am a bit stumped.

Thanks again for your help,

-Carmine

[art297 · Accepted solution]

The following should work:

data want (drop=_:);
  set have (rename=(Start_date=_Start_date
                    Stop_date=_Stop_date));
  retain Start_date;
  by Patient_id Regimen_ID;
  if first.Regimen_ID then Start_date=_Start_date;
  if last.Regimen_ID then do;
    Stop_date=_Stop_date;
    output;
  end;
run;

Art, CEO, AnalystFinder.com

[Carmine_Rossi]

Thank you for sharing this! It worked like a charm. This is greatly appreciated!

All the best,

-Carmine

[mkeintz]

This is a technique that uses the lag function to hold needed values rather than a temporary variable.  Much more compact in cases like this.

data have;
input Patient_id Regimen_ID Start_date :$2. Stop_date :$2.;
put (_all_) (=);
datalines;
1 1 A0 A1
1 1 B0 B1
1 1 C0 C1
1 2 A0 A1
1 3 A0 A1
1 3 B0 B1
run;
data want;
  set have;
  by patient_id regimen_id;

  if first.regimen_id or last.regimen_id;
  if first.regimen_id ^=last.regimen_id then start_date=lag(start_date);
  if last.regimen_id;
run;

Notes:

1. I changed your regimen_id=2 to a 3, and added a single-record regimen_id=2 in the middle, to demonstrate treatment of single-record date ranges.
   
   
2. The first subsetting if  (if first.regimen_id or last.regimen_id) tells SAS to throw away all records except the first and last for each group.
3. The "if first.regimen_id^=last.regimen_id ..." statement.  If the regimen_id has only a single record, then no updating is needed.
   
   But if there are separate first. and last. records, then the single-member lag queue is updated twice for that regimen_id.  Which in turn means that the result of the lag at the end of the group is the value that was put into the lag queue at the beginning of the group. Of course in this case the lag "queue" is a queue of size one.
4. The "if last.regimen_id" statement keeps one record per group -- the last record.

By the way, the single statement

   if first.regimen_id^=last.regimen_id then start_date=lag(start_date);

is NOT EQUIVALENT to the pair of statements

   if first.regimen_id>last.regimen_id then start_date=lag(start_date);

   if first.regimen_id<last.regimen_id then start_date=lag(start_date);

because the single statement maintains only one queue of start_date values, alternating between values of the first. and last. records.  In contrast, the two statements maintain two separate queues, one with the sequence of first. records, and the other with the sequence of last. records.