Hi
I am having some syntax trouble with this piece of code. It works fine when running in SQl but when running it using proc sql, it doesnt work except in the innermost select statement. I am not sure where the syntax error is :/.
The error is:
55 ) order by ORGN_ID)
_____ _
79 22
200
ERROR 79-322: Expecting a ).
ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, (, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND,
ASC, ASCENDING, BETWEEN, CONTAINS, DESC, DESCENDING, EQ, EQT, GE, GET, GT, GTT, IN, IS, LE, LET, LIKE, LT, LTT, NE,
NET, NOT, NOTIN, OR, ^, ^=, |, ||, ~, ~=.
The code is:
proc sql;
create table test1 as
select ORGN_ID, count(*) as V
from (
select ORGN_ID, ID_PERSON,count(distinct ID_PERSON)
from (
select *
from (
select AP.ORGN_ID,XX.ID_PERSON, count(AP.ORGN_ID) as VOL
from PC.AP_SEC AP
left join PC.KEY_PERSON XX
on AP.ID_APP=XX.ID_APP
left join PC.BUSINESS BS
on AP.ID_BUSINESS=BS.ID_BUSINESS
where BS.BSNSS_TYP = 'Z'
and (XX.SRNM is not null or XX.TTL is not null or XX.DT_OF_BRTH is not null )
group by AP.ORGN_ID,XX.ID_PERSON
) order by ORGN_ID
) group by ORGN_ID, ID_PERSON
) group by ORGN_ID;
quit;
Hi @Rohit_R,
The ORDER BY clause must be at the end of the outermost query to be syntactically correct. (Note that there's no need to sort anything else.) Due to the GROUP BY clause (with the same key variable) it should be redundant anyway, so you can simply delete it. Check section "Sort Information" of PROC CONTENTS output for table test1 to verify this.
For further simplification I'd suggest to replace "select * from (innermost query)" by "innermost query".
Hi @Rohit_R,
The ORDER BY clause must be at the end of the outermost query to be syntactically correct. (Note that there's no need to sort anything else.) Due to the GROUP BY clause (with the same key variable) it should be redundant anyway, so you can simply delete it. Check section "Sort Information" of PROC CONTENTS output for table test1 to verify this.
For further simplification I'd suggest to replace "select * from (innermost query)" by "innermost query".
Thank you FreelanceReinhard
Removing the Order BY clause worked like a charm.
Cheers
Rohit
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 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.