Create a new observation from mathematical operations of other observations

[moreka]

How could I create new observations in a dataset based on adding, subtracting, etc. across other observations?

In the example below, observation 4 is created so that

Var1 = 'NotTN'

Var2 = sum of all Var2 where Var1^='TN'

Have:

Var1	Var2
SC	1
NC	3
TN	5

Want:

Var1	Var2
SC	1
NC	3
TN	5
NotTN	4

Thanks!

[Reeza · Accepted solution]

It depends. How complex can your rules get? That one is fairly straight forward, and here's a hard coded solution. If you need something that changes with your data dynamically this won't work.

data want;

set have end=eof;

retain sum;

if var1 ne 'TN' then sum=sum+var2;

output;

if eof then do;

var1='NotTN';

var2=sum;

output;

end;

run;

[moreka]

Thanks!  The 'end=' option is exactly what I'm looking for. 

Also, I needed to put a 0 at the end of the retain line to work:

     retain sum 0;

[stat_sas]

Using sql

data have;
input var1 $ var2;
if var1='TN' then flag=1; else flag=0;
datalines;
SC 1
NC 3
TN 5
;

proc sql;
select var1,var2 from have
union
select "NotTN",sum(var2) from have
where flag=0
order by var2;
quit;