## subtraction on group by

Frequent Contributor
Posts: 115

# subtraction on group by

Hi,

I have below input dataset, variables C1, C2, N1.

C1  C2  N1
--------------
R1        10
R1  S1   1
R1  S2   2
R2        20
R2  S5   4
R2  S9   1

R3         9

R4 S4   1

Output would be in below format(subtraction results would be on R1 and R2 record where C2 is missing and keep all records in below output format), Can u suggested with either proc or datastep

Output:

C1  C2  N1
-------------
R1          7
R1  S1   1
R1  S2   2
R2        15
R2  S5   4
R2  S9   1

R3         9

R4 S4   1

Super Contributor
Posts: 275

## Re: subtraction on group by

Posted in reply to sunilreddy

data have;

infile cards truncover;

input C1 \$ C2 \$  N1;

cards;

R1   .    10

R1  S1   1

R1  S2   2

R2   .    20

R2  S5   4

R2  S9   1

;

run;

data want;

do i=1 by 1 until(last.c1);

set have;

by c1;

retain amount;

if not first.c1 then amount+n1;

end;

do i=1 by 1 until (last.c1);

set have;

by c1;

if first.c1 then n1=n1-amount;

output;

end;

call missing(amount);

drop i amount;

run;

Trusted Advisor
Posts: 1,270

## Re: subtraction on group by

Posted in reply to sunilreddy

Hi,

Try this code for the desired output.

Regards,

Naeem

proc sql;

select a.c1,a.c2,case when a.c2 is null and a.c1 in ('R1','R2') then N1-b.tot else N1 end as N1

from have a left join (select C1,sum(N1) as tot from have where c1 in ('R1','R2') and c2 is not null group by C1) b

on a.C1=b.C1

order by a.c1,a.c2,a.n1;

quit;

Respected Advisor
Posts: 3,167

## Re: subtraction on group by

Naeem,

Your code is ONLY good if OP's problem is an AS-IS assignment instead of a simplified example implicating a more complicated real life scenario. Here is an Proc SQL approach aiming for broader applications per OP's rules:

data have;

input C1 :\$ C2 :\$ N1;

cards;

R1   . 10

R1  S1   1

R1  S2   2

R2    . 20

R2  S5   4

R2  S9   1

R3     . 9

R3  S10  2

R4 S4   1

;

proc sql;

select c1, c2, case when missing (c2) then (select a.n1-coalesce(sum(n1),0) from have where not missing(c2) and a.c1=c1 ) else n1 end as n1

from have a;

quit;

Regards,

Haikuo

Super User
Posts: 10,787

## Re: subtraction on group by

Posted in reply to sunilreddy

Another way is to get the sum of non-missing value and merge it back to subtract it . Here is DOW skill.

```data have;
input C1 :\$ C2 :\$ N1;
cards;
R1   . 10
R1  S1   1
R1  S2   2
R2    . 20
R2  S5   4
R2  S9   1
R3     . 9
R4 S4   1
;
run;
data want;
sum=0;
do until(last.c1);
set have;
by c1;
if not missing(c2) then sum+n1;
end;
do until(last.c1);
set have;
by c1;
if  missing(c2) then n1=n1-sum;
output;
end;
drop sum;
run;
```

Xia Keshan

Discussion stats
• 4 replies
• 517 views
• 2 likes
• 5 in conversation