DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 62
Accepted Solution

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

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

 


Accepted Solutions
Solution
‎04-15-2016 03:01 AM
Contributor
Posts: 39

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

Posted in reply to Yves_Boonen

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;

View solution in original post


All Replies
Solution
‎04-15-2016 03:01 AM
Contributor
Posts: 39

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

Posted in reply to Yves_Boonen

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;

Super User
Posts: 5,437

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

Posted in reply to Yves_Boonen
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.
Data never sleeps
Contributor
Posts: 62

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

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)?

 

 

Contributor
Posts: 62

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

Posted in reply to Yves_Boonen

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 206 views
  • 2 likes
  • 3 in conversation