DATA Step, Macro, Functions and more

Query to extract datetimes in one table between datetimes found in a second table

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

Query to extract datetimes in one table between datetimes found in a second table

Hello community! Hopefully someone can help, as I am stuck

I have data in table 1 with variables and values with datetimes with each row corresponding to 1/40th of a second, with over 30 days per ID. I have data in table 2 that has the start and stop datetimes for each date for each ID.

I want SQL to pull out data in table 1 between the datetimes for each day in table 2.

For example:

Table 1

datetimestamp                id

21MAY15:06:14:57.250  se10
21MAY15:06:14:57.275  se10
21MAY15:06:14:57.300  se10
21MAY15:06:14:57.325  se10
21MAY15:06:14:57.350  se10
21MAY15:06:14:57.375  se10

 

Table 2

beddatetime                       wakedatetime

20MAY15:21:30:00.0000    21MAY15:06:15:00.0000
20MAY15:21:30:00.0000    21MAY15:06:15:00.0000
20MAY15:21:30:00.0000    21MAY15:06:15:00.0000
20MAY15:21:30:00.0000    21MAY15:06:15:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000
21MAY15:21:42:40.0000    22MAY15:07:23:00.0000

 

Currently, the below code pulls out the correct datetimestamp only if beddatetime is after midnight. If beddatetime is prior to midnight then it just stops at midnight and the information until midnight is lost.

PROC SQL;
    CREATE TABLE want AS
    SELECT    t2.x, t2.y, t2.z, t2.lux, t2.event, t2.temp, t2.id, /*t2.date, t2.timestamp,*/ t2.datetimestamp,
            t3.id AS id1, /*t3.date AS date1, t3.waketime, t3.bedtime,*/ t3.day, t3.beddatetime, t3.wakedatetime,
            t3.sleep_h
    FROM WORK.DATA_RAW1 t2
    NATURAL JOIN surveydata t3
    WHERE t2.datetimestamp BETWEEN t3.beddatetime AND t3.wakedatetime
    ORDER by id, datetimestamp ;
QUIT;

 

Any suggestions?


Accepted Solutions
Solution
‎03-25-2016 08:36 PM
Respected Advisor
Posts: 4,644

Re: Query to extract datetimes in one table between datetimes found in a second table

Do not use NATURAL JOIN for this, as it will enforce t2.date=t3.date. 

PG

View solution in original post


All Replies
Solution
‎03-25-2016 08:36 PM
Respected Advisor
Posts: 4,644

Re: Query to extract datetimes in one table between datetimes found in a second table

Do not use NATURAL JOIN for this, as it will enforce t2.date=t3.date. 

PG
Contributor
Posts: 25

Re: Query to extract datetimes in one table between datetimes found in a second table

Even though they have been coded out? With only the ID, datetimestamp, wakedatetimestamp, and beddatetimestamp, the output is still incorrect.

 

Any time I have called for an inner join my computer freezes up after 10 or so minutes.

Super User
Posts: 10,487

Re: Query to extract datetimes in one table between datetimes found in a second table

If T3 is your constraint table you might try a RIGHT JOIN instead of NATURAL JOIN.

 

Contributor
Posts: 25

Re: Query to extract datetimes in one table between datetimes found in a second table

The constraining table has other IDs with respective wakedatetimes and beddatetimes also, and is producing some odd results that still are cutting off the data at midnight.

Super User
Posts: 10,487

Re: Query to extract datetimes in one table between datetimes found in a second table

Sounds like time to provide example input data with all of the variables (dummy is fine as long as it replicates the problem) and what the actual output desired for that example data may be.

 

Oh, One other point: Those values are SAS Datetime numeric values aren't they and not character?

Contributor
Posts: 25

Re: Query to extract datetimes in one table between datetimes found in a second table

It's solved! Using the NATURAL JOIN was the only join that worked, but I removed date and instead used a generic number to keep the data set in order.

 

Sometimes just hashing these things out with others gets the brain flowing!

Respected Advisor
Posts: 4,644

Re: Query to extract datetimes in one table between datetimes found in a second table

Natural join joins on all common named variables whether you mention them in your select list or not. 

 

Build smaller datasets for testing. Get the correct result on those before scaling up to your full datasets.

PG
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 322 views
  • 2 likes
  • 3 in conversation