BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Yves_Boonen
Quartz | Level 8

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
Pamela_JSRCC
Quartz | Level 8

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

4 REPLIES 4
Pamela_JSRCC
Quartz | Level 8

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;

LinusH
Tourmaline | Level 20
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
Yves_Boonen
Quartz | Level 8

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

 

 

Yves_Boonen
Quartz | Level 8

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

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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