How can i truncate table, this is not working for me:
Proc sql;
truncate table sasdata.barkoci;
run;
Thanks.
If there aren't any indexes on the table then the quickest way is to recreate it.
data sasdata.barkoci;
stop;
set sasdata.barkoci;
run;
or...
data sasdata.barkoci;
set sasdata.barkoci(obs=0);
stop;
run;
@AMSAS You might know already that but posting this here anyway.
The issue with a SQL Delete on SAS files is that the delete is only logical. It doesn't reduce the size of the SAS file.
data work.class;
set sashelp.class;
run;
proc sql;
delete from work.class;
quit;
proc contents data=work.class;
run;quit;
If you really want to mimic a SQL Truncate also maintaining all the table attributes, indexes and constraints then you have to do something "ugly" as below.
data work.class(index=(name));
set sashelp.class;
run;
%let sv_obs=%sysfunc(getoption(obs,keyword));
%let sv_dlcreatedir=%sysfunc(getoption(dlcreatedir));
options obs=0 dlcreatedir;
libname tmp_work "%sysfunc(pathname(work))/tmp_work";
proc datasets lib=work nolist;
copy
in=work
out=tmp_work
clone
datecopy
force
index=yes
constraint=yes
;
select class;
run;
delete class;
run;
copy
in=tmp_work
out=work
move
;
select class;
run;
quit;
/* restore options */
options &sv_obs &sv_dlcreatedir;
libname tmp_work clear;
proc contents data=work.class;
run;quit;
I, I would strongly recommend to NOT use the delete statement to empty a table, because it has poor performances for that... For a dataset with few records, this is fine, but as soon as you deal with thousands of records, it will take ages just to empty your dataset.
For me, the best approach in SAS - which is also fully compatible with proc sql is the following:
create table XX like XX;
Or, for a better example:
data work.class; set sashelp.class; run; proc sql noprint; create table class like class; quit;
Short and efficient!
@Pato485 wrote:
How can i truncate table, this is not working for me:
Proc sql;
truncate table sasdata.barkoci;
run;
Thanks.
Don't.
That is for database systems where making a new table is major Pain In The A** .
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: