SQL error using INSERT INTO to build a table

Question

I'm trying to put together a table in SAS Enterprise Guide using INSERT INTO using online tutorials but keep getting errors when I do the same thing as the tutorial. What could be going wrong?

My code:

/* Create the table */ 
   CREATE TABLE Summary_1920 (
      Variable VARCHAR(255), 
      Count INT, 
      Percentage FLOAT ); 
/* Create and insert the variable names */
   INSERT INTO Summary_1920 
      (Variable, Count, Percentage)
   VALUES 
      ("Variable1", 100, 90.8),
      ("Variable2", 8, 7.0);

QUIT;

The error:

28               ("IntName", 100, 90.8),
                                       _
                                       22
                                       200
ERROR 22-322: Syntax error, expecting one of the following: ;, VALUES.  

ERROR 200-322: The symbol is not recognized and will be ignored.

This is my first time using SAS EG/SQL so it's probably very obvious but I've been trying to fix this for ages. Thanks for any insight.

Gordon Linoff Gordon Linoff · Accepted Answer · 2021-03-24T11:26:45

Interesting. It looks like proc sql doesn't allow you to insert multiple rows using a single VALUES clause. So just use two statements:

INSERT INTO Summary_1920 (Variable, Count, Percentage)
    VALUES ("Variable1", 100, 90.8);

INSERT INTO Summary_1920 (Variable, Count, Percentage)
    VALUES ("Variable2", 8, 7.0);

I think it also supports multiple VALUES clauses in one statement:

INSERT INTO Summary_1920 (Variable, Count, Percentage)
    VALUES ("Variable1", 100, 90.8)
    VALUES ("Variable2", 8, 7.0);

This is not SQL standard syntax and I'm not familiar with any other database that supports it. Your version with commas is standard -- or at least supported by most databases.

SQL error using INSERT INTO to build a table

2 Answers