Complex SQL code

[Wallo]

I have inherited SQL code that I know works, but being a novice SQL person, cannot figure out how it is working.
I can do the basic create, select, from, where, group by, order by code. The additonal FROMs and SELECTS have me thrown for a loop. Is it poorly written or can someone breakdown the code in english and tell me what it is doing and where it is doing it from?

Thanks for any and all help.

proc sql;

create table GROUP.REP3 as

select distinct b.reqstno label='Request Number',
b.agecat label='Age Group',
male label='Male' format=comma11.,
female label='Female' format=comma11.
from (select distinct b.reqstno, b.agecat, male
from (select distinct reqstno, agecat, sum(months99)/12 as male
from group.membs
where sex='M'
group by reqstno, agecat) a
RIGHT JOIN pat b
on a.reqstno=b.reqstno and
a.agecat=b.agecat) a
FULL JOIN
(select distinct b.reqstno, b.agecat, female
from (select distinct reqstno, agecat, sum(months99)/12 as female
from group.membs
where sex='F'
group by reqstno, agecat) a
RIGHT JOIN pat b
on a.reqstno=b.reqstno and
a.agecat=b.agecat) b
on a.reqstno=b.reqstno and
a.agecat=b.agecat;
quit;

 

[klaz2002]

Wallo,
It doesn't sound too complex. Its a 4 way join. To do the same in reg sas code would probably be a 8-12 step process not counting the proc sorts. What do you want to know exactly?
In short, you have four different types of views that is 'mined' from the same table and put together (side-by side) in one table. Just follow the selects and you will see this. It pretty neat realy. It must of taken this guy/gal at least a day to write this.

klaz

 

[jymm]

you might get a better response if you were to post this (or at least the sql - no quit, no format=...) in the sql thread forum183 me give it a go though after ranting about SAS vs SQL

Which parts do you not understand??? It IS confusing, but
if it works... it IS a bit odd to do some of these things.

 

[Wallo]

Dear Klaz2002: Thanks for your response. In all the SQL books I have referenced, there has been no code written for a 4 way join. I had no idea it could be accomplished. SQL reference books only go as far as one inner select and one outer select without the additional right joins, full joins, and on within on. In trying to decipher the code, it becomes confusing to figure out what is being processed first, second, third, fourth. The reference books also do not explain the order in which the code executes, just an example of the code and the results. I'm trying to look at this code the same as I would look at nested IF statments in SAS. Maybe that's the wrong approach.

 

[klaz2002]

Wallo,
The best way to understand the code is to turn stuff off (comment the code) and see the resulting differences. You can start by just executing the first 'select' statement up and including the 'where' statement. Check the the table that it creates. Now you can run it again and check the latest table. Next, compare the two tables. You will see some additions. Keep to that process and the code will explain itself.
Unless I have access to your data there is only so much that I can expalin. Sorry.
I hope this helps you.
Klaz

 

[Quehay]

The virtual table 'a', which is a nested select from which columns are pulled and which is joined to pat, aliased as 'b' is defined twice if I'm reading correctly (there are two aliases of 'a' after the ON clause for the first RIGHT JOIN).

You can nest SELECT statements as virtual tables and use them with an alias thus:

select
a.col1,
a.col2
b.col3
from
(select col1, col2 from some_table) a
INNER JOIN other_table b
ON a.col1 = b.col3

Even if the RDBMS can handle the nested aliasing of these selects it's a bad coding style.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[Wallo]

Quehey,
Your right, it appears to be badly written code because there is no documentation and anytime nesting is done, it causes complications in unravelling.
Thanks for your response.