how to substract two events from different date for multiple records

dnbhatta — Fri, 16 Aug 2024 23:58:44 GMT

I would like to subtract the second IncarcerationDate minus the first ReleaseDate based on ID. For example there is an ID with the number 1 with eight different start dates and end dates, Id 2 has one record, id 3 has four records. I would like a new variable "DaysAtHome", substract first.id ReleaseDate from second.id IncarcerationDate and for last record (last.id) susbtract from SysDate(Today's Date) -ReleaseDate. Any help would be greatly appreciated. Thanks.

Here is a dataset:

data incarceration;
input Id $
IncarcerationDate :mmddyy10.
ReleaseDate :mmddyy10.;
format IncarcerationDate ReleaseDate mmddyy10.;
datalines;
1 08/12/2019 08/13/2019
1 08/20/2019 10/02/2019
1 10/31/2019 11/01/2019
1 11/07/2019 11/08/2019
1 11/29/2019 01/02/2020
1 01/15/2020 03/25/2020
1 08/05/2020 08/08/2020
1 10/21/2020 01/07/2021
2 01/21/2018 01/23/2018
3 11/07/2018 01/07/2019
3 04/25/2018 05/09/2018
3 08/14/2020 08/17/2020
3 03/22/2018 04/11/2018
;
run;

I want a new Variable "DaysAtHome":

Id	IncarcerationDate	ReleaseDate	DaysAtHome
1	8/12/2019	8/13/2019	7	8/20/2019 - 8/13/2019
1	8/20/2019	10/2/2019	29	10/31/2019 - 10/02/2019
1	10/31/2019	11/1/2019	6
1	11/7/2019	11/8/2019	21
1	11/29/2019	1/2/2020	13
1	1/15/2020	3/25/2020	133
1	8/5/2020	8/8/2020	74
1	10/21/2020	1/7/2021	1317	today's date -01/07/2021
2	1/21/2018	1/23/2018	2397	today's date -01/23/2018
3	3/22/2018	4/11/2018	14
3	4/25/2018	5/9/2018	182
3	11/7/2018	1/7/2019	585
3	8/14/2020	8/17/2020	1460

Wrote few codes but did not get the results.

proc sort data=incarceration;
by Id IncarcerationDate;

run;

data days_home;
set incarceration;
by Id;

SysDate = today();

prevReleaseDate=lag(ReleaseDate);
if not last.id then DaysAtHome=IncarcerationDate-prevReleaseDate;
if last.Id then DaysAtHome = SysDate - ReleaseDate;
run;

Re: how to substract two events from different date for multiple records

Kurt_Bremser — Sat, 17 Aug 2024 06:10:57 GMT

Two ways:

sort the dataset in reverse chronological order, so you can use LAG, then re-sort to the original order
or do a look-ahead:

data days_home;
merge
  incarceration
  incarceration (
    firstobs=2
    keep=id incarcerationdate
    rename=(
      id=_id
      incarcerationdate=_inc
    )
  )
;
if id = _id
then daysathome = _inc - releasedate;
else daysathome = today() - releasedate;
drop _id _inc;
run;

Re: how to substract two events from different date for multiple records

dnbhatta — Sat, 17 Aug 2024 18:15:35 GMT

Thank you so much, I appreciate.

topic Re: how to substract two events from different date for multiple records in New SAS User

how to substract two events from different date for multiple records

Re: how to substract two events from different date for multiple records

Re: how to substract two events from different date for multiple records