topic Re: Longitudinal date manipulation (subtracting columns) in SAS Programming

Longitudinal date manipulation (subtracting columns)

Shad — Mon, 18 May 2020 00:31:02 GMT

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!

Re: Longitudinal date manipulation (subtracting columns)

r_behata — Mon, 18 May 2020 01:01:04 GMT

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;

Re: Longitudinal date manipulation (subtracting columns)

novinosrin — Mon, 18 May 2020 01:03:38 GMT


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;

Re: Longitudinal date manipulation (subtracting columns)

novinosrin — Mon, 18 May 2020 01:14:01 GMT

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;