Combining two data sets with SQL

[wlierman]

I have a straightforward ask.

I have two datasets  DS1 and DS2.  DS1  has two columns: Contact_ID ,  Place_of_employment

                                                          DS2  several fields:  Place_of_employment  ,   NAICS_code   and several others ..........

My question is that I want to join the datasets using  SQL    DS1 --> DS2

so the combined dataset     DS_ALL  has as columns:  Contact_ID,   Place of Employment,  NAICS_Code,  other columns .....

I have included my code where I was using a Left Join

Proc Sql noprint;
CREATE TABLE SASCDC_2.Contact_ID_Employment_NAICS AS
   Select * 
   From SASCDC_2.Set_Contact_Person_Employment
   Left Join SASCDC_2.Set_Contact_ID_Employment
   On SASCDC_2.Set_Contact_Person_Employment.Place_of_Employment = SASCDC_2.Set_Contact_ID_Employment.Place_of_Employment;
quit;

But the code is not correct as can be seen by Log

Select *
85      From SASCDC_2.Set_Contact_Person_Employment
86      Left Join SASCDC_2.Set_Contact_ID_Employment
87      On SASCDC_2.Set_Contact_Person_Employment.Place_of_Employment =
                                                 -
                                                 22
                                                 76
87 ! SASCDC_2.Set_Contact_ID_Employment.Place_of_Employment;
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, ',', -, /,
              <, <=, <>, =, >, >=, ?, AND, ANSIMISS, BETWEEN, CONTAINS, CROSS, EQ, EQT,
              EXCEPT, FULL, GE, GET, GROUP, GT, GTT, HAVING, IN, INNER, INTERSECT, IS, JOIN,
              LE, LEFT, LET, LIKE, LT, LTT, NATURAL, NE, NET, NOMISS, NOT, NOTIN, OR, ORDER,
              OUTER, RIGHT, UNION, WHERE, ^, ^=, |, ||, ~, ~=.

Appreciate your help in this.

Thank you.

wklierman

[ballardw · Accepted solution]

SQL uses datasetidenfier.variablename syntax. When you use a LIBRARY as part of the name you asking for something that SQL doesn't expect to see. So you use an ALIAS to refer to the libname.dataset name

Proc Sql noprint;
CREATE TABLE SASCDC_2.Contact_ID_Employment_NAICS AS
   Select * 
   From SASCDC_2.Set_Contact_Person_Employment AS A
   Left Join SASCDC_2.Set_Contact_ID_Employment AS B
   On A.Place_of_Employment = B.Place_of_Employment;
quit;

The "as" after the data set name is optional but I tend to use it as a reminder that A is referencing  that set more clearly.

You would also likely want : Select A.*, B.* to get variables from both sets though there will be a message about one of the variables "already on the data set" because the variable(s) joined on come from both sets. Or use explicit lists of the variables using A. and B. prefixes.

[wlierman]

Thank you for the quick response. I tried a regular data step sorting by place_of_employment

that messed things up because values for the other variables did not come through.

Thank you for this approach.

wklierman