Use Earliest Date For Calculation

[jcapua2]

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!

[novinosrin · Accepted solution]

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;
else if PatientRe_Admitted='Yes' then DaysUntilGraftFailifReAdmitted=intck('days', temp, StatusDate);
drop temp;
run;

[jcapua2]

Thank you! It works!

[Reeza]

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;

[mkeintz]

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;
  if readmitted='Y' then days_to_failure_readmit=status-min_procdate;
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.