DATA Step, Macro, Functions and more

Create table statement overwritting first table

Reply
Occasional Contributor
Posts: 11

Create table statement overwritting first table

 

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?

Super User
Posts: 10,623

Re: Create table statement overwritting first table

Posted in reply to rashid3m0

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 11

Re: Create table statement overwritting first table

Posted in reply to KurtBremser

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.

Super User
Super User
Posts: 9,866

Re: Create table statement overwritting first table

Posted in reply to rashid3m0

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.

Super User
Posts: 10,623

Re: Create table statement overwritting first table

Posted in reply to rashid3m0

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Esteemed Advisor
Posts: 5,627

Re: Create table statement overwritting first table

Posted in reply to rashid3m0

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.

 

PG
Super User
Posts: 24,026

Re: Create table statement overwritting first table

Posted in reply to rashid3m0

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;
Ask a Question
Discussion stats
  • 6 replies
  • 138 views
  • 0 likes
  • 5 in conversation