topic Re: calculate sum of fields with calculate field in SAS Programming

calculate sum of fields with calculate field

Ronein — Wed, 03 Apr 2024 06:39:05 GMT

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;

Re: calculate sum of fields with calculate field

Ksharp — Wed, 03 Apr 2024 07:16:57 GMT

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;

Re: calculate sum of fields with calculate field

Kurt_Bremser — Wed, 03 Apr 2024 07:19:35 GMT

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".

Re: calculate sum of fields with calculate field

Ronein — Wed, 03 Apr 2024 07:19:38 GMT

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??

Re: calculate sum of fields with calculate field

Ronein — Wed, 03 Apr 2024 07:20:58 GMT

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;