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
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;
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;
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)?
Oh, never mind me. I just finished my morning coffee and Pamela's reply makes absolute sense. Thanks!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.