topic Re: Nested SQL joins in SAS Programming

Nested SQL joins

Rohit_R — Fri, 16 Aug 2019 09:29:50 GMT

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;

Re: Nested SQL joins

FreelanceReinh — Fri, 16 Aug 2019 11:43:45 GMT

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".

Re: Nested SQL joins

Rohit_R — Fri, 16 Aug 2019 12:07:31 GMT

Thank you FreelanceReinhard

Removing the Order BY clause worked like a charm.

Cheers

Rohit