DATA Step, Macro, Functions and more

SAS SQL Code Efficiency

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

SAS SQL Code Efficiency

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;

 


Accepted Solutions
Solution
‎01-19-2017 01:31 AM
PROC Star
Posts: 7,363

Re: SAS SQL Code Efficiency

[ Edited ]

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

 

 

 

View solution in original post


All Replies
Solution
‎01-19-2017 01:31 AM
PROC Star
Posts: 7,363

Re: SAS SQL Code Efficiency

[ Edited ]

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

 

 

 

New Contributor
Posts: 3

Re: SAS SQL Code Efficiency

^ Thanks a ton Smiley Happy
Super User
Posts: 6,948

Re: SAS SQL Code Efficiency

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 132 views
  • 0 likes
  • 3 in conversation