subtracting dates in hours by group and volumes

[monday89]

I have the following dataset. What I would like to do is that by ID, I would like to subtract order #2, etc from 1 as static by date_Time in hours and volumes

ID	date_time	volume	order
1234	1/2/2020 13:12	53.1	1
1234	1/2/2020 20:12	32.9	2
1234	1/3/2020 9:12	23.1	3
4522	3/2/2020 12:19	20.5	1
4522	3/2/2020 14:19	24.1	2

Something like this:

ID	date_time	volume	order	difference_hour	difference_volume
1234	1/2/2020 13:12	53.1	1
1234	1/2/2020 20:12	32.9	2	7	-20.2
1234	1/3/2020 9:12	23.1	3	13	-30
4522	3/2/2020 12:19	20.5	1
4522	3/2/2020 14:19	24.1	2	2	3.6

I was thinking to transpose the data and do one by one but it is rather very tedious

[ballardw]

Untested

data want;
   set have;
   by id;
   difference_hour = intck('hour',lag(date_time), date_time);
   difference_volume= dif(volume);
   if first.id then call missing(difference_hour, difference_volume);
run;

assumes you date_time values are actual SAS datetimes and that the data is sorted by ID. If the ID is not actually sorted use the NOTSORTED option on the BY statement.

Lag gets the value of a variable from a previous record, DIF does the difference of a variable with the previous record.

The BY and First.ID sets the calculated values to missing because they would be using values from the previous ID.

[biopharma]

You mentioned that you want to subtract from ORDER 1. In that case, the value of difference_hour for the last 1234 should be 20. The difference_volume seems to be as stated.

[biopharma]

Assuming that differences are calculated from ORDER = 1.

data have ;
   input ID date:mmddyy10. time:time5. volume order ;
   format date mmddyy10. time time5. datetime datetime22. ;
   datetime = dhms(date,0,0,time) ;
   cards ;
1234 1/2/2020 13:12 53.1 1
1234 1/2/2020 20:12 32.9 2
1234 1/3/2020 9:12 23.1 3
4522 3/2/2020 12:19 20.5 1
4522 3/2/2020 14:19 24.1 2
;
run ;

data want ;
   set have ;
   by id order ;
   retain _d _v ;
   drop _: ;
   if first.id then 
      do ;
         _v = volume ;
         _d = datetime ;
      end ;
   else do ;
      difference_hours = intck("hour",datetime,_d) ;
      difference_volume = volume - _v ;
   end ;
run ;

[ed_sas_member · Accepted solution]

Hi @monday89 

Here is another approach using PROC SQL:

data have ;
	input ID date:mmddyy10. time:time5. volume order;
	format date mmddyy10. time time5. datetime datetime22.;
	datetime = dhms(date,0,0,time);
	datalines ;
1234 1/2/2020 13:12 53.1 1
1234 1/2/2020 20:12 32.9 2
1234 1/3/2020 9:12 23.1 3
4522 3/2/2020 12:19 20.5 1
4522 3/2/2020 14:19 24.1 2
	;
run;

option missing="";
proc sql;
	create table want as
	select  a.*,
			case when intck("hour",b.datetime,a.datetime) ne 0 then intck("hour",b.datetime,a.datetime) else . end as difference_hour,
			case when (a.volume - b.volume) ne 0 then (a.volume - b.volume) else . end as difference_volume
	from have as a left join
		 (select * from have group by ID having order=min(order)) as b
		 on a.ID=b.ID
	order by id, order;
quit;