topic Re: Date come from Oracle to change format in SAS Programming

Date come from Oracle to change format

azertyuiop — Wed, 20 Sep 2017 08:12:59 GMT

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.

Re: Date come from Oracle to change format

Patrick — Wed, 20 Sep 2017 10:33:38 GMT

@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.

Re: Date come from Oracle to change format

Shmuel — Wed, 20 Sep 2017 10:41:17 GMT

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.

Re: Date come from Oracle to change format

azertyuiop — Wed, 20 Sep 2017 13:44:59 GMT

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

Re: Date come from Oracle to change format

gamotte — Wed, 20 Sep 2017 12:30:11 GMT

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.

Re: Date come from Oracle to change format

azertyuiop — Wed, 20 Sep 2017 13:47:07 GMT

Hello ,

Sorry i want say

mytable.mydate date as new_date format=datetime.

mytable.mydate not mytable.format ...

Re: Date come from Oracle to change format

Shmuel — Wed, 20 Sep 2017 14:44:52 GMT

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.

Re: Date come from Oracle to change format

ballardw — Wed, 20 Sep 2017 15:13:31 GMT

@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.

Re: Date come from Oracle to change format

Patrick — Wed, 20 Sep 2017 22:51:22 GMT

@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#p06jk0u30uhuj5n18fqw9sxr25lk.htm

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.

Re: Date come from Oracle to change format

azertyuiop — Fri, 22 Sep 2017 09:37:01 GMT

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.

Re: Date come from Oracle to change format

Patrick — Fri, 22 Sep 2017 09:54:10 GMT

@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;

Re: Date come from Oracle to change format

azertyuiop — Fri, 22 Sep 2017 11:39:28 GMT

After a new test , this test is ok .

I can validate your function to see the date

Thanks for your help

Re: Date come from Oracle to change format

Patrick — Fri, 22 Sep 2017 12:35:46 GMT

@azertyuiop

So that means your problem is solved?

Re: Date come from Oracle to change format

azertyuiop — Fri, 22 Sep 2017 13:51:44 GMT

Excatly ,

The problem is solved . We can close this topic 🙂

Re: Date come from Oracle to change format

Patrick — Fri, 22 Sep 2017 21:51:21 GMT

@azertyuiop

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