Error sql when joining to date+1 "Date overflow while adding an integer."

[brulard]

hi,

I'm converting a report that contains multiple queries, into one big sql query.

The condition requires matching date from table a, to date in table B+1 day. No issue in original report:

Proc sql;
create table add2 as
Select b.bill_addr_line1,B.bill_addr_line2, B.bill_addr_line3, B.BILL_CITY,B.BILL_STATE_PROV_CD,B.BILL_ZIP_POST_CD,B.BILL_CNTRY_CD,a.*
From raw_data as a left join address_table as b on
a.id=b.id and a.event_dt=b.eff_to_dt+1;quit;

But, when when combining the queries into 6 joins, I receive the message: "CLI OPEN CURSOR ERROR:... Date overflow while adding an integer.

If anyone has a tip to offer, pls share, thank you

[kiranv_]

please check whether one of your b.eff_to_dt has value of Dec 31, 9999 somewhere, probably that might be causing this.

[ballardw]

---

@kiranv_ wrote:

please check whether one of your b.eff_to_dt has value of Dec 31, 9999 somewhere, probably that might be causing this.

---

Actually not in SAS with SAS date values. Arithimatic with data valued varaibles is just that and the range doesn't matter. Use of Date and Datetime related functions is another matter as the current limit appears to be  31 Dec 20000 or 31 Dec 19999 depending on the function requested.

Please consider:

data _null_;
   d = mdy(12,31,19999);
   put 'First put ' d= best10. d= date9.;
   d2 = d + 365;
   put 'Second put ' d2= best10. d2= date9.;
   d3 = intnx('year',d,1,'S');
   put 'Third put ' d3= best10. d3= date9.;
   d4 = mdy(12,31,20000);
   d5 = mdy(1,1,20001);
   put d4= d5=;
run;

Note SAS does not have a date format that displays 5 digit years for some reason 🙂 so you see **** in place of the year.

The intnx fuction doesn't want to increment a year past 31 Dec 19999 but MDY will accept 31 Dec 20000. I suspect another of the little known rules regarding leap years is involved.

[brulard]

thanks for your tip... Yeah, there are values of '31dec9999'd, but this never causes error when I run my original query (simple left join)

[Astounding]

Does your data contain any missing values for b.eff_to_dt

No promises that it would cause that type of error, but it's worth testing (I can't test it right now).

[ballardw]

Are you running this as pass through code to an external database?

Since you do not show use of any of the SAS date functions then SAS will not care about the actual value of the variables as a date when using date_variable+1 but I suspect that some external database might.

[brulard]

thanks for your response ballardw. I am not using passthrough (do you suggest that I try that method?). I am accessing to an external database via a library that loads through my autoexec when loading unix sas... No error message when coding as a single left join, only when combining multiple left joins... For it to work as multiple leftjoins, I coded :
ON f.eff_to_dt=today()-3 AND a.id=f.id

[Tom]

Your error message is not a SAS error message. Looks more like an Oracle error message.

Most likely your database is using maximum possible date to indicate a right censored value.

You will need to take that into consideration in your query.

So instead of 

on a.id=b.id and a.event_dt=b.eff_to_dt+1

you could use this to avoid the overflow

on a.id=b.id and a.event_dt-1=b.eff_to_dt

But to really account for those extreme values you probably want to use more complex logic.