topic Re: Combining two data sets with SQL in SAS Programming

Combining two data sets with SQL

wlierman — Wed, 30 Jun 2021 18:08:55 GMT

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

Re: Combining two data sets with SQL

ballardw — Wed, 30 Jun 2021 18:51:44 GMT

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.

Re: Combining two data sets with SQL

wlierman — Wed, 30 Jun 2021 20:36:08 GMT

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