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.
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.