Deleting rows in SAS DIS

[May15]

Hi @JBailey,

Many thanks for sharing your code.

One question, why do you say it doesn't help me? Actually, the process I mentioned works in order to make the file smaller: I've tested it. To replace the original table with the data I want, the table loader transformation in SAS DIS generates a proq sql with the CREATE TABLE AS statement and, as you also checked in your code, it saves space. The issue, as I said above, is when I have referential integrity constraints. Thus, I'd like to know what I could change in this kind of process in DIS to eliminate the errors I mentioned above. Any help will be appreciated.

@mkeintz, thanks for your point of view.

Thanks,

May15

[JBailey]

Hi @May15

I meant it doesn't help with INSERTing back into the original table. For it to work the original table (really a file) must be replaced with a new version. 

In order to do this on a table with RI ,the RI must be dropped prior to the maintenance then reapplied afterward.

Best wishes,

Jeff

[mkeintz]

FWIW, here's a little script to tell you how much disk space you might recover via PROC COPY, etc..  Perhaps DIS already has this capability via some other instruction, but my familiarity is with Base SAS:

data cars;
  set sashelp.cars;
run;

data cars;
  modify cars;
  if mod(_n_,10)=3 then remove;
run;

proc sql;
  select nobs
        ,nlobs
        ,nobs-nlobs as removed_obs
        ,calculated removed_obs/nobs as proportion_removed
        ,obslen
        ,obslen * calculated removed_obs as potential_disk_savings
  from dictionary.tables
    where libname='WORK' and memname='CARS';
quit;

[LinusH]

Is it just me, but all this sounds a bit awkward.
First, what is the delete ratio? How often do you delete?
I usually don't come across this kind of situation when working on governed data like you seem to have with constraints and all. In my work you keep all the data, and only reason more deletions are archiving (seldom perhaps once a year, monthly at the most), or manual data patching of a misload.
If you still have step g requirements for this kind of operations Base SAS may not be the best match for storage.