DATA Step, Macro, Functions and more

Collapsing over unique ID and retaining start and stop dates

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Collapsing over unique ID and retaining start and stop dates

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


Accepted Solutions
Solution
‎12-05-2017 08:39 PM
Super User
Posts: 8,213

Re: Collapsing over unique ID and retaining start and stop dates

Posted in reply to Carmine_Rossi

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

 

View solution in original post


All Replies
Solution
‎12-05-2017 08:39 PM
Super User
Posts: 8,213

Re: Collapsing over unique ID and retaining start and stop dates

Posted in reply to Carmine_Rossi

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

 

Occasional Contributor
Posts: 10

Re: Collapsing over unique ID and retaining start and stop dates

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

All the best,

-Carmine

Trusted Advisor
Posts: 1,387

Re: Collapsing over unique ID and retaining start and stop dates

Posted in reply to Carmine_Rossi

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 170 views
  • 0 likes
  • 3 in conversation