Tuesday, December 6, 2011

Proc SQL: Using Joins in an Update Statement

I ran into simple use case the other day -- with a surprising outcome. There was a parent and a child table, and I wanted to use an UPDATE query in PROC SQL to update one field of all parent records linked to a child record with a certain value.

In MS Access, the following worked just fine:
UPDATE tblParent INNER JOIN tblChild ON tblParent.pID = tblChild.pID
SET tblParent.field = "updated" WHERE (((tblChild.yesno)=1)) ;

The same statement did not work in SAS. Apparently, PROC SQL does not allow joins in the beginning of an UPDATE statement:
9    PROC SQL;
10   UPDATE tblParent JOIN tblChild ON tblParent.pID = tblChild.pID
                      ----
                      22
                      76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, AS, SET.
ERROR 76-322: Syntax error, statement will be ignored.
11   SET tblParent.field = "updated" WHERE (((tblChild.yesno)=1));
12   QUIT;

In the end, I had to use a subquery. It was probably less efficient than a join, but my table wasn't large enough for it to be a big deal. The solution:
PROC SQL;
   UPDATE tblParent A
   SET field = (SELECT DISTINCT "updated" FROM tblChild B WHERE B.pID = A.pID AND B.yesno=1);
QUIT;

If anyone has a better solution to this or some sort of workaround, please post a comment below! I tried searching Google for a better solution but did not find one.