ERROR: ORACLE execute error: ORA-01830: date format picture ends before converting entire input string.

[skallamp]

I am getting ORA-01830 error while running the below code. I have defined the Yearfrom and Yearthru in my macro. I tried inputting my date with single quote and without it. But same error.

Does anyone know what it is? Any insights would be helpful.

%let yearmoFrom = '20150601';

%let yearmoThru = '20150630';

Proc SQL;

Create table one as

select a, b, c,d

from data

where EFF_DT <= to_date(&yearmoThru. || '15','yyyymmdd')

            and elg.MBR_TRMNTN_DT  >= to_date(&yearmoFrom. || '15','yyyymmdd')

;

quit;

[ballardw]

this code: &yearmoThru. || '15'  looks like would be attempting to  generate 2015063015 (ignoring all quotes)

This does not look like any match to yyymmdd

%let yearmoThru = 201506;

for the same value should generate 20150615, which makes a bit more sense.

[Tom · Accepted solution]

Since you have written SAS code (not ORACLE code) you cannot use TO_DATE() function.

Let's assume that you actually submitted that code to ORACLE in a pass thru SQL query.

Since your macro variables already contain 8 digits (20150601) when you are append two more ( || '15') you will have string that is 10 characters long.  But your format string ('yyymmdd') is only 8 characters long.  That is why you are getting the error message.

Either remove the || '15' or assign only 6 digits to the macro variable.

[jakarman]

Check the generate Oracle SQL. You are getting an Oracle error. TO_Date is an SAS function an a Oracle function it should be translated

SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Seventh Edition There is a warning associated with that. 

A more common pitfall is that Oracle is only knowing data-time approaches not the segregated date as SAS does.TO_DATE (Oracle)

[Patrick]

@Jaap

I wanted to write "to_date()" is not a SAS function. But then I checked just in case and actually the function exists as part of DS2 syntax.

BUT: to_date() is not a valid SAS function outside of DS2 so you can't use it with SAS SQL flavor.

[RW9]

Hi,

I thin you need to clarify your post.  Exactly where are you running this code.  If it is SAS SQL, then how are you getting an oracle error?  TO_DATE() is a valid Oracle function as described: http://www.techonthenet.com/oracle/functions/to_date.php

However to use that function, you need to pass your code through to the Oracle SQL parser (note you also need to place the macro variable in quotes so it gets resolved) - Note I assume the macro variable to look like 201504:

proc sql;

     connect to oracle (path=...);

     create table ONE as

     select * from connection to oracle (

          select   A,B,C,D

          from      DATA

          where   EFF_DT <= to_date("&yearmoThru." || '15','yyyymmdd')

              and   elg.MBR_TRMNTN_DT  >= to_date("&yearmoFrom." || '15','yyyymmdd'));

  disconnect from oracle;

quit;

[Patrick]

I believe @Tom is "spot on". You must be using Oracle pass-through SQL as else we wouldn't get this kind of Oracle error back. The way you populate the macro var and the format picture I believe the following should work (in pass-through SQL):

to_date(&yearmoThru.,'yyyymmdd')

[jakarman]

Patrick, you are right the to_date fucntion is no sas function.

Sorry I forgot  to check that one, you got me on that one. I got my head into DS2 but the strict language checkng I prefer to leave to the compiler not my head.

Rw9 made the clarification for that. (Sql pass through).    His reference to the oracle to_date function.

Some alerts:

- The double quotes is not valid withn ansi SQL for strings. The double quoates are for names using enhanced lterals and strings are only processed wiht singel quotes.

  That is mostly complicating the macro-var coding. Adding single quotes as one char is done with coding (a macro quoting challenge):  %nrstr(%')   

- The concatenation of strings OP original post, is something that looks from the sas-datastep processing. That will not work in sas-macro processing and not work in Oracle SQL.