ERROR: File XXXX is sequential. This task requires reading observations in a random order, but the engine allows only sequential access

Question

I am trying to update an oracle table.

I encounter this error when running the following data step:

data oracle.have(drop=_:);
    modify oracle.have end=last;
    if _n_=1 then do;
        declare hash h1(dataset:'_update');
        declare hiter hh1('h1');
        _rc = h1.defineKey('id','tid','valid_to');
        _rc = h1.defineData('valid_from');
        _rc = h1.defineDone();
    end;

    if h1.find()=0 then do;
        replace;
        _rc = h1.remove();
    end;
run;

ERROR: File ORACLE.HAVE.DATA is sequential. This task requires reading observations in a random order, but the engine allows only sequential access.

Is there any way to bypass this error ?

Answer 1

Apparently, modify statements only works with SAS data sets.

The workaround I use is the following:

proc sql;
update have t1
       set valid_from = (select valid_from from _update t2
                         where t1.id = t2.id
                            and t1.tid = t2.tid
                            and t1.valid_to = t2.valid_to
                            and t2.dim="FROM")
      where catx('#',id, tid, valid_to) in (select catx('#',id, tid, valid_to)
                                            from _update t3
                                            where t3.dim="FROM");
quit;