topic Re: problem with trunc SQL in SAS in SAS Programming

problem with trunc SQL in SAS

DataExplorer — Thu, 18 Jul 2019 07:30:10 GMT

I have the following Oracle SQL:

select count(*) from my_table where trunc(timestamp) = trunc(sysdate);

I have lib to the database and created following code:

proc sql;

select count(*) from Libma.my_table where trunc(timestamp) = trunc(sysdate);

run;

How I figured out trunc works different in SAS but what is best solution to get the same result as in Oracle SQL.

Re: problem with trunc SQL in SAS

Kurt_Bremser — Thu, 18 Jul 2019 07:47:44 GMT

What are the contents of the columns?

Oracle trunc() is a function that retrieves the date out of a date, since (IIRC) Oracle dates can also contain times (similar to Excel, where datetimes are stored as counts of days, with the time in the fractional part).

So what you are looking for in SAS is the datepart() function if you need to retrieve the date from a datetime (as in SAS dates are counts of days, and times counts of seconds, so datetime = 86400 * date). But you need to take care that you don't apply datepart() to something that is already a date when imported into SAS by the oracle engine.

Re: problem with trunc SQL in SAS

Kurt_Bremser — Thu, 18 Jul 2019 07:49:58 GMT

PS trunc() is a special function in SAS that reduces the length of the binary representation of numbers (which means that the values remain mostly unchanged aside from a loss in precision).

Re: problem with trunc SQL in SAS

DataExplorer — Thu, 18 Jul 2019 10:56:57 GMT

Ok i tried the following Code

where datepart(zeitstempel) = today();

Seem to work but no matches in the database so I tried the following code for a test:

where datepart(zeitstempel) = datepart(timestamp '2018-08-08 18:50:07');

but I get this error

33         where datepart(mytimestamp) = datepart(timestamp '2018-08-08 18:50:07');

                                                          _____________________

                                                          22

ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, CONTAINS,

              EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.

Not sure what the problem ist.