topic Re: Create table statement overwritting first table in SAS Programming

Create table statement overwritting first table

rashid3m0 — Mon, 29 Jan 2018 12:27:50 GMT

proc sql; 
create table fruit 
(
name VARCHAR(20), 
owner VARCHAR(20), 
species VARCHAR(20), 
sex CHAR(1), 
birth DATE 
)
; 

quit;

proc sql; 
create table fruit 
(
species VARCHAR(20), 
sex CHAR(1), 
birth DATE 
) 
;

quit;

Hi all,

I am trying to create table by CREATE TABLE sql statement. but it is not checking the table name weather there is another table or not. it is just over writing the existing table. could you please help?

Re: Create table statement overwritting first table

Kurt_Bremser — Mon, 29 Jan 2018 12:32:28 GMT

Works as designed and documented. Use a new name if you don't want to overwrite the dataset.

Re: Create table statement overwritting first table

rashid3m0 — Mon, 29 Jan 2018 13:13:29 GMT

Thanks for your suggestion. I can do that. But I don't want to overwrite by mistake. For example MySQL support as follows to prevent duplication of table name

CREAT TABLE IF NOT EXISTS fruit

(

name varchar(20),

)

;

I am looing for as describe above.

Re: Create table statement overwritting first table

RW9 — Mon, 29 Jan 2018 13:59:09 GMT

The question really is what is it your trying to do which would require such a thing? Lets put it another way, you have written code before this part which does some work, so why do you not know if there will be some output from this code you created? There are only two circumstances where you would need to create an empty table (and neither would care if the file already exists):

You want to create template to ensure an output table is as you expect it

You are going to use SQL syntax and insert data into the empty table

So at no point would you need such a syntax. I suspect your trying to do things using the thinking from another system, rather than using SAS methodology to do your logic. Perhaps explain what it is your doing, providing explanations would help.

Re: Create table statement overwritting first table

Kurt_Bremser — Mon, 29 Jan 2018 14:33:19 GMT

If you need to find out if a table already exists, SAS provides the exist() datastep function. Use it to set a macro variable, and you can then run code conditionally depending on that.

Re: Create table statement overwritting first table

Reeza — Mon, 29 Jan 2018 16:32:25 GMT

SAS has the REPLACE/NOREPLACE option.

If you want a warning before you replace any table or a requirement to delete tables before replacing switch your option to NOREPLACE.

You can also set a password on a data set to prevent overwriting using PROC DATASETS.

option noreplace;

Re: Create table statement overwritting first table

PGStats — Mon, 29 Jan 2018 20:34:33 GMT

Sometimes, doing simple things is complicated in SAS. Using SQL, you could do:

%macro createFruit;
proc sql noprint;
select count(*) into :cmd 
from dictionary.tables
where libname="WORK" and upcase(memname) = "FRUIT";
%if not &cmd %then %do;
create table fruit (name char(20));
%end;
quit;
%mend createFruit;
%createFruit;

Note that option noreplace has no effect in the WORK Library.