topic How to compare two different datetime values in proc sql in SAS Programming

How to compare two different datetime values in proc sql

cruzerkk — Fri, 29 Sep 2017 20:11:32 GMT

So here is the deal, I have two SQL Server Tables (A & B) which I want to merge using Proc sql.

Table A has variable datetime_A and Table B has variable datetime_B.

datetime_A format : yyyy-mm-dd hh:mm:ss:mil

datetme_B format: yyyy-mm-dd hh:mm:ss

Further, the datetime_A is accurate to the milliseconds and datetime_B has its seconds rounded (which means the seconds are always 00)

In essence i want to compare only till the minutes part. I figured I will have to somehow convert the datetime to a char or varchar till the minutes part and compare the two in my join clause.

I'm unable to arrive at the correct syntax to do the same.

Any help is greatly appreciated.

Re: How to compare two different datetime values in proc sql

mkeintz — Fri, 29 Sep 2017 20:52:08 GMT

If you're dealing with SAS datetime values then it's the numbers of second after midnight Jan 1, 1960. If milliseconds are 000, then the datetime value is an integer. The milliseconds are just the non-integer component of the datetime.

So just match

datetime_b

int(datetime_a)

Re: How to compare two different datetime values in proc sql

cruzerkk — Tue, 03 Oct 2017 18:05:58 GMT

1) I'm dealing with Datetime values in SQL which I am bringing into SAS through proc SQL.
2) I want to compare only till minutes, in other words, if datetime_A = 2017-01-13 11:28:11.477
& datetime_B = 2017-01-13 11:28:00. I want the match to be true.

Re: How to compare two different datetime values in proc sql

mkeintz — Tue, 03 Oct 2017 18:32:32 GMT

Are you asking an SQL server question in a SAS forum? Are you employing pass-through code?

Re: How to compare two different datetime values in proc sql

cruzerkk — Tue, 03 Oct 2017 18:37:35 GMT

I am asking if there is a way to manipulate incoming data from sql server on the sas side. I do not have access to the sql server to do the manipulation there.
This is probably what I would have done in the SQL Server Side:
select * from A inner join B on
CONVERT(VARCHAR(16), datetime_A ) = CONVERT(VARCHAR(16), datetime_B )

But the 'CONVERT' function does not work in proc sql. Is there a corresponding implementation in SAS ?

Re: How to compare two different datetime values in proc sql

mkeintz — Tue, 03 Oct 2017 18:46:21 GMT

I have never used SAS access to sql server, but I thought that if sql server metadata specifies a variable as SQL DATETIME (apparently 2 4-byte integers in an 8-byte value, according to a quick google search), it would be recognized by PROC SQL, and consequently the value would be converted to SAS datetime value. If so, then the milliseconds would merely be the non-integer portion of the value.

If you extract a row with a known SQL Server datetime value, and then apply a SAS datatime27.3 format to it, do you get the expected display of date and time? If so, then my conjecture on sas/access to sql server is apparently correct, and you can use the INT function in SAS to remove the sub-second component. No doubt someone with direct experience can cite chapter and verse on this, alleviating the need for my experiment.

Re: How to compare two different datetime values in proc sql

Patrick — Tue, 03 Oct 2017 20:46:23 GMT

@cruzerkk

When joining two database tables using SQL syntax formulated in the SAS SQL flavor then the SAS/Access engine will try and push as much of this SQL to the database for processing. If using SAS only functions in the SQL then SAS won't be able to push a lot of the SQL but will have to pull all the data from the database to SAS for processing which can be quite an overhead.

Within SAS

SAS DateTime values are the count of seconds starting from 1/1/1960. This count is stored in a SAS variable of type Numeric and you can use it for calculations. So to find two SAS DateTime values within 60 seconds: abs(DTTM_Var1 - DTTM_Var2)<60

There are multiple approaches possible to find values with the same number of minutes: You can use intnx() to align the values to the beginning of the minute and then compare, you can divide by 100, use int() and compare, or you can convert the values to a character up to the minute using a put(<var>, <format>) and compare.

...BUT: To avoid the overhead for SAS having to pull all the data on its side: You can also use explicit SQL pass-through and write the whole SQL in the database flavor using database functions and then only pull the result set into SAS for further processing (if you need it there at all).

Re: How to compare two different datetime values in proc sql

s_lassen — Wed, 04 Oct 2017 09:56:12 GMT

@cruzerkk:

You could use the ROUND function to round datetime_A to the nearest minute - as others have already pointed out, the SAS datetime value is number of seconds, so rounding to the nearest 60 will get you the minutes, e.g.:

select 
  <columns>
from 
  <sqlserver table A> a 
join 
  <sqlserver table B> b
on
  b.<datetime variable>=round(a.<datetime variable>,60)