## Summing fields with conditions

Solved
Super Contributor
Posts: 261

# Summing fields with conditions

X1X2X3X4X5
-10-55050100
32342-123
1-5-74412

Bascially i want to perform calculation that allocates the negative values to the cells with the positive values in proportion to the split of the positive values.

So the above table would become:

X1X2X3X4X5
00=50+(-15)*(50/200)=50+(-15)*(50/200)=100+(-15)*(100/200)

To achive this i need to sum the negative values, the posive values and then apply a condition formula like if X1 <0 then X1 =0; else X1 = X1 + (sumNeg)*(X1)/(sumPos);

or something like that

Is this easy to do?

Accepted Solutions
Solution
‎09-09-2014 10:36 AM
Posts: 1,270

## Re: Summing fields with conditions

data want(keep=x1-x5);

set have;

array x{*} x1-x5;

sum_neg=0;

sum_pos=0;

do i=1 to dim(x);

if x{i}<0 then sum_neg+x{i};

else sum_pos+x{i};

if x{i}<0 then x{i}=0;

end;

do i=1 to dim(x);

x{i} = x{i} + sum_neg*x{i}/sum_Pos;

end;

run;

All Replies
Solution
‎09-09-2014 10:36 AM
Posts: 1,270

## Re: Summing fields with conditions

data want(keep=x1-x5);

set have;

array x{*} x1-x5;

sum_neg=0;

sum_pos=0;

do i=1 to dim(x);

if x{i}<0 then sum_neg+x{i};

else sum_pos+x{i};

if x{i}<0 then x{i}=0;

end;

do i=1 to dim(x);

x{i} = x{i} + sum_neg*x{i}/sum_Pos;

end;

run;

Super Contributor
Posts: 261

## Re: Summing fields with conditions

Thank you

Regular Contributor
Posts: 233

## Re: Summing fields with conditions

data have;
input  var1 var2 var3 var4 var5;
cards;
-10  -5  50  50 100
3    23  42  -1  23
1    -5  -7  44  12
;
run;

%let howmany = 5;

%macro want(howmany);
%IF &howmany GT 0 %THEN
%DO;

data want;
set have;

%do j=1 %to &howmany;
if var&j. lt 0 then
var&j.=0;
else var&j.=var&j.+(-15)*(var&j./200);
%end;
run;

%end;
%mend want;

%want(5)

🔒 This topic is solved and locked.