BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sean100
Calcite | Level 5

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! 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, minor change:

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

View solution in original post

5 REPLIES 5
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;
Sean100
Calcite | Level 5

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, minor change:

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

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

 

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

if _n_=1 then result = .;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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