[SOLVED] SAS STUDIO - Error with Inner join syntax

SAS STUDIO - Error with Inner join syntax

I have 4 tables; PREFIX, FORENAME, MIDDLENAME, SURNAME. With an common column "row_num". I am taking the fuzzy matched scores out of each table, name PFX_SCR etc...

This is my current code:

PROC SQL;
CREATE TABLE REJOIN_SCRS AS
SELECT PREFIX.ROW_NUM, PFX_SCR, FRNME_SCR, MDNME_SCR, SRNME_SCR
FROM PREFIX
INNER JOIN FORENAME, MIDDLENAME, SURNAME
USING(ROW_NUM);
QUIT;

I am getting this error:

  SELECT PREFIX.ROW_NUM, PFX_SCR, FRNME_SCR, MDNME_SCR, SRNME_SCR
86    FROM PREFIX
87    INNER JOIN FORENAME, MIDDLENAME, SURNAME
                         -
                         22
                         76
ERROR 22-322: Syntax error, expecting one of the following: a name, (, '.', AS, ON.  
ERROR 76-322: Syntax error, statement will be ignored.
88    USING(ROW_NUM);
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
89    QUIT;

I don't know if I'm having a moment or if there's something wrong but I cannot figure out what is wrong with this.

Solution

You have to have an ON clause for every INNER JOIN. So perhaps you meant something like

from PRFIX
inner join FORENAME on PREFIX.ROW_NUM = FORENAME.ROW_NUM
inner join MIDDLENAME on PREFIX.ROW_NUM = MIDDLENAME.ROW_NUM
...

You can use NATURAL joins to have SAS pick the keys to match based on which variables have the same names.

from PRFIX natural inner join FORENAME
natural inner join MIDDLENAME
...

I would think it would be easier to just use SAS syntax instead of SQL.

data REJOIN_SCRS;
  merge PREFIX FORENAME MIDDLENAME LASTNAME ;
  by ROW_NUM;
run;