BookmarkSubscribeRSS Feed
Pato485
Calcite | Level 5

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

 

Proc sql;
truncate table sasdata.barkoci;
run;

 

Thanks.

5 REPLIES 5
Patrick
Opal | Level 21

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
SAS Super FREQ

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 ;

 

Patrick
Opal | Level 21

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

Patrick_0-1652269754523.png

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;

Patrick_1-1652270193072.png

 

JuSAS
Calcite | Level 5

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!

 
Tom
Super User Tom
Super User

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

 

Ready to join fellow brilliant minds for the SAS Hackathon?

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!
SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 7416 views
  • 1 like
  • 5 in conversation