DATA Step, Macro, Functions and more

summing my sums in proc sql easily

Reply
Contributor
Posts: 43

summing my sums in proc sql easily

suppose I have a query that is has a lot of sums, but I also want to sum these functions in a subsequent column

proc sql;
select
sum(fee1,fee2,fee3) as Fee123,
sum(fee4,fee5,fee6) as Fee456,
sum(Fee123, Fee456) as TotalSUM quit;

I know that TotalSUM will not work and the only way to do this is the query below or I can put this in a table and then sum Fee123 + Fee456

 

 


sum(sum(fee1,fee2,fee3), sum(fee4,fee5,fee6)) as TotalSUM

 

but is there a way to do this cleanly and easily (either procsql or datastep)?  My query has a lot of aggregates of aggregates and I want it to simplify this and also avoid creating a bunch of temp tables to make it work.

Super User
Super User
Posts: 8,281

Re: summing my sums in proc sql easily

If you want to reference a derived value in the same SQL statement use the CALCULATED keyword.

proc sql;
select
  sum(fee1,fee2,fee3) as Fee123
, sum(fee4,fee5,fee6) as Fee456
, sum(calculated Fee123,calculated  Fee456) as TotalSUM
from ....
;
quit;

You do realize you are using the SAS function SUM(,) and not the SQL aggregate function SUM() in that query?

So it is calculating separate sums for each observations. It is similar to if you coded use + operator, but with missing values treated as zero.

  fee1+fee2+fee3 as Fee123
, fee4+fee5+fee6 as Fee456
Ask a Question
Discussion stats
  • 1 reply
  • 84 views
  • 0 likes
  • 2 in conversation