Desktop productivity for business analysts and programmers

PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ???

Reply
New Contributor
Posts: 3

PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ???

Hi All,

 

First time post - usually I can find an anwer by seaching the forums, but I've struggled on this one.

 

I've built a WORK.OUTPUT table in EG with some date columns with a dd/mm/yyyy format.

 

I'm then using a Program with PROC SQL to create a table in Oracle based upon the WORK.OUTPUT table.

 

However when I do this, all the dates are defaulting to SAS styled format: DD-MON-YY in the Oracle table. How do I get PROC SQL to build the table in oracle with dd/mm/yyyy date formats? I've tried both:

 

PROC SQL

create table ORACLELIB.NEW_TABLE as

SELECT *

FROM WORK.OUTPUT;

QUIT;

 

and

 

PROC SQL;

create table ORACLELIB.NEW_TABLE as

select

ITEM 1

,ITEM 2

,DATE_1 format=ddmmyys10.

,DATE_2 format=ddmmyys10.

FROM WORK.OUTPUT;

QUIT;

 

Anyone able to help?

Esteemed Advisor
Posts: 5,198

Re: PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ???

The Oracle standard display format is an issue for Oracle. if you need to change that, talk to your Oracle DBA.

Why the concern, is the table directly available for end user reporting?

If you have a Metadata server, you can register the desired format there, and then it will be used if the user are using the META engine.

Data never sleeps
New Contributor
Posts: 3

Re: PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ???

Thanks for the reply.

 

Yeah the Oracle table is used for end reporting with the date output desired/required in dd/mm/yyyy format.

 

Was hoping it would be an easy fix I could do in the output from SAS.

 

I'll see what our DBA says then, it'll probably only take 2 weeks for them to get back to me lol.... Smiley Happy

 

Cheers

Esteemed Advisor
Posts: 5,198

Re: PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ???

It would be an easy fix if you had a metadata server....Smiley Wink

Another quickie would be to use a SAS view on top of the Oracle table. Not ideal, but it would work.

Data never sleeps
Frequent Contributor
Posts: 81

Re: PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ???

I've had lots of problems with Oracle-SAS data type as well, although I'm usually going the other direction, from Oracle to SAS, and date/time variables are always the trickiest. One thing I've noticed that may be helpful is that there seems to be a big difference in the interchange between Oracle and SAS depending on if the variable is a DATE, TIME or DATETIME in SAS or if it's a DATE, TIMESTAMP or TIMESTAMP WITH TIME ZONE in Oracle.  It looks like below it's using a SAS DATE format, but if it's not too much of a hassle it may be worth converting it to a DATETIME and see what Oracle makes of it on the upload.  

 

One other thing to consider, if on upload the SAS DATE variables are DATE or TIMESTAMP values rather than a VARCHAR2, is that you may be able to change the schema parameters in Oracle to alter the default DATE/TIMESTAMP format it displays.  I don't recall how to do this off the top of my head, but I don't think it should be too hard to find.

New Contributor
Posts: 3

Re: PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ???

Thanks for the info.

 

Dates/times are a royal faff between systems aren't they!

 

Unfortunately as it's SAS which is dropping/creating the table each time (we have an easy prompt-run EGP which pulls data, formats, adds info then drops/re-writes a table), it means getting Oracle to do any sort of converting/formatting is off the menu.

 

Appreciate all the suggestions but given this scenario, if I can't get SAS to do this via PROC SQL then I think I'll have to just live with it and remember to reformat everything when we use the table for producing reports!

 

Thanks for all the suggestions info though everyone!

Ask a Question
Discussion stats
  • 5 replies
  • 123 views
  • 0 likes
  • 3 in conversation