subtraction on group by

[sunilreddy]

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

[slchen]

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;

[stat_sas]

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;

[Haikuo]

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

[Ksharp]

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