topic Re: PROC SQL Joins & Time Formats in New SAS User

PROC SQL Joins & Time Formats

sabataged — Wed, 31 Oct 2018 20:44:36 GMT

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!!

Re: PROC SQL Joins & Time Formats

Kurt_Bremser — Wed, 31 Oct 2018 20:43:50 GMT

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

Re: PROC SQL Joins & Time Formats

sabataged — Wed, 31 Oct 2018 20:50:05 GMT

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

Re: PROC SQL Joins & Time Formats

PGStats — Wed, 31 Oct 2018 21:06:49 GMT

The join condition

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

should work.

Re: PROC SQL Joins & Time Formats

sabataged — Wed, 31 Oct 2018 21:17:45 GMT

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