Solved
Contributor
Posts: 26

# Create a new observation from mathematical operations of other observations

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:

Var1Var2
SC1
NC3
TN5

Want:

Var1Var2
SC1
NC3
TN5
NotTN4

Thanks!

Accepted Solutions
Solution
‎07-07-2014 02:14 PM
Super User
Posts: 23,700

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

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;

All Replies
Solution
‎07-07-2014 02:14 PM
Super User
Posts: 23,700

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

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;

Contributor
Posts: 26

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

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;

Posts: 1,270

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

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;

🔒 This topic is solved and locked.