04-24-2018 02:17 PM
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.
04-24-2018 02:23 PM
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