topic Re: FedSQL Convert and Compare current_date in SAS Programming

FedSQL Convert and Compare current_date

Uemerson — Wed, 22 Aug 2018 14:08:58 GMT

Hello experts!

I am trying to convert the code below (Oracle SQL) to FedSQL used by SAS DataFlux. The field called in this example "DATE" is a numeric type with the format 'yyyymmdd'.

Name Type

DATE NUMBER(8)

select count(0) AS Ten_Days_Before
from [TABLE]
where DATE = TO_CHAR(SYSDATE-10, 'yyyymmdd');

Any suggestion?

I tried the function "PUT" with the code below, but It´s not working because of different data types.

select count(0) AS Ten_Days_Before
from [TABLE]
where PUT(DATE,yymmddn8.) = PUT(current_date-10,yymmddn8.) ;

Thank you!

Re: FedSQL Convert and Compare current_date

ChrisNZ — Thu, 23 Aug 2018 00:23:58 GMT

1. It´s not working because of different data types.

What are the data types?

2. If this works

put(DATE,yymmddn8.) = put(CURRENT_DATE-10,yymmddn8.)

then this works

DATE = CURRENT_DATE-10

(not that it's better, just different and a tad faster)

Re: FedSQL Convert and Compare current_date

Uemerson — Tue, 28 Aug 2018 11:59:53 GMT

Hello ChrisNZ, Thanks for your reply.

The different types I mean are about the field called "DATE" and the system variable "current_date". The first one is Numeric, and after use the function PUT it will be transformed into Double, right? And the second one is a date, and I don´t know what type it will be after PUT function, anyway, the result of this example I gave is 0.

Do you have another function or logic to do this count comparing these fields?

Thank you!

Re: FedSQL Convert and Compare current_date

ChrisNZ — Tue, 28 Aug 2018 21:54:18 GMT

1. I am confused as to what the values are.

>The first one is Numeric

Is it a SAS date like 23056? Or a number expressing a date like 20180612?

> And the second one is a date, and I don´t know what type it will be after PUT function,

A date should probably not need using PUT

Please provide the raw untransformed values for each, for example the first lines of :

select DATE, SYSDATE from TABLE

2. I am confused as to where you run your query.

This is invalid SAS code:

select count(0) AS Ten_Days_Before

from [TABLE]

where DATE = TO_CHAR(SYSDATE-10, 'yyyymmdd');

while this is only valid in SAS:

select count(0) AS Ten_Days_Before
from [TABLE]
where PUT(DATE,yymmddn8.) = PUT(current_date-10,yymmddn8.) ;

So which one is it? Where do you run the code? Please provide full code and log.

Re: FedSQL Convert and Compare current_date

Uemerson — Wed, 29 Aug 2018 09:09:07 GMT

Hi ChrisNZ,

1. The field "DATE" is a number expressing a date (Ex.: 20180829).

2. My query was running in Oracle SQL, now I need to migrate the code to SAS DataFlux.

Solution:

I tried the code below, and It works well. I think that the problem was the format used with PUT function.

select count(0) AS Ten_Days_Before
from

[TABLE]
where

PUT(DATE,8.) = PUT(current_date-10,yymmddn8.) ;

Thanks for your reply!

Re: FedSQL Convert and Compare current_date

ChrisNZ — Wed, 29 Aug 2018 09:18:31 GMT

It's clearer now.
where input(DATE,yymmddn8.) = current_date-10 ;
orwhere input(DATE,yymmdd8.) = current_date-10 ; would probably work too. Not that it's better. Just to show how to compare dates rather than strings.