Hi,
I'm pretty new to working with longitudinal data and need to calculate the difference between two dates. Specifically, I want to subtract all date values from one column to the first value of another.
This is what my data looks like, and want to add a date_diff column that would subtract admit_date from discharge_date. All the admit dates for the first MRN would be subtracted from the discharge_date 1/26/2015.
MRN Admit_Date Discharge_Date
-13007 1/24/2015 1/26/2015
-13007 1/25/2015 1/26/2015
-13007 2/9/2015 2/9/2015
-13007 2/13/2015 2/14/2015
-13007 2/13/2015 2/14/2015
-13007 2/13/2015 2/15/2015
-13007 2/18/2015 2/18/2015
-13007 2/20/2015 2/20/2015
-13007 2/26/2015 2/26/2015
-13007 3/3/2015 3/5/2015
-13007 3/6/2015 3/7/2015
-13007 6/9/2015 6/9/2015
-13007 7/27/2018 7/27/2018
5422028 12/24/2010 12/26/2010
5672028 12/24/2010 12/26/2010
6952028 12/27/2010 12/29/2010
6952028 4/11/2015 4/11/2015
6952028 10/5/2015 10/5/2015
8752026 12/31/2016 1/1/2017
9562028 12/29/2010 1/3/2011
9562028 12/29/2010 1/3/2011
9772028 2/4/2014 2/9/2014
9772028 5/7/2014 5/8/2014
9772028 2/9/2017 2/9/2017
10002028 2/6/2011 2/14/2011
10002028 2/28/2011 3/1/2011
10002028 4/5/2013 4/5/2013
10002028 8/15/2014 8/19/2014
10722028 12/31/2010 1/8/2011
10722028 12/31/2010 1/8/2011
10722028 1/23/2011 1/23/2011
11022028 8/11/2018 8/12/2018
13582028 4/22/2011 4/23/2011
14202028 1/5/2011 1/10/2011
14202028 1/5/2011 1/10/2011
An example of what I want my data to look like with the added date_diff column.
MRN Admit_Date Discharge_Date date_diff
-13007 1/24/2015 1/26/2015 -2
-13007 1/25/2015 1/26/2015 -1
-13007 2/9/2015 2/9/2015 14
-13007 2/13/2015 2/14/2015 18
-13007 2/13/2015 2/14/2015 18
-13007 2/13/2015 2/15/2015 18
-13007 2/18/2015 2/18/2015 23
-13007 2/20/2015 2/20/2015 25
-13007 2/26/2015 2/26/2015 31
-13007 3/3/2015 3/5/2015 36
-13007 3/6/2015 3/7/2015 39
-13007 6/9/2015 6/9/2015 134
-13007 7/27/2018 7/27/2018 1278
5422028 12/24/2010 12/26/2010 -2
5672028 12/24/2010 12/26/2010 -2
6952028 12/27/2010 12/29/2010 -2
6952028 4/11/2015 4/11/2015 1564
6952028 10/5/2015 10/5/2015 1741
8752026 12/31/2016 1/1/2017 -1
9562028 12/29/2010 1/3/2011 -5
9562028 12/29/2010 1/3/2011 -5
9772028 2/4/2014 2/9/2014 -5
9772028 5/7/2014 5/8/2014 87
9772028 2/9/2017 2/9/2017 1096
10002028 2/6/2011 2/14/2011 -8
10002028 2/28/2011 3/1/2011 14
10002028 4/5/2013 4/5/2013 781
10002028 8/15/2014 8/19/2014 1278
10722028 12/31/2010 1/8/2011 -8
10722028 12/31/2010 1/8/2011 -8
10722028 1/23/2011 1/23/2011 15
11022028 8/11/2018 8/12/2018 2772
13582028 4/22/2011 4/23/2011 -1
14202028 1/5/2011 1/10/2011 -5
14202028 1/5/2011 1/10/2011 -5
Hopefully, this is enough information. Thank you!
data want;
set have;
retain fdischarge_Date;
by MRN;
if first.mrn then fdischarge_Date=discharge_Date;
date_diff=intck('days',fdischarge_Date,Admit_Date);
drop fdischarge_Date;
run;
data have;
input MRN (Admit_Date Discharge_Date) (:mmddyy10.);
format Admit_Date Discharge_Date mmddyy10.;
cards;
-13007 1/24/2015 1/26/2015
-13007 1/25/2015 1/26/2015
-13007 2/9/2015 2/9/2015
-13007 2/13/2015 2/14/2015
-13007 2/13/2015 2/14/2015
-13007 2/13/2015 2/15/2015
-13007 2/18/2015 2/18/2015
-13007 2/20/2015 2/20/2015
-13007 2/26/2015 2/26/2015
-13007 3/3/2015 3/5/2015
-13007 3/6/2015 3/7/2015
-13007 6/9/2015 6/9/2015
-13007 7/27/2018 7/27/2018
5422028 12/24/2010 12/26/2010
5672028 12/24/2010 12/26/2010
6952028 12/27/2010 12/29/2010
6952028 4/11/2015 4/11/2015
6952028 10/5/2015 10/5/2015
8752026 12/31/2016 1/1/2017
9562028 12/29/2010 1/3/2011
9562028 12/29/2010 1/3/2011
9772028 2/4/2014 2/9/2014
9772028 5/7/2014 5/8/2014
9772028 2/9/2017 2/9/2017
10002028 2/6/2011 2/14/2011
10002028 2/28/2011 3/1/2011
10002028 4/5/2013 4/5/2013
10002028 8/15/2014 8/19/2014
10722028 12/31/2010 1/8/2011
10722028 12/31/2010 1/8/2011
10722028 1/23/2011 1/23/2011
11022028 8/11/2018 8/12/2018
13582028 4/22/2011 4/23/2011
14202028 1/5/2011 1/10/2011
14202028 1/5/2011 1/10/2011
;
data want;
do until(last.mrn);
set have;
by mrn;
if first.mrn then _n_=Discharge_Date;
date_diff=Admit_Date-_n_;
output;
end;
run;
For those who may fancy SQL notwithstanding the automatic remerge, it's easy
proc sql;
create table want as
select *, Admit_Date-min( Discharge_Date) as date_diff
from have
group by mrn
order by mrn,Admit_Date, Discharge_Date;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.