BookmarkSubscribeRSS Feed
rashid3m0
Calcite | Level 5

 

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?

6 REPLIES 6
rashid3m0
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Kurt_Bremser
Super User

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.

PGStats
Opal | Level 21

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
Reeza
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 8318 views
  • 2 likes
  • 5 in conversation