topic Re: SAS SQL Code Efficiency in SAS Programming

SAS SQL Code Efficiency

gargi01 — Thu, 19 Jan 2017 05:29:21 GMT

Could someone please help me with how SAS/SQL processes nested queries or subqueries. Would it do the inner select, "select sum(price) from MasterFile" only once or with each iteration of the outer query?

proc sql; 
create table categorySpend as 
select categoryid, sum (price) as CategoryRevenue, (select sum(price) from MasterFile)as TotalRevenue 
from MasterFile 
group by categoryid; 
quit;

Re: SAS SQL Code Efficiency

art297 — Thu, 19 Jan 2017 06:11:34 GMT

Good question!

Here is a test I just ran:

data masterfile;
  input categoryid price;
  do i=1 to 1000000;
    output;
  end;
cards;
1 10
2 20
3 30
;

proc sql; 
  select categoryid, 
         sum (price) as CategoryRevenue,
         (select sum(price) from MasterFile)as TotalRevenue 
    from MasterFile 
      group by categoryid
  ; 
quit;

proc sql; 
  select categoryid, 
         sum (price) as CategoryRevenue
    from MasterFile 
      group by categoryid
  ; 
  select sum(price) as TotalRevenue
    from MasterFile
  ;
quit;

Both sets of code select the same things. When I first ran the test it appeared that the embedded select ran for each subgroup. However, when I set up the code to run about 100 times, on average both sets took the same time to run.

As such, I'd have to guess that it is only run once.

HTH,

Art, CEO, AnalystFinder.com

Re: SAS SQL Code Efficiency

gargi01 — Thu, 19 Jan 2017 06:31:44 GMT

^ Thanks a ton 🙂

Re: SAS SQL Code Efficiency

Kurt_Bremser — Thu, 19 Jan 2017 08:47:07 GMT

Just to add: the probably fastest method might be this:

proc summary data=masterfile;
var price;
class categoryid;
output out=sum1 (drop=_type_ _freq_ rename=(price=categoryrevenue)) sum=;
run;

data sum2;
set sum1;
retain totalrevenue;
if categoryid = .
then do;
  totalrevenue = categoryrevenue;
  delete;
end;
run;

As this really does only one pass through the original dataset.

With 10 times as many observations as used by @art297, these steps outperformed the SQL 8:16 seconds to 44.53 seconds on my 2-CPU IBM p520.