Calculating difference in dates using the same date variable

[jcapua2]

Hello,

I want to calculate the difference between dates in days using the same variable date [Procedure_Date] in the same patient, among only those with the same Status=Alive.

data have;

patient_num	Procedure_Date	Status_Date	Status
patient33	03/07/2002	02/23/2004	Alive
patient33	02/23/2004	10/01/2014	Alive
patient50	05/07/2007	12/02/2014	Alive
patient50	05/27/2009	10/08/2015	Dead

data want;

patient_num	Procedure_Date	Status_Date	Status	Days_Between_Procedure
patient33	03/07/2002	02/23/2004	Alive	718
patient33	02/23/2004	10/01/2014	Alive	718
patient50	05/07/2007	12/02/2014	Alive	.
patient50	05/27/2009	10/08/2015	Dead	.

So for example, the difference in days of patient33's first Procedure_Date and second Procedure_Date, which equals to 718 days (same Status="Alive") and appears in the both rows.

Is there a way to do it in SAS datastep or in SQL?

Thank you!

[ballardw]

It appears that you only want the first value calculated and then retained. If your data is sorted by patient_num and procedure_date and your procedure_date and status_date variables are SAS date valued variables

data want;
    set have;
    by patient_num;
    retain  Days_between_procedure;
    if first.patient_num then Days_between_procedure= Status_date-Procedure_date;
run; 

If your dates are character variables then you will have to convert them to SAS date values

if first.patient_num then Days_between_procedure=  input(Status_date,mmddyy10.)- input(Procedure_date,mmddyy10.);

[Astounding]

Putting the same "days between procedures" on every observation is probably a mistake.  What values go where, if the patient actually had 3 procedures instead of 2?

Here's an easy way to put the "days between procedures" on the current observation only, as long as the patient is still alive.

proc sort data=have;

  by patient_num procedure_date;

run;

data want;

set have;

by patient_num procedure_date;

days_between_procedure = dif(procedure_date);

if first.patient_num or status ne 'Alive' then days_between_procedure = .;

run;

You may want to inspect the results to see if they seem like they would be easy enough to work with.  There are other ways, if you really need them.

[novinosrin]

data have;
input patient_num : $10. (Procedure_Date Status_Date) (:mmddyy10.) Status $;
format Procedure_Date Status_Date mmddyy10.;
datalines;
patient33 03/07/2002 02/23/2004 Alive
patient33 02/23/2004 10/01/2014 Alive
patient50 05/07/2007 12/02/2014 Alive
patient50 05/27/2009 10/08/2015 Dead
;

data want;
call missing(_flag);
do _n_=1 by 1 until(last.patient_num);
set have;
by patient_num Procedure_Date;
if _n_=2 then _temp=Procedure_Date;
if status='Dead' then _flag=0;
end;
do _n_=1 by 1 until(last.patient_num);
set have;
by patient_num Procedure_Date;
if _n_=1 and missing(_flag) then Days_Between_Procedure=intck('days',Procedure_Date, _temp);
output;
end;
drop _:;
run;