Count days between followed up events

[Tamino]

Hello,

I have a dataset with claims of medication, each line / observation is one claim. I have the patient_id and the date of the claim. Now I created a variable, which counts up the several claims per patient between the years 2014 and 2020, beginning with 1 for the first claim, 2 for the second, 3 for the third... The maximum number viaries from patient to patient.

Now I want to create a new variable, which counts the number of days between one claim to the previous within each patient, meaning the number of days between the second to the first, the third to the second...

I would be very happy for your help, thank you in advance.

[ballardw · Accepted solution]

Example data helps. Such things as are your dates actually SAS date values (amazing how many people ask about manipulating "dates" that are actually character strings)? Is the data already sorted by patient_id and date?

Dummy code assuming the response to both questions above is yes:

data want;
   set have; 
   by patient_id;
   days = dif(datevariable);
   if first.patiend_id then days=.; /* or 0 if peferred*/
run;

When you use BY group processing in a data step SAS creates automatic variables that indicate whether a record is the first or last of a group, that are 1/0 for true/false, and referenced with the First. or Last. (pay attention to the dot in there).

The DIF function returns the difference of the current value of a variable with that of the previous record if used carefully. Since SAS date values are a number of days then DIF is the number of days between dates.

[Tamino]

Thank you, that worked perfectly as I wanted.
Sorry about the dates, they were actually SAS date values.

[mkeintz]

Do you ever have multiple claims with the same date from a single patient? 

If so, what rule do you want to establish for the new variable?  I can imagine a couple possibilities:

1. You would have some instances of days_between_events=0.  Or course this would be sensitive to reordering multiple claims for the same date.
2. Each record on a given date has days_between_event set to the number of days since the last earlier event?

[Tamino]

Thank you for your hint. I recognized that as I actually have some claims with the same date from a singe patient and the days_between_events=0 then. But as I want to exclude patients with less than 365d between their claims according to my research question, I ecluded them with a separate command (if claim_number>1 and days_between_events=0 then delete).