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
please check whether one of your b.eff_to_dt has value of Dec 31, 9999 somewhere, probably that might be causing this.
@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.
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).
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.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.