SAS PROC SQL update with inner join

Question

Last time I checked, this is completely legit SQL code that even Access can handle. Not sure why I'm getting the following error from SAS

UPDATE fu_coding 
INNER JOIN old_form16 
    ON fu_coding.CSPCCID = old_form16.CSPCCID 
SET fu_coding.CHANGED = 1
WHERE fu_coding.NEW_DIAG <> old_form16.NEW_DIAG;

Error is:

18311      INNER JOIN old_form16
           -----
       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.

I really appreciate your help with this!

duplicate of http://stackoverflow.com/questions/24629793/update-with-a-proc-sql — Allan Bowe, May 26 '16 at 11:30

Gordon Linoff · Answer 1 · 2016-05-28T21:41:15.123

3

I don't think proc sql supports update with inner join. You can do this:

UPDATE fu_coding 
    SET CHANGED = (SELECT 1
                   FROM old_form16
                   WHERE fu_coding.CSPCCID = old_form16.CSPCCID AND
                         fu_coding.NEW_DIAG <> old_form16.NEW_DIAG
                  )
    WHERE EXISTS (SELECT 1
                   FROM old_form16
                   WHERE fu_coding.CSPCCID = old_form16.CSPCCID AND
                         fu_coding.NEW_DIAG <> old_form16.NEW_DIAG
                  ) ;

The code might be simpler using a data step.

edited May 28 '16 at 21:41

answered May 25 '16 at 16:24

Gordon Linoff

1,242,037
58
646
786

I'm getting a syntax error. SAS underlines the period between fu_coding and CHANGED. It says it's expecting an "=" – blacksaibot May 27 '16 at 11:52
@blacksaibot . . . That table alias is not needed there. – Gordon Linoff May 28 '16 at 21:41

score 0 · Answer 2 · answered May 26 '16 at 11:32

0

SAS doesn't support update with inner join in the SQL procedure. Either re-write (as per @Gordon's suggestion), or use pass through if possible!

answered May 26 '16 at 11:32

Allan Bowe

12,306
19
75
124

SAS PROC SQL update with inner join

2 Answers2