09-29-2017 04:11 PM
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.
09-29-2017 04:52 PM
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
10-03-2017 02:05 PM
10-03-2017 02:37 PM
10-03-2017 02:46 PM
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.
10-03-2017 04:46 PM
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.
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).
10-04-2017 05:56 AM
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)