topic subtraction on group by in SAS Programming

subtraction on group by

sunilreddy — Sat, 31 May 2014 20:12:58 GMT

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

Re: subtraction on group by

slchen — Sat, 31 May 2014 21:39:37 GMT

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;

Re: subtraction on group by

stat_sas — Sat, 31 May 2014 23:38:04 GMT

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;

Re: subtraction on group by

Haikuo — Sun, 01 Jun 2014 03:37:41 GMT

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

Re: subtraction on group by

Ksharp — Sun, 01 Jun 2014 04:21:22 GMT

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