BookmarkSubscribeRSS Feed
brulard
Pyrite | Level 9

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

 

 

 

7 REPLIES 7
kiranv_
Rhodochrosite | Level 12

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

ballardw
Super User

@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
Pyrite | Level 9
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
PROC Star

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
Super User

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
Pyrite | Level 9
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
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 7 replies
  • 1336 views
  • 4 likes
  • 5 in conversation