topic Re: subtracting dates in hours by group and volumes in SAS Programming

subtracting dates in hours by group and volumes

monday89 — Fri, 29 May 2020 19:46:45 GMT

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

Re: subtracting dates in hours by group and volumes

ballardw — Fri, 29 May 2020 19:54:21 GMT

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.

Re: subtracting dates in hours by group and volumes

biopharma — Fri, 29 May 2020 20:29:21 GMT

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.

Re: subtracting dates in hours by group and volumes

biopharma — Fri, 29 May 2020 20:31:14 GMT

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 ;

Re: subtracting dates in hours by group and volumes

ed_sas_member — Sat, 30 May 2020 09:04:36 GMT

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;