LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (December 2011, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:   Mon, 5 Dec 2011 12:24:05 -0800
Reply-To:   "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Sender:   "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:   "Huang, Ya" <Ya.Huang@AMYLIN.COM>
Subject:   Re: Oracle SQL pass through to_date again
Comments:   To: "DUELL, BOB" <bd9439@att.com>
In-Reply-To:   <AC5F423B9AB19C43BE62C86BAF8113CC1919C4@WABOTH9MSGUSR8C.ITServices.sbc.com>
Content-Type:   text/plain; charset="us-ascii"

Thanks,

Now I know it is a character field:

Obs COLUMN_NAME TYPE LENGTH NULLABL

1 CASE_ID VARCHAR2 500 N 8 DATE_COMPLETED VARCHAR2 8 Y

I also found that date_completed could have partial date like '2007', '200710' etc.

So, what the best way to select the records based on such a character column and a specified time period?

-----Original Message----- From: DUELL, BOB [mailto:bd9439@att.com] Sent: Monday, December 05, 2011 12:10 PM To: Huang, Ya; SAS-L@LISTSERV.UGA.EDU Subject: RE: Re: Oracle SQL pass through to_date again

Hmmm, it sounds like "date_completed" is not a data column. A quick way to check the table structure is to look at the Oracle metadata. Try running this to see all the columns:

proc sql; CONNECT TO ORACLE (your login info); create table temp as select * from connection to oracle ( select COLUMN_NAME, DATA_TYPE, DATA_LENGTH, NULLABLE from ALL_TAB_COLUMNS where TABLE_NAME='PC_INVESTIGATIONS' order by column_id ); quit;

If you do not have access to ALL_TAB_COLUMNS, ask your friendly neighborhood DBA for help.

Bob

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Huang, Ya Sent: Monday, December 05, 2011 10:48 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: Oracle SQL pass through to_date again

I actually don't know what it is in Oracle. If I just download the data (libname engine), I can see it in SAS as a string like '20071231'. So, could it be that it is actually a string in Oracle?

-----Original Message----- From: Bolotin Yevgeniy [mailto:YBolotin@schools.nyc.gov] Sent: Monday, December 05, 2011 10:45 AM To: Huang, Ya Subject: RE: Re: Oracle SQL pass through to_date again

b.date_completed is a date variable and not a string, right? you might have invalid data in it

-----Original Message----- From: Ya Huang [mailto:ya.huang@AMYLIN.COM] Sent: Monday, December 05, 2011 1:43 PM To: SAS-L@LISTSERV.UGA.EDU; Bolotin Yevgeniy Cc: Ya Huang Subject: Re: Oracle SQL pass through to_date again

Yes, it works. It also works if I just treat it as string:

b.date_completed between '20071231' and '2008-01-06'

But the query result seems wrong (with much less records then I expected)

On Mon, 5 Dec 2011 13:39:36 -0500, Bolotin Yevgeniy <YBolotin@SCHOOLS.NYC.GOV> wrote:

>Just as a sanity check - can you remove the BETWEEN condition entirely >(along with the dates) and run the query again? > >If you get the same error again, must be something else wrong. > > >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of >Huang, Ya >Sent: Monday, December 05, 2011 1:11 PM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Re: Oracle SQL pass through to_date again > >Same message: > >b.date_completed between date '2007-12-31' and date >'2008-01-06' ) >78 ) >79 ; >ERROR: ORACLE execute error: ORA-01861: literal does not match format >string. > >-----Original Message----- >From: DUELL, BOB [mailto:bd9439@att.com] >Sent: Monday, December 05, 2011 10:05 AM >To: Huang, Ya; SAS-L@LISTSERV.UGA.EDU >Subject: RE: Oracle SQL pass through to_date again > >Try changing the != symbol to <>. Also, you can avoid the TO_DATE >function by using an ANSI date string. In other words: > >%macro aepccase; >(select > a.case_id, > a.delete_flag, > a.prod_cd, > a.adverse_event_flag, > a.product_complaint_flag, > a.case_create_dt, > b.date_completed > from &schema..ae_cases a, > &schema..pc_investigations b > where a.case_id = b.case_id and > (upper(a.delete_flag) <> 'Y' or a.delete_flag is null) and > upper(a.prod_cd) in ('XXX','YYY') and > upper(a.ADVERSE_EVENT_FLAG)='Y' and > upper(a.PRODUCT_COMPLAINT_FLAG)='Y' and > b.date_completed between date '2007-12-31' and > date '2008-01-06' >) >%mend aepccase; > >-----Original Message----- >From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Ya >Huang >Sent: Monday, December 05, 2011 9:43 AM >To: SAS-L@LISTSERV.UGA.EDU >Subject: Re: Oracle SQL pass through to_date again > >Tried and still not working. Besides, for testing, those date are really >hardcoded in the marco: > >%macro aepccase; >(select > a.case_id, > a.delete_flag, > a.prod_cd, > a.adverse_event_flag, > a.product_complaint_flag, > a.case_create_dt, > b.date_completed > from &schema..ae_cases a, > &schema..pc_investigations b > where a.case_id = b.case_id and > (upper(a.delete_flag) !='Y' or a.delete_flag is null) and > upper(a.prod_cd) in ('XXX','YYY') and > upper(a.ADVERSE_EVENT_FLAG)='Y' and > upper(a.PRODUCT_COMPLAINT_FLAG)='Y' and > b.date_completed between to_date('20071231', 'YYYYMMDD') and > to_date('20080106', 'YYYYMMDD') >) >%mend aepccase; > >So, the quoting for 20071213 is no difference from all the other >single quote here. > > >On Mon, 5 Dec 2011 11:30:44 -0600, Joe Matise <snoopy369@GMAIL.COM> >wrote: > >>Macro quoting issue maybe? Does it work if you paste in the %aepccase >>text directly? I checked that your general syntax should work: >> >>with b as (select to_date('20080105','YYYYMMDD') as date_completed, 1 >>as id from dual), >> a as (select 1 as id from dual) >> select * from a,b where a.id = b.id and b.date_completed between >>to_date('20071231', >>'YYYYMMDD') and to_date('20080106', 'YYYYMMDD') >> >>returns >>ID DATE_COMPLETED ID >>---------------------- ------------------------- ---------------------- >>1 05-JAN-08 1 >> >>-Joe >> >>On Mon, Dec 5, 2011 at 11:23 AM, Ya Huang <ya.huang@amylin.com> wrote: >>> I thought it would work too, but somehow it didn't. There is nothing >>> leading to this, this is the first step: >>> >>> 75 proc sql; >>> 76 connect to oracle(path=&path user=&userid pass=&pwd >>> dbmax_text=25000); >>> 77 create table here.aepccase as >>> 78 select * >>> 79 from connection to oracle >>> 80 (select * >>> 81 from %aepccase >>> MPRINT(AEPCCASE): (select a.case_id, a.delete_flag, a.prod_cd, >>> a.adverse_event_flag, a.product_complaint_flag, a.case_create_dt, >>> b.date_completed from aers.ae_cases a, aers.pc_investigations b where >>> a.case_id = b.case_id and (upper(a.delete_flag) !='Y' or >>> a.delete_flag is null) and upper(a.prod_cd) in ('XXX','YYY') >>> and upper(a.ADVERSE_EVENT_FLAG)='Y' and >upper(a.PRODUCT_COMPLAINT_FLAG) >>> ='Y' and b.date_completed between to_date('20071231', >>> 'YYYYMMDD') and to_date('20080106', 'YYYYMMDD') ) >>> 82 ) >>> 83 ; >>> ERROR: ORACLE execute error: ORA-01861: literal does not match format >>> string. >>> NOTE: Table HERE.AEPCCASE created, with 0 rows and 7 columns. >>> >>> >>> Any idea? >>> >>> >>> On Mon, 5 Dec 2011 11:12:51 -0600, Joe Matise <snoopy369@GMAIL.COM> >wrote: >>> >>>>Looks fine to me; see >>>>http://infolab.stanford.edu/~ullman/fcdb/oracle/or-time.html for >>>>example. Works fine for me also (Oracle 10g): >>>> >>>>select to_date('20071231', 'YYYYMMDD') from dual >>>> >>>>returns >>>> >>>>TO_DATE('20071231','YYYYMMDD') >>>>------------------------- >>>>31-DEC-07 >>>> >>>>Maybe you have something else wrong [missing semicolon type problem] >>>>that is leading to this error? >>>> >>>>-Joe >>>> >>>>On Mon, Dec 5, 2011 at 11:02 AM, Ya Huang <ya.huang@amylin.com> >wrote: >>>>> Hi there, >>>>> >>>>> What should I use to marsk the date format: >>>>> >>>>> ORA-01861: literal does not match format string was caused by this >line >>>>> >>>>> to_date('20071231', 'YYYYMMDD') >>>>> >>>>> I tried YYYY/MM/DD and failed too. >>>>> >>>>> Thanks >>>>> >>>>> Ya


Back to: Top of message | Previous page | Main SAS-L page