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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.