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
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;
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.
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 ;
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: