DATA Step, Macro, Functions and more

Create a new observation from mathematical operations of other observations

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

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: 17,745

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;

View solution in original post


All Replies
Solution
‎07-07-2014 02:14 PM
Super User
Posts: 17,745

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;

Trusted Advisor
Posts: 1,204

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 256 views
  • 0 likes
  • 3 in conversation