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!
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!
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)
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!
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.
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!
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.