Date: Thu, 9 Sep 2010 14:46:43 -0500
Reply-To: Joe Matise <snoopy369@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Joe Matise <snoopy369@GMAIL.COM>
Subject: Re: CLI Fetch error Invalid datetime format
In-Reply-To: <3BE43274ACAB1D4DA40BA4270EA10F0F064ECD804D@WDCE7M004.internal.cigna.com>
Content-Type: text/plain; charset=ISO-8859-1
I would break it down into simple steps then (Assuming you can't get better
answers out of one of the experts):
First, does this work?
Create table all_member_diagnosis as
Select
MEMBDGIS.strtdt,
MEMBDGIS.end_dt,
MEMBDGIS.is_err
from prod.MEMBDGIS;
Does that get you a dataset with DT values?
Second, does this work?
Create table all_member_diagnosis as
Select
MEMBDGIS.strtdt,
MEMBDGIS.end_dt,
MEMBDGIS.is_err
from prod.MEMBDGIS (dbsastype=(end_dt='date9.'));
Does that give you a dataset with Date values?
If those both work, then the problem is the WHERE statement. I suspect it
is, personally - the error you report "invalid datetime format" suggests
that you're giving Oracle the command
SELECT * FROM TABLE WHERE ENDDT <= 18410
which is invalid in Oracle (and in any event wrong). You need
SELECT * FROM TABLE WHERE ENDDT <= to_Date('01/01/2010','mm/dd/yyyy')
or the actual oracle date value equivalent. You could also do this in a
data step, and use a subsetting IF rather than a WHERE, which would help in
that you know it's going in SAS, but it would be slower as it will fetch all
rows from Oracle first and then subset them. Alternately, create one table
with all values and then filter that into a second table fully in SAS (still
slow). Finally, it may be that there's a way to force SAS to process the
where clause in SAS - not sure, perhaps browse the SAS/ACCESS for ODBC
documentation, or the WHERE clause documentation in base SAS or PROC SQL.
If it's the DBSASTYPE that is the error (ie, the first table works, the
second doesn't), then you may need to adjust it somehow - either leave it as
a datetime and use a datetime value to filter your query, or change some
option that adjust show SAS interprets oracle dates.
By the way, this may be ultimately easier if you use pass-through SQL:
CONNECT TO ODBC (connection string stuff)
then
SELECT datepart(strtdt) as strtdt format=date9., datepart(end_dt) as end_dt
format=date9., is_err FROM CONNECTION TO ODBC (
select strtdt, end_Dt, is_err from MEMBDGIS where end_dt between
to_date('<startdate>','mm/dd/yyyy') and to_date('<enddate>','mm/dd/yyyy')
<more where stuff>
);
That might be safer (in that oracle code is oracle and sas code is sas) and
easier.
-Joe
On Thu, Sep 9, 2010 at 1:52 PM, Barkanic, Gema 1915 <Gema.Barkanic@cigna.com
> wrote:
> that was supposed to be the fix. It didn't work without it. I can't
> even do a proc freq on end_dt without getting the error
>
> ------------------------------
> *From:* Joe Matise [mailto:snoopy369@gmail.com]
> *Sent:* Thursday, September 09, 2010 2:50 PM
> *To:* Barkanic, Gema 1915
> *Cc:* SAS-L@listserv.uga.edu
>
> *Subject:* Re: CLI Fetch error Invalid datetime format
>
> My non-expert opinion: If I understand correctly, the WHERE clause may
> (sometimes) be processed ODBC-side (ie, oracle-side). So &date1 and &date2
> need to be oracle date literals, not SAS date literals. But that's just a
> guess. Art may also be correct that your DBSASTYPE could be causing a
> problem - if you take it out does it work as expected (just use a datetime
> in the WHERE instead of a date)? Try changing the where clause first, I'd
> suggest, maybe just remove it entirely and see if the select works without
> the where clause, and if that doesn't help then remove the dbsastype.
>
> -Joe
>
> On Thu, Sep 9, 2010 at 6:55 AM, Barkanic, Gema 1915 <
> Gema.Barkanic@cigna.com> wrote:
>
>> Here is the code that I am running on xp sp3:
>>
>> %let date1='01Jan2010'd;
>> %let date2='31aug2010'd;
>> Proc sql;
>> Create table all_member_diagnosis as
>> Select
>> MEMBDGIS.strtdt,
>> MEMBDGIS.end_dt,
>> MEMBDGIS.is_err
>> from prod.MEMBDGIS (dbsastype=(end_dt='date9.'))
>> where
>> ((MEMBDGIS.end_dt between &date1 and &date2) or
>> (MEMBDGIS.strtdt between &date1 and &date2) or
>> (MEMBDGIS.end_dt >= &date2 and MEMBDGIS.strtdt <= &date1) or
>> (MEMBDGIS.end_dt= . and MEMBDGIS.strtdt <= &date1));
>> quit;
>>
>>
>>
>> Here is the log:
>> 14 Proc sql;
>> 15 Create table all_member_diagnosis as
>> 16 Select
>> 17 MEMBDGIS.strtdt,
>> 18 MEMBDGIS.end_dt,
>> 19 MEMBDGIS.is_err
>> 20 from prod.MEMBDGIS (dbsastype=(end_dt='date9.'))
>> 21 where
>> 22 ((MEMBDGIS.end_dt between &date1 and &date2) or
>> SYMBOLGEN: Macro variable DATE1 resolves to '01Jan2010'd
>> SYMBOLGEN: Macro variable DATE2 resolves to '31aug2010'd
>> 23 (MEMBDGIS.strtdt between &date1 and &date2) or
>> SYMBOLGEN: Macro variable DATE1 resolves to '01Jan2010'd
>> SYMBOLGEN: Macro variable DATE2 resolves to '31aug2010'd
>> 24 (MEMBDGIS.end_dt >= &date2 and MEMBDGIS.strtdt <= &date1) or
>> SYMBOLGEN: Macro variable DATE2 resolves to '31aug2010'd
>> SYMBOLGEN: Macro variable DATE1 resolves to '01Jan2010'd
>> 25 (MEMBDGIS.end_dt= . and MEMBDGIS.strtdt <= &date1));
>> SYMBOLGEN: Macro variable DATE1 resolves to '01Jan2010'd
>> ERROR: CLI cursor extended fetch error: [Oracle][ODBC]Invalid datetime
>> format.
>> NOTE: Compressing data set WORK.ALL_MEMBER_DIAGNOSIS increased size by
>> 23.16 percent.
>> Compressed is 8981 pages; un-compressed would require 7292 pages.
>> NOTE: Table WORK.ALL_MEMBER_DIAGNOSIS created, with 1225000 rows and 3
>> columns.
>>
>> NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of
>> statements.
>> 26 quit;
>> NOTE: The SAS System stopped processing this step because of errors.
>> NOTE: PROCEDURE SQL used (Total process time):
>> real time 2:38.16
>> cpu time 4.42 seconds
>>
>>
>>
>>
>> -----Original Message-----
>> From: Robin Templer [mailto:templerr@clear.net.nz]
>> Sent: Thursday, September 09, 2010 2:46 AM
>> To: Barkanic, Gema 1915
>> Subject: RE: CLI Fetch error Invalid datetime format
>>
>> Do you have the full sas log ?? There are a lot of other options that may
>> or may not be relevant.
>>
>>
>> -----Original Message-----
>> From: Barkanic, Gema 1915 [mailto:Gema.Barkanic@CIGNA.com]
>> Sent: Thursday, 9 September 2010 2:01 p.m.
>> To: templerr@clear.net.nz
>> Subject: RE: CLI Fetch error Invalid datetime format
>>
>> I tried that and still got the same fetch error. I made sure my libname
>> was generated correctly
>>
>> -----Original Message-----
>> From: templerr@clear.net.nz [mailto:templerr@clear.net.nz]
>> Sent: Wednesday, September 08, 2010 9:34 PM
>> To: Barkanic, Gema 1915
>> Subject: Re: CLI Fetch error Invalid datetime format
>>
>> Gema
>> The magic option is STRINGDATES='YES' which goes on your LIBNAME
>> statement.
>> Be aware that this will turn all your DATE and TIME values as character
>> variables raher than Numerics - and you will have to manipulate them
>> yourself to convert them into numerics with appropriate formats/informats.
>>
>>
>>
>> ----- Original Message Follows -----
>> > I'm getting a CLI Fetch error whhile reading in an ODBC file. Is
>> > there a way to override this so I can at least read the data in. I
>> > know the problem is with a certain variable. Thanks, Gema
>> >
>> > This is the actual error I'm getting:
>> >
>> >
>> >
>> > ERROR: CLI cursor extended fetch error:
>> > [Oracle][ODBC]Invalid datetime format. NOTE: The data step has been
>> > abnormally terminated. NOTE: Missing values were generated as a result
>> > of performing an operation on missing values.
>> > Each place is given by: (Number of times) at (Line):(Column).
>> > 495288 at 17:11 3 at 18:13
>> > NOTE: The SAS System stopped processing this step because of errors.
>> > NOTE: There were 2373000 observations read from the data set
>> > PROD.MEMBDGIS. WARNING: The data set MAIN.MEMBERLEVEL may be
>> > incomplete. When this step was stopped there
>> > were 9160 observations and 49 variables.
>> > NOTE: Compressing data set MAIN.MEMBERLEVEL decreased size by 99.05
>> > percent.
>> > Compressed is 87 pages; un-compressed would require 9160 pages.
>> > WARNING: Data set MAIN.MEMBERLEVEL was not replaced because this step
>> > was stopped. NOTE: DATA statement used (Total process time):
>> > real time 10:33.34
>> > 2 The SAS System
>> > 17:52 Tuesday, September 7, 2010
>> >
>> > cpu time 1:54.86
>> >
>> >
>> >
>> > Gema Barkanic
>> > Health Data Specialist
>> > 412-747-7537
>> > 412- 473- 7694 (home office M and F)
>> > gema.barkanic@cigna.com
>> >
>> > aConfidential, unpublished property of CIGNA. Do not duplicate or
>> > distribute. Use and distribution limited solely to authorized
>> > personnel. (c) Copyright 2010 CIGNA
>> >
>> >
>> > ----------------------------------------------------------
>> > -------------------- CONFIDENTIALITY NOTICE: If you have received this
>> > email in error, please immediately notify the sender by e-mail at the
>> > address shown. This email transmission may contain confidential
>> > information. This information is intended only for the use of the
>> > individual(s) or entity to whom it is intended even if addressed
>> > incorrectly. Please delete it from your files if you are not the
>> > intended recipient. Thank you for your compliance. Copyright 2010
>> > CIGNA ==========================================================
>> > ====================
>>
>>
>>
>>
>> ----------------------------------------------------------------------------
>> --
>> CONFIDENTIALITY NOTICE: If you have received this email in error, please
>> immediately notify the sender by e-mail at the address shown. This email
>> transmission may contain confidential information. This information is
>> intended only for the use of the individual(s) or entity to whom it is
>> intended even if addressed incorrectly. Please delete it from your files if
>> you are not the intended recipient. Thank you for your compliance.
>> Copyright 2010 CIGNA
>>
>> ============================================================================
>> ==
>>
>>
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------------------
>> CONFIDENTIALITY NOTICE: If you have received this email in error, please
>> immediately notify the sender by e-mail at the address shown. This email
>> transmission may contain confidential information. This information is
>> intended only for the use of the individual(s) or entity to whom it is
>> intended even if addressed incorrectly. Please delete it from your files if
>> you are not the intended recipient. Thank you for your compliance.
>> Copyright 2010 CIGNA
>>
>> ==============================================================================
>>
>
>
> ------------------------------------------------------------------------------
>
> CONFIDENTIALITY NOTICE: If you have received this email in error, please
> immediately notify the sender by e-mail at the address shown. This email
> transmission may contain confidential information. This information is
> intended only for the use of the individual(s) or entity to whom it is
> intended even if addressed incorrectly. Please delete it from your files if
> you are not the intended recipient. Thank you for your compliance. Copyright
> 2010 CIGNA
>
> ==============================================================================
>
| 
