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.

PROC SQL: Select Values into Macro Variables

In SAS, it is possible to select/save values into macro variables within PROC SQL. Here are some examples:

One Variable (Summarized)
PROC SQL;
    SELECT SUM(field1)
    INTO :var1
    FROM table;
QUIT;


Multiple Variables (Summarized)
PROC SQL;
    SELECT SUM(field1), COUNT(field2)
    INTO :var1, :var2
    FROM table;
QUIT;


One Variable (Multiple values saved to an array)
PROC SQL;
    SELECT field1
    INTO :var1 - :var9999
    FROM table;
QUIT;


Multiple Variables (Multiple values saved to multiple arrays)
PROC SQL;
    SELECT field1, field2
    INTO :var1 - :var9999, :x1 - :x9999
    FROM table;
QUIT;