PROC DELETE is a dedicated procedure in SAS v9.4 to perform this particular action.
"Drop" statement in PROC SQL is the SQL way of doing this.
Both of them will be faster from PROC DATASETS "delete" statement to delete one table from the SAS Library with eg. 10 000 tables/data stets. Explanation from PROC DELETE will also apply for PROC SQL for this case.
But which one out of them will be faster?
The answer isn't as easy it seems.
Below you can find SAS logs output from removing 16 GB data set on the Linux machine.
74? proc delete data=tmp.class_a;run; NOTE: Deleting TMP.CLASS_A (memtype=DATA). NOTE: PROCEDURE DELETE used (Total process time): real time 0.49 seconds user cpu time 0.00 seconds system cpu time 0.50 seconds memory 20.09k OS Memory 9380.00k Timestamp 07/02/2019 06:48:36 PM Step Count 217 Switch Count 0 Page Faults 0 Page Reclaims 9 Page Swaps 0 Voluntary Context Switches 0 Involuntary Context Switches 6 Block Input Operations 0 Block Output Operations 0 76? proc sql noprint; drop table tmp.class_a; quit; NOTE: Table TMP.CLASS_A has been dropped. NOTE: PROCEDURE SQL used (Total process time): real time 0.50 seconds user cpu time 0.00 seconds system cpu time 0.50 seconds memory 47.18k OS Memory 9380.00k Timestamp 07/02/2019 06:51:25 PM Step Count 255 Switch Count 0 Page Faults 15 Page Reclaims 56 Page Swaps 0 Voluntary Context Switches 16 Involuntary Context Switches 6 Block Input Operations 2520 Block Output Operations 0
So it seems that PROC DELETE will be "slightly" faster than PROC SQL "drop" and will be more memory efficient.
Let's dig a little deeper and verify how both of those approaches will look like from the VFS (The Linux Virtual File System) point of view. We will try to count VFS calls generated by the SAS process during those actions with funccount tool.
PROC DELETE also looks like more efficient in terms of number VFS calls what should translate to better performance. Whereas it will be a big difference that will depend on the scenario and underlying hardware.
So PROC DELETE should be a little bit faster due to the smaller number of IO calls :)