Hello
I want to calculate sum of fields .
One of the fields in the sum is calculated variable.
Is there a way to calculate it in one step with SUM function?
This way is not working
SUM(case when X<0 then abs(X) else 0 end as calc_X,Y,Z) as Total_Other_Way
Data have;
Input Cust_ID X Y Z;
cards;
1 -100 50 60
2 200 60 40
3 -300 30 20
4 400 10 90
5 250 80 20
;
Run;
proc sql;
create table want as
select *,
case when X<0 then abs(X) else 0 end as calc_X,
SUM(calculated calc_X,Y,Z) as Total,
SUM(case when X<0 then abs(X) else 0 end as calc_X,Y,Z) as Total_Other_Way
from have
;
quit;
If you want the intermediate result in a new variable, you have to do the separate calculation. If not, use the second way, but remove the "as calc_x".
I got no problem.
Data have;
Input Cust_ID X Y Z;
cards;
1 -100 50 60
2 200 60 40
3 -300 30 20
4 400 10 90
5 250 80 20
;
Run;
proc sql;
create table want as
select *,
case when X<0 then abs(X) else 0 end as calc_X,
SUM(calculated calc_X,Y,Z) as Total
from have
;
quit;
Or you could try this one:
Data have;
Input Cust_ID X Y Z;
cards;
1 -100 50 60
2 200 60 40
3 -300 30 20
4 400 10 90
5 250 80 20
;
Run;
proc sql;
create table want as
select *,
-X*(X<0) as calc_X,
SUM(calculated calc_X,Y,Z) as Total
from have
;
quit;
Thank you but the solution provided is in two steps of calculation-
First you calculate the field X_calc
Then you calculate TOTAL
My question is if can do it in one step so the calculation of field X_Calc be inside the SUM function??
If you want the intermediate result in a new variable, you have to do the separate calculation. If not, use the second way, but remove the "as calc_x".
Great,
Here is the solution as you offered
Data have;
Input Cust_ID X Y Z;
cards;
1 -100 50 60
2 200 60 40
3 -300 30 20
4 400 10 90
5 250 80 20
;
Run;
proc sql;
create table want as
select *,
SUM(case when X<0 then abs(X) else 0 end,Y,Z) as Total_Other_Way
from have
;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.