CODEWITHSUNDEEP
sqlsas
Rookie_123
Rookie_123

Reputation: 2017

PROC SQL LEFT JOIN

I want to join two tables in SAS and I am using PROC SQL for it

I have newdb.access_db which has several company codes and newdb.Company_Codes having names of the corresponding company codes

I want the ouput in such a way that the resulting table will all the columns from newdb.access_db and the new column of company names corresponding to company codes of each row

Following is my code 

PROC SQL;
CREATE TABLE newdb.access_db_with_company_name AS
SELECT newdb.access_db.*,newdb.Company_Codes.ENTITY FROM newdb.access_db
LEFT JOIN newdb.Company_Codes
ON newdb.access_db.ENTREPRISE_IDENTIFIER = newdb.Company_Codes.ENTREPRISE_IDENTIFIER;
RUN;

With this code I am getting syntax error 

ERROR 22-322: Syntax error, expecting one of the following: a quoted string, !, !!, &, *, **,
              +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, AS, BETWEEN, CONTAINS, EQ, EQT,
              FORMAT, FROM, GE, GET, GT, GTT, IN, INFORMAT, INTO, IS, LABEL, LE, LEN, LENGTH,
              LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, TRANSCODE, ^, ^=, |, ||, ~, ~=.

Error points to 

SELECT newdb.access_db.*,newdb.Company_Codes.ENTITY FROM newdb.access_db
                      -                         -
                      22                        22
                      200                       76

When I see my editor window in sas it say that code is running.I then hit break button to stop the code.

I am not getting whether what I have wrote is correct or not.Even if it is wrong then how the code is executing

Upvotes: 0

Views: 3865

Answers (2)

DomPazz
DomPazz

Reputation: 12465

You need to alias the tables:

PROC SQL;
CREATE TABLE newdb.access_db_with_company_name AS
SELECT a.*b.ENTITY 
FROM newdb.access_db as a
LEFT JOIN newdb.Company_Codes as b
ON a.ENTREPRISE_IDENTIFIER = b.ENTREPRISE_IDENTIFIER;
RUN;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Whenever, you have more than one table in a query, you should use table aliases and qualified column names. The aliases should be abbreviations for the tables, so the queries are easier to understand.

So, I would recommend:

PROC SQL;
CREATE TABLE newdb.access_db_with_company_name AS
    SELECT a.*, cc.ENTITY
    FROM newdb.access_db a LEFT JOIN
         newdb.Company_Codes cc
         ON a.ENTREPRISE_IDENTIFIER = cc.ENTREPRISE_IDENTIFIER;
    RUN;

Upvotes: 1

Related Questions