BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Uemerson
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Uemerson
Fluorite | Level 6

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!

View solution in original post

5 REPLIES 5
ChrisNZ
Tourmaline | Level 20

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)

Uemerson
Fluorite | Level 6

 

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!

ChrisNZ
Tourmaline | Level 20

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.

 

Uemerson
Fluorite | Level 6

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!

ChrisNZ
Tourmaline | Level 20
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.

SAS Innovate 2025: Save the Date

 SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!

Save the date!

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 1535 views
  • 0 likes
  • 2 in conversation