topic Re: Joining tables: one.id=two.id syntax issue in SAS Programming

Joining tables: one.id=two.id syntax issue

Yves_Boonen — Tue, 12 Apr 2016 07:33:20 GMT

I have made a query which creates a table containing employee names, division and area.

- dataset: tables.employee

- employee names variable: group_member

I have made another query which creates a table containing each employee's opened and closed dossiers and the respective time to close said dossiers.

- dataset: tables.task__ (double underscore)

- employee names variable: task_handler

Now I wanted to merge both tables into one neat looking table, based on one common variable: employee names. I found this generalized code on the SAS.com website, but I have no idea what the proper syntax is.

General code:

PROC SQL;
CREATE TABLE QinBOTH AS
SELECT *
FROM ONE, TWO
WHERE ONE.ID=TWO.ID
;
QUIT;

I changed it into this:

proc sql;
create table tables.report as
select *
from tables.employee, tables.task__
where tables.employee.group_member=tables.task__.task_handler
;
quit;

Unfortunatly simply changing the word "ONE" with the tables.employee and changing the word "TWO" with tables.task__ isn't correct. How would the syntax have to be?

This is the log by the way:

3139  proc sql;
3140  create table tables.report as
3141  select *
3142  from tables.employee, tables.task__
3143  where tables.employee.group_member=tables.task__.task_handler
                           -
                           22
                           76
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, -, /, <, <=,
              <>, =, >, >=, ?, AND, BETWEEN, CONTAINS, EQ, EQT, EXCEPT, GE, GET, GROUP, GT, GTT,
              HAVING, IN, INTERSECT, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ORDER,
              OUTER, UNION, ^, ^=, |, ||, ~, ~=.

ERROR 76-322: Syntax error, statement will be ignored.

3144  ;
3145  quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds

Re: Joining tables: one.id=two.id syntax issue

Pamela_JSRCC — Thu, 14 Apr 2016 19:06:28 GMT

Hi, I think you might find using an alias helpful, e.g.

proc sql;
create table tables.report as
select *
from tables.employee A
join tables.task__ B on a.task_handler = b.group_member;
quit;

Re: Joining tables: one.id=two.id syntax issue

LinusH — Thu, 14 Apr 2016 19:18:08 GMT

SAS doesn't support three or four level names line my other databases do. That's why you need to use aliases, like @Pamela_JSRCC suggests.

Re: Joining tables: one.id=two.id syntax issue

Yves_Boonen — Fri, 15 Apr 2016 06:53:36 GMT

Thanks for the feedback.

I do understand what both of you imply, however, I kind of fail to see it in a practical situation. Could you perhaps elaborate with a simple example and bold out alias (right vs wrong)?

Re: Joining tables: one.id=two.id syntax issue

Yves_Boonen — Fri, 15 Apr 2016 07:00:22 GMT

Oh, never mind me. I just finished my morning coffee and Pamela's reply makes absolute sense. Thanks!