25 May

SAS: INSERT / VALUES

Originally, I’d planned this post to be about using CASE in an UPDATE.  However, strangely, unexpectedly,  it turned out that the big 3 all used the same syntax.  So, this will just be a quick overview of the differences between SAS and MySQL / SQL Server using INSERT.

Here’s some SAS code that creates a table, inserts some data and then updates it.

proc sql;
CREATE TABLE work.myjunk
(
	name CHAR(1),
	number INT
);
INSERT INTO work.myjunk VALUES ('A',1)
                        VALUES ('B',2)
                        VALUES ('C',3);
UPDATE work.myjunk
SET number =
CASE WHEN name = 'A' then 11
     WHEN name = 'B' then 22
	 ELSE 33
	 END;
SELECT * FROM work.myjunk;
quit;

name    number
--------------
A           11
B           22
C           33

There are two things worth noting in this code. First the INT data type. SAS accepts it, 9 others according to the Study Guide which it will then convert to one of the two SAS data types: number and character. In this case, INT is converted to a number.

I, finally, got smart and created a work database for MySQL and work schema for SQL Server.  It should help my code look a bit more consistent, even if work. means one thing in SAS, another in MySQL and still another in SQL Server.

The other thing, which is what led me to write this post, is that SAS uses a VALUES clause before each row in an INSERT. As you’ll see in the MySQL code (it’s the same with SQL Server) we only use a single VALUES clause.

mysql> CREATE TABLE work.myjunk
    -> (
    ->  name char(1),
    ->  number int
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO work.myjunk VALUES ('A',1),('B',2),('C',3);
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> UPDATE work.myjunk
    -> SET number =
    -> CASE WHEN name = 'A' then 11
    ->      WHEN name = 'B' then 22
    ->   ELSE 33
    ->   END;
Query OK, 3 rows affected (0.02 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM work.myjunk;
+------+--------+
| name | number |
+------+--------+
| A    |     11 |
| B    |     22 |
| C    |     33 |
+------+--------+
3 rows in set (0.00 sec)

Hopefully, this makes sense.  You use a single VALUES clause for SQL Server / MySQL and a VALUES clause for each row with SAS.  And, just be aware that you can use a CASE statement in an UPDATE.