topic Re: SAS sql nested query in SAS Procedures

SAS sql nested query

pawandh — Sun, 20 Dec 2015 05:24:18 GMT

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?

Re: SAS sql nested query

mohamed_zaki — Sun, 20 Dec 2015 11:41:00 GMT

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;

Re: SAS sql nested query

PGStats — Mon, 21 Dec 2015 04:25:55 GMT

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;