Fluorite | Level 6

SAS sql nested query

I have a date set shown below

data new;
input
location\$ &20. date:mmddyy. rev1:dollar. rev2:dollar. rev3:dollar. costs:dollar.;
format date date9.;
cards;
Boston 12/15/2006 \$500 \$150 \$300 -\$200
Boston 12/22/2006 \$300 \$500 \$200 -\$200
Boston 12/29/2006 \$100 \$200 \$50 -\$200
Boston 1/6/2007 \$100 \$100 \$150 -\$200
New York 12/15/2006 . \$600 \$400 -\$400
New York 12/22/2006 \$700 \$600 \$250 -\$400
New York 12/29/2006 \$200 \$300 \$100 -\$400
New York 1/6/2007 \$50 \$100 \$50 -\$400
;run;

need to find -How much profit did each branch earn during the holiday period, in dollars and as a percent of
total profits?

And got one solution -select
location,
sum(sum(rev1,rev2,rev3,costs)) as branch_profit,
total_profit
from financials,
(select
sum(sum(rev1,rev2,rev3,costs)) as total_profit
from financials
where location ne ' ')
where location ne ' '
group by location;
quit;

can someone explain how this query is working and why "total_profit" is there in the main query?

2 REPLIES 2
Barite | Level 11

Re: SAS sql nested query

``````proc sql;
select distinct
location,
sum(sum(rev1,rev2,rev3,costs)) as branch_profit
, (CALCULATED branch_profit/ total_profit *100) as percent
from  financials, (select sum(sum(rev1,rev2,rev3,costs)) as total_profit from  financials where location ne ' ')
where location ne ' '
group by location;
quit;``````
Opal | Level 21

Re: SAS sql nested query

``````select
location,
sum(sum(rev1, rev2, rev3, costs)) as branch_profit,
total_profit
from
financials,
(select
sum(sum(rev1, rev2, rev3, costs)) as total_profit
from financials
where location ne ' ' )
where location ne ' '
group by location;
quit;``````

The main query joins table financials with the result from the subquery without a join condition. The result from the subquery (a single observation) is thus crossed with every observation from table financials. The resulting table, call it A = (location, rev1, rev2, rev3, costs, total_profit), is then summarized by location to yield another table, call it B = (location, branch_profit). Tables A and B are then joined (remerged) by location to yield the final result. The SQL optimizer might use some tricks to speed things up, but formally, that's how it's done.

You would get the same result (perhaps more efficiently) with the query:

``````select
*
from
(select
location,
sum(sum(rev1, rev2, rev3, costs)) as branch_profit
from financials
where location ne ' '
group by location),
(select
sum(sum(rev1, rev2, rev3, costs)) as total_profit
from financials
where location ne ' ' )
;
quit;``````
PG
Discussion stats
• 2 replies
• 738 views
• 0 likes
• 3 in conversation