ERROR: CLI cursor fetch error

[Steve_Sr_Mrkt]

Received ERROR: CLI cursor fetch error ... The requested repeat query is
       not known to the server.  Please re-define. NOTE: The DATA step has been abnormally terminated.

I run a piece of code monthly and this code has been in use for 8 years.  This month I received this error.  

I got around the problem to finish my work by breaking the data step into 2 pieces instead of 1 single optimized code.  

There was a 'Datapart' aspect of the code that seems to that may have broken very recently.  Any info would be great.  Thanks!

[SASKiwi]

Posting errors without the code that caused them means there is little useful advice we can offer. I'm assuming here that this was a repeatable error, not just a one time problem. If you want some useful advice on the original code then rerun it and post the complete SAS log including any messages and errors.

[Steve_Sr_Mrkt]

data contact_info_website;
 format data_dt YYMMDDN8.;
set prd.fundsupp_stg (keep=data_dt company_key website
               rename=(data_dt = data_dt_ing
               company_key = comp_key));
        length company_key 6.;
        data_dt = datepart(data_dt_ing);
         drop data_dt_ing;
        if data_dt > '31-mar-2008'd;

        label comp_key = ' ';
        run;

[SASKiwi]

So does PRD LIBREF point to an external database? I'd suggest filtering your data with a WHERE statement so it gets applied in the database rather than the IF you currently have which filters once the data is in SAS:

data contact_info_website;
 format data_dt YYMMDDN8.;
 set prd.fundsupp_stg (keep=data_dt company_key website
               rename=(data_dt = data_dt_ing
               company_key = comp_key));
        length company_key 6.;
        where data_dt_ing > '31Mar2008:00:00:00'dt;
        data_dt = datepart(data_dt_ing);
        drop data_dt_ing;
        label comp_key = ' ';
run;

[Tom · Accepted solution]

How large is the dataset being read?  How many of the observations meet the subsetting criteria?

It is probably going to be faster to use WHERE to filter on the original datetime values instead of copying all of the observations and then excluding some of them.

Why did you tell SAS to create a new empty COMPANY_KEY?  Why did you tell SAS to only store 6 of the 8 bytes needed to store the 64 bit floating point numbers that SAS uses? (Since it is going to all be missing values I guess it doesn't really matter as there will be no data that could be impacted by the lose of precision.). 

I am also not sure why you forced the new date variable to be the first one.  Is that important? Why?  Also why are you displaying the date in that confusing way with 8 digits without any punctuation? Why not use YYMMDD10. to display the dates so it is more obvious it is date value and not some type of number or identifier?

If you going to remove the label from COMP_KEY why not also remove the label from WEBSITE?

data contact_info_website;
  set prd.fundsupp_stg (keep=data_dt company_key website);
  where date_dt > '31MAR2008:00:00:00'dt
  data_date = datepart(data_dt);
  format data_date YYMMDDN8.;
  drop data_dt;
  rename data_date=data_dt company_key=comp_key ;
  label company_key = ' ' website=' ';
run;

[Steve_Sr_Mrkt]

Hi Tom,

Thanks for your assistance, this code was written 8 years ago and just somehow worked until this month.  The dataset is not that large (213k rows), I ran the code with a changed input date to the recent month (27k rows) and it still threw the same CLI Cursor fetch error.  

I agree the 'where' statement would probably function better.  I did not write the code I'm just the analyst that runs it every month.  I think simply using the date structure you provided was the missing piece to make the code work as intended with minimal change.  Thank you!