Combing data points based on time

[PerNielsen]

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

[PeterClemmensen]

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
;. 

[PerNielsen]

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

[PeterClemmensen]

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?

[PeterClemmensen]

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;

[PerNielsen]

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

[Jagadishkatam · Accepted solution]

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;

[Jagadishkatam]

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;