BookmarkSubscribeRSS Feed
TarquinKrikery
Calcite | Level 5

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?

5 REPLIES 5
LinusH
Tourmaline | Level 20

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
TarquinKrikery
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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
Sven111
Pyrite | Level 9

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.

TarquinKrikery
Calcite | Level 5

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!

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1391 views
  • 0 likes
  • 3 in conversation