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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.