PROC SQL Joins & Time Formats

[sabataged]

Hello everyone, I have run across a strange problem. 

I am trying to join two tables that look something like this: 

Table "x"

Chat_ID    xAge     xGender      xDate        xTime         

  1236     |    21    |       F     |     7/5/18    |   5:01PM

  2345     |    17    |       M    |     8/3/18    |   4:33PM

Table "y"

Res_ID     yAge     yGender      yDate         yTime         

  7708    |    21    |       F       |     7/5/18    |   5:01PM

  7707    |    17    |       M      |     8/3/18    |   4:33PM

For example, Chat_ID 1236 and Res_ID 7708 are describing the same person, and must be joined by matching age, gender, and time.

I am trying to join them using this code: 

proc sql; 
create table Join1 as 
select x.*, y.* from x
inner join y on 
x.xdate=y.ydate and
x.xtime=y.ytime and
x.xage=y.yage and 
x.xgender=y.ygender
;
quit;

    Under normal circumnstances, this code works, but I am getting 0 joins right now. After doing some troubleshooting, I realized that the "xtime" variable may be the culprit. I took away all the time formats to look at the raw SAS Time format:

For example, the time "5:12 PM" is written as 1.8201E9 in table x, but written as 61920 as table y. Why might this be? I think this discrepancy is the reason for why my tables aren't getting joined, but I'm not sure how to fix it. 

Notes:

• These datasets were imported from Excel via proc import. 
• The time in table "x" was created by separating the time from a "DateTime" field
• Then I used "intnx" function to round the time to the nearest minute.

I don't know if these things caused a difference, but I thought I would mention them! I did this to other datasets before, but never had this problem in the past, so I'm not sure what happened.

Thanks in advance!!

[Kurt_Bremser]

Looks like one is a time value (seconds from midnight) and the other a datetime (seconds from midnight on 1960-01-01).

[sabataged]

Hi Kurt, Thanks for your reply! Yes, the time field was created from a DateTime field by using timepart(). Would this be why the join isn't happening properly? I have used this function in the past to create separate date and time fields and have never run across this problem before. 😕

[PGStats · Accepted solution]

The join condition

round(timepart(x.xtime), '00:01:00't) = round(y.ytime, '00:01:00't)

should work.

[sabataged]

Oh my goodness, this worked like a charm!  Thank you so much for your help!  I appreciate it a lot. I was stuck on this for a loooong time today. 🙂