DATA Step, Macro, Functions and more

Computing Averages for values in different columns and different observations

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Computing Averages for values in different columns and different observations

Hi all,

I have a table like this:

A                B

-63261.57  -0.76
-0.75          0
0.01           68.63
68.64         252.5
252.51       488.93
488.94       787.53
787.54       1114.01
1114.02     1635.63
1635.64     2619.87
2619.91     64905.18

 

 

and I want to get the average of the record in the second column and the record in the following observation of the first column.
So for example, the average of -0.76 (row 1 column 2) and -0.75 (row 2 column 1), and so on. 

 

Any ideas on how to go about this? Also, I will not necessarily have 10 observations every time.

Thanks a million! 


Accepted Solutions
Solution
‎05-02-2018 08:58 AM
Super User
Super User
Posts: 9,416

Re: Computing Averages for values in different columns and different observations

Yes, minor change:

data want;
  set have;
  if _n_ > 0 then result=avg(lag(b),a);
run;

View solution in original post


All Replies
Super User
Super User
Posts: 9,416

Re: Computing Averages for values in different columns and different observations

Maybe something like (and not tested, present test data in the form of a datastep):

data want;
  set have;
  if _n_ ne 1 then result=avg(lag(b),a);
run;
Occasional Contributor
Posts: 11

Re: Computing Averages for values in different columns and different observations

Hi RW9,

This is working almost perfectly, but it doesn't calculate the very first possible entry. From the example I gave I get the following:

 

 

A                  B              Result

-63261.57    -0.76         .
-0.75            0               .                <-Here is the problem
0.01             68.63         0.005
68.64           252.5         68.635
252.51         488.93       252.505
488.94         787.53       488.935
787.54         1114.01     787.535
1114.02       1635.63     1114.015
1635.64       2619.87     1635.635
2619.91       64905.18   2619.89

 

Any idea about why this is happening/ how can I fix it?

Thanks

Solution
‎05-02-2018 08:58 AM
Super User
Super User
Posts: 9,416

Re: Computing Averages for values in different columns and different observations

Yes, minor change:

data want;
  set have;
  if _n_ > 0 then result=avg(lag(b),a);
run;
Super User
Posts: 6,632

Re: Computing Averages for values in different columns and different observations

Since _n_ will always be greater than zero, you could use:

 

result = avg(lag(b), a);

if _n_=1 then result = .;

Occasional Contributor
Posts: 11

Re: Computing Averages for values in different columns and different observations

Brilliant, thanks!
☑ This topic is solved.

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

Discussion stats
  • 5 replies
  • 98 views
  • 0 likes
  • 3 in conversation