topic Re: PROC SQL Oracle Table Build - Date formatting to ddmmyys10. ??? in SAS Enterprise Guide

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

TarquinKrikery — Tue, 14 Feb 2017 11:42:26 GMT

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?

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

LinusH — Tue, 14 Feb 2017 12:14:16 GMT

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.

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

TarquinKrikery — Tue, 14 Feb 2017 12:18:28 GMT

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

Cheers

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

LinusH — Tue, 14 Feb 2017 12:22:24 GMT

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

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

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

Sven111 — Thu, 16 Feb 2017 23:37:34 GMT

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.

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

TarquinKrikery — Fri, 17 Feb 2017 09:44:42 GMT

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!