Hello. I'm trying to run this basic sql and getting the following errors. Thoughts?
PROC SQL;
create table compare_table as
(SELECT FY, COUNT(*) AS MATCH_COUNT
FROM WORK.FINALCOMPARE_504
WHERE test2 = 1
group by fy) a
LEFT JOIN
(SELECT FY, COUNT(*) AS TOTAL_OBS
FROM WORK.FINALCOMPARE_504
group by fy) b
on a.fy = b.fy;
quit;
This is the log:
Yes, that part works
Try this, if it doesn't work, please post your code and log again, but make sure to use the insert code/log (6/7th icons in the editor).
EDIT: you're missing the select * from portion so the sub queries run but you don't have a main query.
PROC SQL; create table compare_table as select * from (SELECT FY, COUNT(*) AS MATCH_COUNT FROM WORK.FINALCOMPARE_504 WHERE test2 = 1 group by fy) a LEFT JOIN (SELECT FY, COUNT(*) AS TOTAL_OBS FROM WORK.FINALCOMPARE_504 group by fy) b on a.fy = b.fy; quit;
@Badjuju wrote:
Hello. I'm trying to run this basic sql and getting the following errors. Thoughts?
PROC SQL;
create table compare_table as
(SELECT FY, COUNT(*) AS MATCH_COUNT
FROM WORK.FINALCOMPARE_504
WHERE test2 = 1
group by fy) a
LEFT JOIN
(SELECT FY, COUNT(*) AS TOTAL_OBS
FROM WORK.FINALCOMPARE_504
group by fy) b
on a.fy = b.fy;
quit;
This is the log:
27 PROC SQL;
28 create table compare_table as
29 (SELECT FY, COUNT(*) AS MATCH_COUNT
30 FROM WORK.FINALCOMPARE_504
31 WHERE test2 = 1
32 group by fy) a
_
22
202
ERROR 22-322: Syntax error, expecting one of the following: ;, EXCEPT, INTERSECT, ORDER, OUTER, UNION.ERROR 202-322: The option or parameter is not recognized and will be ignored.33 LEFT JOIN
34 (SELECT FY, COUNT(*) AS TOTAL_OBS
__ __
22 202
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=.ERROR 202-322: The option or parameter is not recognized and will be ignored.34 ! (SELECT FY, COUNT(*) AS TOTAL_OBS
__
22
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.35 FROM WORK.FINALCOMPARE_504
2 The SAS System 08:28 Thursday, January 23, 202036 group by fy) b
_____
22
76
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, ), *, **, +, ',', -, /, <, <=, <>, =, >, >=, ?, AND, BETWEEN,
CONTAINS, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE, NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.ERROR 76-322: Syntax error, statement will be ignored.Any help greatly appreciated
Hi @Badjuju
You just need to add a SELECT clause before joining the two queries in the FROM clause
proc sql;
create table compare_table as
select a.FY, a.MATCH_COUNT, b.TOTAL_OBS
from
(select FY, count(FY) as MATCH_COUNT from WORK.FINALCOMPARE_504 WHERE test2=1 group by FY) a
left join
(select FY, count(FY) as TOTAL_OBS from WORK.FINALCOMPARE_504 group by FY) b
on a.fy=b.fy;
quit;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.