BookmarkSubscribeRSS Feed
Badjuju
Fluorite | Level 6

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
4 REPLIES 4
tomrvincent
Rhodochrosite | Level 12
does this work by itself?

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

Yes, that part works

Reeza
Super User

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
Meteorite | Level 14

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;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

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

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1488 views
  • 0 likes
  • 4 in conversation