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

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

jkurka — Fri, 25 Mar 2016 18:56:45 GMT

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?

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

PGStats — Fri, 25 Mar 2016 19:14:40 GMT

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

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

jkurka — Fri, 25 Mar 2016 19:46:44 GMT

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.

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

ballardw — Fri, 25 Mar 2016 20:06:43 GMT

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

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

PGStats — Fri, 25 Mar 2016 21:01:18 GMT

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.

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

jkurka — Fri, 25 Mar 2016 23:00:48 GMT

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.

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

ballardw — Fri, 25 Mar 2016 23:06:27 GMT

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?

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

jkurka — Sat, 26 Mar 2016 00:36:24 GMT

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!