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;