## Use Earliest Date For Calculation

Solved
Highlighted
Occasional Contributor
Posts: 11

# Use Earliest Date For Calculation

Hello,

data have;

 patient_num Procedure Date Patient Re-Admitted Status Date Days Until Graft Failure patient32 03/07/2002 No 02/23/2004 718 patient32 02/23/2004 Yes 10/01/2014 3873

data want;

 patient_num Procedure Date Patient Re-Admitted Status Date Days Until Graft Failure Days Until Graft Failure if Re-Admitted patient32 03/07/2002 No 02/23/2004 718 . patient32 02/23/2004 Yes 10/01/2014 3873 4591

For the variable "Days Until Graft Failure if Re-Admitted," I want to calculate the days using the earliest procedure date instead of the procedure date corresponding to that row for anyone who was "Patient Re-Admitted"='Yes'.

Is there way to calculate this in SAS datastep?

Thank you!

Accepted Solutions
Solution
‎01-02-2018 02:49 PM
PROC Star
Posts: 1,317

## Re: Use Earliest Date For Calculation

[ Edited ]

Do you want something like this:

data have;
input patient_num : \$10. Procedure_date :mmddyy10. PatientRe_Admitted \$ StatusDate :mmddyy10. DaysUntilGraftFailure;
format Procedure_date StatusDate mmddyy10.;
datalines;
patient32 03/07/2002 No 02/23/2004 718
patient32 02/23/2004 Yes 10/01/2014 3873
;
data want;
set have;
by patient_num;
retain temp;
if first.patient_num then temp=Procedure_date;
drop temp;
run;

All Replies
Solution
‎01-02-2018 02:49 PM
PROC Star
Posts: 1,317

## Re: Use Earliest Date For Calculation

[ Edited ]

Do you want something like this:

data have;
input patient_num : \$10. Procedure_date :mmddyy10. PatientRe_Admitted \$ StatusDate :mmddyy10. DaysUntilGraftFailure;
format Procedure_date StatusDate mmddyy10.;
datalines;
patient32 03/07/2002 No 02/23/2004 718
patient32 02/23/2004 Yes 10/01/2014 3873
;
data want;
set have;
by patient_num;
retain temp;
if first.patient_num then temp=Procedure_date;
drop temp;
run;

Occasional Contributor
Posts: 11

## Re: Use Earliest Date For Calculation

Thank you! It works!

Super User
Posts: 22,845

## Re: Use Earliest Date For Calculation

Sort your data so that the earliest procedure date is first. Then you can use RETAIN and first to keep it and use it for further calculations.

``````proc sort data=have;
by patient_num procedure_date;
run;

data want;
set have;
retain first_procedure_date;

if first.patient_num then first_procedure_date = procedure_date;

***do calculations with first procedure date here****;

run;
``````
Posts: 1,284

## Re: Use Earliest Date For Calculation

If the data are sorted only by id, and sorting by procdate within id is expensive, then this is a good case to use auto-interleaving,  i.e. interleaving data set HAVE with itself, on an ID by ID basis.  It leaves the data in original order:

``````data want;
set have (in=firstpass)
have (in=secondpass);
by id;

retain min_procdate;
if first.id then min_procdate=procdate;
else if firstpass then min_procdate=min(min_procdate,procdate);

if secondpass;
run;
``````

For each id, all the records are read for a first pass, calculating the minimum procdate.  Then keep only records from the second pass ("if secondpass;") for the same id, which is when the days_to_failure variable is calculated.

Auto-interleaving is also frequently done by use of two "do until (last.id)" loops, commonly called "double dow".  But this code is simpler, in that no do groups need be constructed, nor do you need an explicit OUTPUT statement in the second do group.

☑ This topic is solved.