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
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;
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;
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.