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
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
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.