06-22-2017 05:36 PM
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:
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
06-23-2017 11:23 AM
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.
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.
07-04-2017 12:59 PM
06-22-2017 06:46 PM
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).
06-23-2017 11:07 AM
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.
07-04-2017 01:14 PM
07-04-2017 01:28 PM
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.