01-29-2018 07:27 AM
create table fruit
quit; proc sql;
create table fruit
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?
01-29-2018 07:32 AM
01-29-2018 08:13 AM
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
I am looing for as describe above.
01-29-2018 08:59 AM
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.
01-29-2018 09:33 AM
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.
01-29-2018 03:34 PM
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.
01-29-2018 11:32 AM
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.