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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.