topic PROC SQL FULL OUTER JOIN naming syntax confusion in New SAS User

PROC SQL FULL OUTER JOIN naming syntax confusion

moseland — Mon, 01 Apr 2019 02:40:21 GMT

I'm writing a PROC SQL code to full outer join 2 tables. I am somewhat familiar with the syntax in PostgreSQL but am unsure what the naming syntax is when writing a step like this inside PROC SQL. My issue is I get a syntax error on the last line before the run statement at the period between the first diagnosis and DX_KEY. In SAS how is one supposed to identify the primary key matching columns? The chop is the name of the library I created.

ON chop.diagnosis.DX_KEY = chop.visit_diagnosis.DX_KEY;
-
22
76
ERROR 22-322: Syntax error,

PROC SQL;
CREATE TABLE chop_dx_joined
AS Select * FROM chop.diagnosis
FULL JOIN chop.visit_diagnosis
ON chop.diagnosis.DX_KEY = chop.visit_diagnosis.DX_KEY;
run;

Re: PROC SQL FULL OUTER JOIN naming syntax confusion

Tom — Mon, 01 Apr 2019 03:00:31 GMT

You have the syntax correct, but are confused about how to use ALIASes in SQL code to clarify which specific variable you are referencing.

The ALIAS is a ONE word string that refers to a table mentioned in your SQL statement. By default the alias will be the member name of the dataset you are referencing. If you want to use a different alias then add the alias after the table (dataset) name in your statement. This is critical when you are using either the same dataset twice or two datasets with the same member name from two different libraries.

In this example I have used LEFT and RIGHT as the aliases.

proc sql;
create table chop_dx_joined as
  select *
  from chop.diagnosis LEFT
  full join chop.visit_diagnosis RIGHT
  on LEFT.dx_key = RIGHT.dx_key
;
run;

Note this is standard SQL syntax. So it will work just as well in PosgreSQL's implementation of SQL.

Re: PROC SQL FULL OUTER JOIN naming syntax confusion

Patrick — Mon, 01 Apr 2019 03:07:56 GMT

@moseland

You can't use 3 level name syntax like "chop.diagnosis.DX_KEY" with SAS SQL. Use aliases instead.

For the select clause: If you have same named variables in both source tables then you need to explicitly define which one to use. A simple select * won't do.

PROC SQL;
  CREATE TABLE chop_dx_joined AS
    Select t1.* 
      FROM chop.diagnosis t1 FULL JOIN chop.visit_diagnosis t2
        ON t1.DX_KEY = t2.DX_KEY;
run;

Re: PROC SQL FULL OUTER JOIN naming syntax confusion

moseland — Tue, 02 Apr 2019 02:07:43 GMT

Thank you! The code was successful. A follow up question: For organization, could I declare the aliases at the beginning of the PROC SQL step? For example,

PROC SQL

create table chop_dx_joined

diagnosis AS T1

visit_diagnosis AS T2
select *
from chop.diagnosis
full join chop.visit_diagnosis
on T1.dx_key = T2.dx_key;
run;

Re: PROC SQL FULL OUTER JOIN naming syntax confusion

moseland — Tue, 02 Apr 2019 02:08:47 GMT

Thank you! This explanation is extremely helpful.