topic Re: Simple sql not working in SAS Procedures

Simple sql not working

Badjuju — Thu, 23 Jan 2020 18:01:48 GMT

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

Re: Simple sql not working

tomrvincent — Thu, 23 Jan 2020 18:08:08 GMT

does this work by itself?

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

Re: Simple sql not working

Badjuju — Thu, 23 Jan 2020 18:08:54 GMT

Yes, that part works

Re: Simple sql not working

Reeza — Thu, 23 Jan 2020 18:13:43 GMT

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

Re: Simple sql not working

ed_sas_member — Thu, 23 Jan 2020 18:23:53 GMT

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;