topic Create a new observation from mathematical operations of other observations in SAS Programming

Create a new observation from mathematical operations of other observations

moreka — Mon, 07 Jul 2014 18:08:44 GMT

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!

Re: Create a new observation from mathematical operations of other observations

Reeza — Mon, 07 Jul 2014 18:14:05 GMT

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;

Re: Create a new observation from mathematical operations of other observations

moreka — Mon, 07 Jul 2014 18:34:50 GMT

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;

Re: Create a new observation from mathematical operations of other observations

stat_sas — Mon, 07 Jul 2014 19:08:13 GMT

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;