Merge IDs, start and end of recording with timeseries datasets

[Giulia Gaggioni]

Hi All,

I am having issues with this merge.

In one dataset, I have the ID, the device serial number, the start and the end date of the recording.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte ID str8 device int(StartRecording EndRecording)
1 "62-36-7d" 21489 21497
2 "62-36-7d" 21497 21503
3 "03-04-2e" 21489 21503
end
format %td StartRecording
format %td EndRecording

In another dataset, I have the device number, and a time series (Date 1 & Date 2) of the variable of interest.

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 device int(Date1 Date2) long VariableofInterest
"62-36-7d" 21489 21490    700
"62-36-7d" 21490 21491    350
"62-36-7d" 21491 21492   1000
"62-36-7d" 21492 21493     55
"62-36-7d" 21495 21496    302
"62-36-7d" 21496 21497    650
"62-36-7d" 21497 21498     20
"62-36-7d" 21498 21499    852
"62-36-7d" 21499 21500     39
"62-36-7d" 21500 21501    102
"62-36-7d" 21501 21502    258
"62-36-7d" 21502 21503    657
"03-04-2e" 21489 21490     96
"03-04-2e" 21490 21491    104
"03-04-2e" 21491 21492  36987
"03-04-2e" 21492 21493 201598
"03-04-2e" 21493 21494  98745
"03-04-2e" 21494 21495   3698
"03-04-2e" 21495 21496   2015
"03-04-2e" 21496 21497    258
"03-04-2e" 21497 21498    357
"03-04-2e" 21498 21499    198
"03-04-2e" 21499 21500    963
"03-04-2e" 21500 21501     15
"03-04-2e" 21501 21502    367
"03-04-2e" 21502 21503    126
end
format %td Date1
format %td Date2

The problem is that the same device number can be associated with two different IDs; however, in this case, the start and the end date of the recording also differ.
My aim is to merge these two datasets resulting in a file with ID, device, Date1, Date2, Variable of Interest. Any suggestions? Many thanks in advance.

[Clyde Schechter]

It is unclear to me what you want the final result to consist of. The second dataset is rather strange in that Date2 == Date1 + 1 in every observation. So you really have only a single date in that data set. And you don't say which dates from the second data set you want to pair up with which dates from the first. I'm going to make a guess that you want to pair each observation from the first data set with those in the second data set that have the same device number and where Date1 and Date2 fall in the interval from StartRecording to EndRecording (inclusive of endpoints).

use dataset1, clear
joinby device using dataset2
keep if inrange(Date1, StartRecording, EndRecording) ///
    & inrange(Date2, StartRecording, EndRecording)
sort ID Date1

If this is not what you wanted to do, when posting back, please make it clear what you do want to do. Probably rather than trying to describe it in words, you should post a short example from datasets 1 and 2 and then also by hand work out what the result you want looks like, and show that as well.

[Leonardo Guizzetti]

I'm not sure I completely understand the issue here either, but I would think a simple merge (or two) is sufficient? Sort your dataset with dates by ID and start date, then identify the first record of each ID which will be your first start time, which you can use to merge. You can do it again if you sort by ID then end date, and then identify the last record, which is the last date for each ID.

[Clyde Schechter]

Leonardo Guizzetti I don't see how -merge- can be used with these two data sets. What is the merge key? The only common variable in the data sets is device. But it doesn't identify observations in either data set. What am I missing?

[Leonardo Guizzetti]

Hmm, perhaps I've misunderstood. I thought they could be merged on serial number?

[Giulia Gaggioni]

Thanks Clyde and Leonardo, and apologies for not being clear in my first post. I am also struggling to see how datasets could be merged by serial numbers; the code #2 works perfectly – thank you Clyde! I did initially try to use rangejoin but it was not the right approach.