topic Re: how to truncate table in SAS Enterprise Guide

how to truncate table

Pato485 — Wed, 11 May 2022 10:06:17 GMT

How can i truncate table, this is not working for me:

Proc sql;
truncate table sasdata.barkoci;
run;

Thanks.

Re: how to truncate table

Patrick — Wed, 11 May 2022 11:11:34 GMT

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;

Re: how to truncate table

AMSAS — Wed, 11 May 2022 12:02:29 GMT

See the following post by @Patrick, who makes a good point about using the PROC SQL Delete approach.

I'd go with @Patrick approach, but if you want to use SQL:

data have ;
	do group=1 to 3 ;
		do members=1 to int(ranuni(0)*10) ;
			output ;
		end ;
	end ;
run ;

proc sql ;	
	delete * from have ;
quit ;

Re: how to truncate table

Patrick — Wed, 11 May 2022 11:58:32 GMT

@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;

Re: how to truncate table

JuSAS — Wed, 10 May 2023 12:22:36 GMT

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!

Re: how to truncate table

Tom — Wed, 10 May 2023 12:50:30 GMT

@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** .