Simple sql not working

[Badjuju]

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, 2020

36         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

[tomrvincent]

does this work by itself?

SELECT FY, COUNT(*) AS MATCH_COUNT
FROM WORK.FINALCOMPARE_504
WHERE test2 = 1
group by fy

[Badjuju]

Yes, that part works

[Reeza]

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, 2020

36         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

---

[ed_sas_member]

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;