Hi,
I have a date set with datapoints based on one point per minute:
Start End
11OCT18:08:31:40 11OCT18:08:32:40
11OCT18:08:32:40 11OCT18:08:33:40
11OCT18:08:33:40 11OCT18:08:34:40
and a direct observation of an animals behaviour within one of these time vindows.
Date | Time |
11-10-2018 | 08:35:25 |
11-10-2018 | 08:40:04 |
11-10-2018 | 08:45:27 |
How do I merge these two dataset based on time? The datapoint from the direct observations need to be pared with the right timespan from the first data set.
It is not a problem for me to work with the time and data data, either to split ithem up or combine them. Just need help on how to merge the data by datetime.
Kind regards,
Per
please try this code
the cow_id will be missing in the want dataset since the cow_id date range does not match with the start and end date.
data have;
input maindate cow_id state $ zone$;
format maindate datetime18.;
cards;
1539246925 6300 Standing Feeding
1539247204 6300 Eating Feeding
1539247527 6300 Eating Feeding
1539247812 6300 Eating Feeding
;
data have2;
input Start: datetime16. End : datetime16.;
format start datetime18. end datetime18.;
cards;
11OCT18:08:31:40 11OCT18:08:32:40
11OCT18:08:32:40 11OCT18:08:33:40
11OCT18:08:33:40 11OCT18:08:34:40
;
proc sql;
create table want as select a.*, b.start, b.end from have2 as b left join have as a on b.start<=a.maindate<=b.end;
quit;
Ok so your data looks like this, correct? Note that I have created a DateTime variable with the DHMS Function in the second data set.
I'm not sure what you want the final data to look like. Can you post an example from the example data? The way I see it, none of the time values from have2 fall between any of the time spans in have1?
data have1;
input (Start End)(:datetime20.);
format Start End datetime20.;
datalines;
11OCT18:08:31:40 11OCT18:08:32:40
11OCT18:08:32:40 11OCT18:08:33:40
11OCT18:08:33:40 11OCT18:08:34:40
;
data have2;
input Date:ddmmyy10. Time:time8.;
format Date ddmmyy10. Time time8. DateTime datetime20.;
DateTime=dhms(Date, 0,0,Time);
datalines;
11-10-2018 08:35:25
11-10-2018 08:40:04
11-10-2018 08:45:27
;.
No sorry, these wa just copy and paste from two different dataset 🙂
What I am looking for is that the Have1 dataset, which also have a varible called 'behaviour', which is the behaviour recorded during the time span, can be merged with the have2 and the behaviour recorded on a specific time in the have2 is merged to the right time period in have1.
So if we in have2 have a datapoint with a corresponding recorded behaviour/variable on: 11oct18:08:31:57 then this datapoint is merged into have1 on the same line as 11oct18:08:31:40 11oct18:08:32:40
Ah ok 🙂 Shouldn't be too complicated. Last question. Do both data sets have a behaviour variable? And should both behaviour varaibles be in your desired data set, provided the time span fits?
I think this gives you what you want. It is not the most efficient, but if you do not have too many records, it will do.
Let me know if it meets your needs 🙂
data have1;
input (Start End)(:datetime20.) behaviour $20.;
format Start End datetime20.;
datalines;
11OCT18:08:31:40 11OCT18:08:32:40 Bad
11OCT18:08:32:40 11OCT18:08:33:40 Good
11OCT18:08:33:40 11OCT18:08:34:40 Bad
;
data have2;
input Date:ddmmyy10. Time:time8. behaviour $20.;
format Date ddmmyy10. Time time8. DateTime datetime20.;
DateTime=dhms(Date, 0,0,Time);
datalines;
11-10-2018 08:35:25 Bad
11-10-2018 08:40:04 Good
11-10-2018 08:45:27 Bad
11-10-2018 08:31:57 Good
;
proc sql;
create table want as
select have1.*
,have2.DateTime as DateTime
,have2.Behaviour as Behaviour2
from have1 left join have2
on have2.DateTime between Start and End;
quit;
Just realised that I also need to combine by animal, have observed more than one cow and all data include more than one cow...
This is have2, timestamp is formatted at datetime20. in my sas dataset.
Timestamp | Cow_ID | State | Zone |
1539246925 | 6300 | Standing | Feeding |
1539247204 | 6300 | Eating | Feeding |
1539247527 | 6300 | Eating | Feeding |
1539247812 | 6300 | Eating | Feeding |
have2
start_time | end_time | cow_id |
11OCT18:08:31:40 | 11OCT18:08:32:40 | 6300 |
11OCT18:08:32:40 | 11OCT18:08:33:40 | 6300 |
11OCT18:08:32:40 | 11OCT18:08:33:40 | 6300 |
11OCT18:08:33:40 | 11OCT18:08:34:40 | 6300 |
11OCT18:08:33:40 | 11OCT18:08:34:40 | 6300 |
so not only do I need to merge these two files by time but also by cow_id.
There is a lot more variable in both files, but as I can see from what I can do in the in program you wrote, it does not really matter since they are all included
please try this code
the cow_id will be missing in the want dataset since the cow_id date range does not match with the start and end date.
data have;
input maindate cow_id state $ zone$;
format maindate datetime18.;
cards;
1539246925 6300 Standing Feeding
1539247204 6300 Eating Feeding
1539247527 6300 Eating Feeding
1539247812 6300 Eating Feeding
;
data have2;
input Start: datetime16. End : datetime16.;
format start datetime18. end datetime18.;
cards;
11OCT18:08:31:40 11OCT18:08:32:40
11OCT18:08:32:40 11OCT18:08:33:40
11OCT18:08:33:40 11OCT18:08:34:40
;
proc sql;
create table want as select a.*, b.start, b.end from have2 as b left join have as a on b.start<=a.maindate<=b.end;
quit;
data have;
input date :ddmmyy10. time : time8.;
maindate=dhms(date,hour(time),minute(time),second(time));
format date date9. time time8. maindate datetime18.;
cards;
11-10-2018 08:35:25
11-10-2018 08:40:04
11-10-2018 08:45:27
;
data have2;
input Start: datetime16. End : datetime16.;
format start datetime18. end datetime18.;
cards;
11OCT18:08:31:40 11OCT18:08:32:40
11OCT18:08:32:40 11OCT18:08:33:40
11OCT18:08:33:40 11OCT18:08:34:40
;
proc sql;
create table want as select a.maindate, b.start, b.end from have as a left join have2 as b on b.start<=a.maindate<=b.end;
quit;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.