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

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

@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

14 REPLIES 14
Patrick
Opal | Level 21

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

Shmuel
Garnet | Level 18

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.

azertyuiop
Quartz | Level 8

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

gamotte
Rhodochrosite | Level 12

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.

 

azertyuiop
Quartz | Level 8

Hello ,

 

Sorry i want say

 

mytable.mydate date as new_date format=datetime.

 

mytable.mydate not mytable.format ...

Patrick
Opal | Level 21

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

Shmuel
Garnet | Level 18

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.

ballardw
Super User

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

azertyuiop
Quartz | Level 8

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.

Patrick
Opal | Level 21

@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;
azertyuiop
Quartz | Level 8

After a new test , this test is ok .

 

I can validate your function to see the date Man Wink

 

Thanks for your help

Patrick
Opal | Level 21

@azertyuiop

So that means your problem is solved?

azertyuiop
Quartz | Level 8

Excatly ,

 

The problem is solved . We can close this topic 🙂

Patrick
Opal | Level 21

@azertyuiop

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 13006 views
  • 1 like
  • 5 in conversation