DATA Step, Macro, Functions and more

How i delete physical data from sas data set which was already deleted logically ?

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How i delete physical data from sas data set which was already deleted logically ?

I have been deleting records logically from one dataset , when i checked size of dataset is remains always same after deletion of records also .

 

Query used :- 

 

proc sql;

delete from ADFBASE.advdep_final_sas1;

quit;

 

Result :- 

NOTE: 57589 rows were deleted from ADFBASE.ADVDEP_FINAL_SAS1.

 

But when i checked size of ADFBASE.ADVDEP_FINAL_SAS1 in server , it is still same after deletion of 57589 records.

 

we are using same code for many dataset and it is just deleting data only Logically not physically.

I want to delete the records which are already deleted so that it will free size for that dataset.

 

How to delete records which were already deleted logically ?


Accepted Solutions
Solution
‎12-30-2016 01:24 AM
Trusted Advisor
Posts: 1,401

Re: How i delete physical data from sas data set which was already deleted logically ?

you need just copy it to itself:

 

data ADFBASE.ADVDEP_FINAL_SAS1;

  set ADFBASE.ADVDEP_FINAL_SAS1;

run;

 

the logical deleted observations will not be copied.

View solution in original post


All Replies
Super User
Posts: 17,912

Re: How i delete physical data from sas data set which was already deleted logically ?

Do you want to delete all records or remove the table entirely? 

Contributor
Posts: 36

Re: How i delete physical data from sas data set which was already deleted logically ?

I want to remove some records physically  which is already removed from dataset using below query:-

 

proc sql ;

 

delete from advdep_final_sas1;

 

quit;

 

after using above query the size of datset remains same that means it is not deleting records from dataset physically.

 

How to remove records from dataset which was deleted previouslly ? 

Solution
‎12-30-2016 01:24 AM
Trusted Advisor
Posts: 1,401

Re: How i delete physical data from sas data set which was already deleted logically ?

you need just copy it to itself:

 

data ADFBASE.ADVDEP_FINAL_SAS1;

  set ADFBASE.ADVDEP_FINAL_SAS1;

run;

 

the logical deleted observations will not be copied.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 240 views
  • 0 likes
  • 3 in conversation