DATA Step, Macro, Functions and more

Date come from Oracle to change format

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

Date come from Oracle to change format

Hello / Good Morning ,

 

I use data which come from  ORACLE data base . The problem is the print format in the screen ... I can see " [I][B][COLOR="#FF0000"]*[/COLOR][/B][/I] " or " [I][B][COLOR="#FF0000"]*******[/COLOR][/B][/I] " or again " [I][B][COLOR="#FF0000"].[/COLOR][/B][/I] " by moment the date format is alway in digit even if there is this function  " [I][B][COLOR="#FF0000"]format=[/COLOR][/B][/I] " or " [I][B][COLOR="#FF0000"]informat=[/COLOR][/B][/I] " .

 

For example in my table in ORACLE prints in SAS this digit " 1820361600 " instead of " september 7, 2017 " .

 

[CODE="SAS"]libname mydb meta library="database oracle johndoe";

 

proc sql;

 

create table table_exit as

select mytable.textfield , mytable.format = datetime. as new_date
from mydb.mytable ;

 

run;[/CODE]

 

This function  " [I][B][COLOR="#FF0000"]matable.format = datetime. as new_date  [/COLOR][/B][/I] " is the only case where " [I][B][COLOR="#FF0000"] datetime. [/COLOR][/B][/I] " prints a date like this " 07SEP1700:00:00 " . If I use those functions  YYMMDDw. , YYMMDD10. ,  date9. ,  datew. ... I can see a digit " 1820361600 " or a dot or several stars. If I use SQL functions like month() ; day() , year() , datepart() the query is in error ...

 

Which instruction I must use to obtain the true date format ? 

 

Thanks for your help.


Accepted Solutions
Solution
‎10-05-2017 08:23 AM
Respected Advisor
Posts: 4,797

Re: Date come from Oracle to change format

Posted in reply to azertyuiop

@azertyuiop

It is YOU who needs to close it by marking the answer which helped you the most as solution.

View solution in original post


All Replies
Respected Advisor
Posts: 4,797

Re: Date come from Oracle to change format

[ Edited ]
Posted in reply to azertyuiop

@azertyuiop

The code you've posted...

proc sql;
create table table_exit as
select mytable.textfield , mytable.format = datetime. as new_date
from mydb.mytable ;
run;

....doesn't look syntactically right to me. Could you run this without errors?

 

In general: The SAS/Access engine will convert an Oracle DATE field to the approapriate SAS variable type and assign it an appropriate SAS DateTime format (for display). You shouldn't have to code for this explicitely unless you want to change the default behaviour.

If your Oracle field isn't DATE then don't expect SAS to convert the value automatically into a SAS DateTime value.

 

Looking at your code: Is there really an Oracle column with name format which is of type DATE? I dare to doubt this.

 

Please explain what Oracle fields you have, which Type the are of (DATE, VARCHAR2, ....) and what you'd like to get on the SAS side for these fields.

Trusted Advisor
Posts: 1,848

Re: Date come from Oracle to change format

Posted in reply to azertyuiop

I'm not an oracle user, but having your example:
For example in my table in ORACLE prints in SAS this digit " 1820361600 " instead of " september 7, 2017 " .

 

try next code:

 

data test;

     data_in = 1820361600 ;

     date_is = datepart(data_in);

     format date_is mmddyy10. ;

run;

 

I don't remember which format will display like september 7, 2017.

Contributor
Posts: 68

Re: Date come from Oracle to change format

[ Edited ]

Hello ,

 

@Patrick 

 

After a control in ORACLE database , the column is effectively a date. In ORACLE we can see the date in this format " YYYY-MM-DD HH-MM-SS" . When we see the date by SAS the data which come from ORACLE , the format is an other . It's a date which appears like a digit ...

 

The Oracle view it's "2017-09-07 00:00:00" in SAS view it's " 1820361600 " .

 

I can confirm that this SAS program is in function :

 

proc sql;
create table table_exit as
select mytable.textfield , mytable.mytade format = datetime. as new_date
from mydb.mytable ;
run;

 

@Shmuel

 

" 1820361600 " it isn't a fixe value. This date can be change. For example we can found the date of 6 september or of 8 september or of an other month and year ...

 

To change the format, I have used your function datepart like this in a first query :

 

" mytable.mydate format = datetime. as new_date_format "

 

In a second query I have used this function to burst the date in several with this system : " year(datepart(new_date_format)) " .

 

This problem arrives when we don't use the part "datepart" in the bloc of functions " year(datepart([name_of_your_column])) " . You can too change " datepart " by " datetime " .

 

Thanks for your reply

Super Contributor
Posts: 359

Re: Date come from Oracle to change format

Posted in reply to azertyuiop

Hello,

 

I agree with @Patrick that your query does not seem syntactically correct.

Didn't you mean something as

 

mytable.date as new_date format=datetime.

 

Contributor
Posts: 68

Re: Date come from Oracle to change format

Hello ,

 

Sorry i want say

 

mytable.mydate date as new_date format=datetime.

 

mytable.mydate not mytable.format ...

Respected Advisor
Posts: 4,797

Re: Date come from Oracle to change format

[ Edited ]
Posted in reply to azertyuiop

@azertyuiop

The good new is: The number (the digits) you've posted have the right value for a SAS DateTime which corresponds to the date you've posted. That means conversion from an Oracle DATE value to a SAS DateTime value happens as it should and the problem is only about display (apply an appropriate SAS DateTime format the the SAS variable of type Numeric).

 

Per default and without any exlicit format definition SAS will convert an Oracle date to a SAS DateTime value and store this value in a numeric SAS variable. Also per default SAS will assign a permanent format of DATETIME20. to this numeric variable so the internal number (that's what you've posted) will display as a datetime for printing.

http://support.sas.com/documentation/cdl/en/acreldb/69580/HTML/default/viewer.htm#p06jk0u30uhuj5n18f...

 

I totally understand that we often need to simplify the code we post BUT you still should try to post a working and tested example as far as possible.

Please execut below (untested) code against your actual oracle table and oracle column and tell us if you also see the reported issue with this code. If yes then please tell us exactly how you're looking at the data as this is not about getting a wrong value into SAS but only about not displaying/printing this value in a human readable form.

proc sql inobs=1;
  create table work.MySASTable as
    select a.MyOraDateVar as MySASDttmVar
    from oralib.oratbl as a
  ;
quit;

 

...Oh... and reading the end of your initial post again - which is not in a very readable format so I've missed this so far:

SAS has a concept of DATE and DATETIME values. If you read an Oracle DATE column into SAS then SAS will store this as a SAS DATETIME value.

You need to use SAS functions and formats for SAS DATETIME values. The ones for DATE values won't work - or you need to convert the SAS DATETIME value to a SAS DATE value.

 

A SAS DATE value is noting else than the count of DAYS since 1/1/1960

A SAS DATETIME value is the count of SECONDS since 1/1/1960

 

There is quite a bit in the SAS documentation explaining the concepts of SAS Dates and DateTimes and how to use them. I suggest you're doing a bit of reading to fully understand this. Once you do working with SAS Dates and DateTimes becomes quite simple.

Trusted Advisor
Posts: 1,848

Re: Date come from Oracle to change format

Posted in reply to azertyuiop

1820361600  - is just your example.

It was obvious to me that you got a timestamp (date and time in same variable).

 

The function DATEPART computes the date part out of the timestamp vraiable.

SAS date 01JAN1960 is represented as 0. Any sas date is counting days from this startting point.

 

The FORMAT is used to tell SAS how to display the value.

Some examples of date formats:  date9. , ddmmyy8. , ddmmyy10. , mmddyy10. etc.

 

You can google for SAS DATE FORMATS and choose whatever you like.

Super User
Posts: 13,941

Re: Date come from Oracle to change format


Shmuel wrote:

I'm not an oracle user, but having your example:
For example in my table in ORACLE prints in SAS this digit " 1820361600 " instead of " september 7, 2017 " .

 

try next code:

 

data test;

     data_in = 1820361600 ;

     date_is = datepart(data_in);

     format date_is mmddyy10. ;

run;

 

I don't remember which format will display like september 7, 2017.


The format is Worddate.

Contributor
Posts: 68

Re: Date come from Oracle to change format

Hello ,

 

I notice your specifition about oracle en SAS linked to the date format.

 

@Patrick

 

I can't test this code :

 

proc sql inobs=1;
  create table work.MySASTable as
    select a.MyOraDateVar as MySASDttmVar
    from oralib.oratbl as a;
quit;

 

I'haven' t the permission to read or see this type of table by SAS.

Respected Advisor
Posts: 4,797

Re: Date come from Oracle to change format

Posted in reply to azertyuiop

@azertyuiop

That has been sample code. You need to replace the libref, table name and column name with what you have in your actual table.

Based on the code you've posted this should be something like:

proc sql inobs=1;
  create table table_exit as
    select mytable.<name of your date field> as new_date
      from mydb.mytable;
run;
Contributor
Posts: 68

Re: Date come from Oracle to change format

[ Edited ]

After a new test , this test is ok .

 

I can validate your function to see the date Man Wink

 

Thanks for your help

Respected Advisor
Posts: 4,797

Re: Date come from Oracle to change format

Posted in reply to azertyuiop

@azertyuiop

So that means your problem is solved?

Contributor
Posts: 68

Re: Date come from Oracle to change format

Excatly ,

 

The problem is solved . We can close this topic Smiley Happy

Solution
‎10-05-2017 08:23 AM
Respected Advisor
Posts: 4,797

Re: Date come from Oracle to change format

Posted in reply to azertyuiop

@azertyuiop

It is YOU who needs to close it by marking the answer which helped you the most as solution.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 1061 views
  • 0 likes
  • 5 in conversation