5

SAS Beginner here.

INTRO: In SQL Server Management Studio an error is thrown when you try to CREATE an object/table that already exists. So the solution is to first DROP the table and then CREATE it.

So how come in SAS I can write something like this... (pretending that the Example table exists)

PROC SQL;
    CREATE TABLE Example AS
        SELECT *
        FROM Work.Test;
QUIT;

QUESTION: Even though the Example object/table already exists, no error is thrown. Does anyone know why SAS or SQL Server Management Studio are different in this regard?

frango_mints
  • 59
  • 1
  • 5

1 Answers1

7

SAS has a default OPTION REPLACE, which tells SAS that you would like to allow this behavior.

If you wish to disable this feature, set OPTION NOREPLACE. As the documentation above states, this would not prevent the above from executing, however, because it is in the work library (which is a temporary library).

As to the why (why things are different), it is undoubtedly due to historical differences in how the languages are used. SAS isn't really a database language (though it shares heavily with them, including the built-in PROC SQL); it's also substantially older than SQL Server (origins in the 1960s and even before in PL/1 and PL/2). SQL Server gives you lots of tools for updating a table in place; SAS has many of those tools, but for various reasons SAS programmers tend to replace tables rather than updating them in place.

Joe
  • 62,789
  • 6
  • 49
  • 67
  • 1
    I don't know if the OP is creating a table in work but it might be worth noting that the link to the documentation you've provided does say "This option has no effect on data sets in the WORK library...". – Amir Oct 30 '15 at 20:18
  • 1
    @Amir Good point; that should be explicit (though it seems obvious). Updated. – Joe Oct 30 '15 at 20:20