topic Calculate time by comparing conditions and dates vertically in New SAS User

Calculate time by comparing conditions and dates vertically

NewUsrStat — Mon, 08 Jun 2026 15:53:08 GMT

Hi guys,

suppose to have the following dataset:

data DB;
  input ID :$200. Event:$200. Date :date09.;
    format Date date9.;
cards;
0001 CR    01SEP2024
0001 PR    10OCT2024
0001 CR    15DEC2024
0001 PD    22MAR2025
0001 CR    08AUG2025
0001 PD    20OCT2025
0001 Death 02DEC2025

;

Here, for simplicity only one patient is shown but the dataset is made of around 100 patients. This patient I'm showing, resumes all the possible cases. As you can see it has different responses to treatment: the complete response, the partial response, the progressive disease and the death. I have to calculate the difference (in months and I know how to do it) between the date of the first occurring PD and the date of the first occurring event other than PD and death. In this case it should be: 22MAR2025-01SEP2024. In principle I know how to compare values but my difficulty is that the comparison should be done vertically and only vertically.

Can anyone help me please?

Re: Calculate time by comparing conditions and dates vertically

Quentin — Mon, 08 Jun 2026 17:17:24 GMT

I can see from some of your other questions that you're familiar with the RETAIN statement. So in this case, couldn't you calculate start date for each ID, retain it, and then when you come to the first PD record, calculate the difference in dates? Or if you like the double DOW loop approach, that could be used here as well.

Those are just hints. Can you show the code you've tried, and describe what went wrong?

Re: Calculate time by comparing conditions and dates vertically

PaigeMiller — Mon, 08 Jun 2026 21:27:32 GMT

@Quentin is on the right track. Use the RETAIN statement. Give it a try, show us what you have tried.

Also, it would be extremely helpful if you can show us the desired output for this particular patient. (And hint hint hint, you should ALWAYS show us the desired output in these complex situations, we shouldn't have to ask.)