SAS procedures Delete and Datasets

Question

I'm just learning sas and see two interesting procedures.

  proc Delete data = table;  run;

and

  proc datasets lib=Libr nolist;
        modify table;
           rename __first = second;
  quit;
  run;

and several questions about them:

why some procedures ended like quit not run?
why datasets use quit and run together? (is this a statement to quit table?)
is it best recommendation to use datasets-procedure for small tasks? (not, of course, but for what? or doesn't use it?)
and also, which method is faster: proc delete or sql-drop? (which have a greater speed and on which amount of data it is necessary)

score 7 · Accepted Answer · edited Mar 15 '18 at 10:44

Some SAS procedures end in QUIT, rather than RUN, because they are running in Interactive Mode. For instance, in PROC GLM, you can specify an additional model statement as long as the top of the SAS window says that PROC GLM is running (if you're using the Windows version).
Some programmers have gotten into the habit of typing QUIT and RUN together. I don't think it actually matters, as procedures that use the QUIT statement begin running as soon as you enter them. I only use one or the other.
PROC DELETE is an unsupported legacy feature; it has been officially superseded by PROC DATASETS, which is the designated tool for handling datasets in SAS. Mailing List Post.

I generally don't find myself with much need to delete datasets while in SAS. Because SAS effectively manages its memory use, and because RAM is so plentiful now, I usually do 90% of my work from temporary datasets which I create on-demand at the start of the session.

As before, PROC DELETE is now deprecated in favor of PROC DATASETS. In terms of which is faster, excluding extremely large data, I would wager that there is little difference between them. When handling permanent SAS datasets, however, I like to use PROC DATASETS rather than PROC SQL, simply because I feel better manipulating permanent datasets using the SAS-designed method, and not the SQL implementation (which isn't 100%) in my opinion.

There are a few things one can do in SQL that one cannot do in PROC SQL. The one that immediately comes to mind is changing the type of a variable. — TARehman, Apr 18 '12 at 15:55
Ah, I read it as `drop table` wasn't 100% in the SQL implementation. — DataParadigms, Apr 18 '12 at 16:21

score 3 · Answer 2 · answered Apr 18 '12 at 19:28

WRT "run" versus "quit":

Some SAS procedures support something called "run group processing", which means that the procedure performs whatever work it is asked to do when it sees the "run;" statement. The procedure continues to execute until it sees a "quit:" statemnet. In fact, a "quit;" statement will automatically insert a "run;" statement if there is still work to be done.

PROC DATASETS is one of those procedures. The "quit;" statement says that there is no more work for the procedure to do. Consider this trivial example:

proc datasets;
   change a=new_a;
run;
   delete new_a;
run;
quit;

The first statement (change) renames an existing dataset "a" to "new_a". The second statement will delete that dataset. If you fail to include a "run;" statement (after "change") in this example, the procedure will fail because it will notice that the "new_a" dataset does not exist and so will not execute either statement.

That said, I rarely use PROC DATASETS myself; I prefer to use PROC SQL.

WRT: PROC DELETE versus DROP TABLE with PROC SQL:

Although PROC DELETE is officially "deprecated", all that means is that it will no longer be changed. It is a simple procedure to delete a data object from a SAS library; I use it all the time. It has one special advantage compared to PROC SQL. If you use PROC DELETE to try and delete a dataset that does not exist, you will just get a warning message in the log. However, if you try a DROP TABLE statement from SQL, you will get an error and your SQL step will halt. I use PROC DELETE all the time when creating new tables in my ETL scripts that load into external databases like Teradata and Oracle.

Long-winded, I know, but I hope this helps.

Bob

score 2 · Answer 3 · edited May 23 '17 at 12:33

2

Regarding whether datasets or sql is faster at deleting tables, I investigated that issue a bit here. Proc SQL was generally faster, which was surprising to me.

edited May 23 '17 at 12:33

Community

1
1

answered Apr 18 '12 at 00:33

cmjohns

4,465
17
21

score 0 · Answer 4 · answered Jul 12 '12 at 07:02

0

I think the following code can delete the SAS datasets in the work library;

proc datasets lib=work memtype=data kill; run;

quit;

answered Jul 12 '12 at 07:02

user1519495

71
2

score 0 · Answer 5 · answered Jan 09 '17 at 19:35

I believe you will find that PROC DELETE has not gone away and will not anytime soon. Moreover, it is often faster than the PROC DATASETS ... DELETE form of deletion for some types of libraries. In my experience, data libraries managed by SPDS with many datasets can cause PROC DATASETS of any kind to have very poor performance, and so I will always use PROC DELETE.

SAS procedures Delete and Datasets

5 Answers5