BookmarkSubscribeRSS Feed
cruzerkk
Calcite | Level 5

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. 

7 REPLIES 7
mkeintz
PROC Star

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

to

    int(datetime_a)

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
cruzerkk
Calcite | Level 5
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.
mkeintz
PROC Star

 

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

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
cruzerkk
Calcite | Level 5
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 ?
mkeintz
PROC Star

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.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Patrick
Opal | Level 21

@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).

 

s_lassen
Meteorite | Level 14

@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)

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 5508 views
  • 0 likes
  • 4 in conversation