Prev: Notes produced from proc mixed
Next: ODS Html Style Graph with SGPLOT in Excel Sheet
From: Cort on 20 Jan 2010 13:35
Good afternoon,

I have something that seems so simple. I've written a summarization
program that is being shared with many external partners. The data
can be in various formats behind the scenes. We have data in both SAS
formatted and in an Oracle database. One of my summaries is on a date
field. Quickly we realized that when you read data using the Oracle
engine, it comes back as datetime, which screws up the program because
the month() and year() functions don't work without throwing a datepart
() around it.

We found a way around it when we are using Oracle data by adding -

-set SASDATEFMT date

in the cfg file.

However, one of the partners have it in SQL server and they use the
ODBC engine. This doesn't seem to fix the issue as it did with the
ORACLE engine.

Is there some configuration that will force SAS to use datepart of all
date variables in SQL server.

Thanks in advance.
Cort
From: Gerhard Hellriegel on 20 Jan 2010 15:11
I cannot imagine that there is such a thing (option) in ODBC. Reason: the
datetime values as standard for date variables seems to be a ORACLE
speciality. But the source of ODBC can be another DB and I think there are
some DBs which have different date / timestamp representation.
Question is (and that is something far back in my brain...): isn't it
possible to format the date in ORACLE with a certain scheme? I remember a
kind of function where it is possible to tell ORACLE to show the datetime-
fields as yyyymmdd with certain delimiters (2009-01-23) or
even "23jan2009"? The function worked with 2 arguments, one the ORACLE-
date variable and the second the scheme how it is to be presented. If that
is possible, a view with that formatted date-values could be created and
in SAS that thing could be converted to a SAS date. For sure only for that
ODBC site.
Only a idea...
Gerhard




On Wed, 20 Jan 2010 10:35:21 -0800, Cort <hayflinger(a)GMAIL.COM> wrote:

>Good afternoon,
>
>I have something that seems so simple. I've written a summarization
>program that is being shared with many external partners. The data
>can be in various formats behind the scenes. We have data in both SAS
>formatted and in an Oracle database. One of my summaries is on a date
>field. Quickly we realized that when you read data using the Oracle
>engine, it comes back as datetime, which screws up the program because
>the month() and year() functions don't work without throwing a datepart
>() around it.
>
>We found a way around it when we are using Oracle data by adding -
>
> -set SASDATEFMT date
>
>in the cfg file.
>
>However, one of the partners have it in SQL server and they use the
>ODBC engine. This doesn't seem to fix the issue as it did with the
>ORACLE engine.
>
>Is there some configuration that will force SAS to use datepart of all
>date variables in SQL server.
>
>Thanks in advance.
>Cort
 | 
Pages: 1
Prev: Notes produced from proc mixed
Next: ODS Html Style Graph with SGPLOT in Excel Sheet