🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Fluorite | Level 6

## Getting the average two by two

Hi everyone,

I have a dataset with 2 columns :

```percentile	lift
1	        13,14
2	        4,59
3	        3,91
4	        2,70
5	        2,70
6	        2,47
7	        2,61
8	        2,20
9	        2,38
10	        1,84```

I would like to create the average two by two, like this:

1st : average of percentile 1 and 2

2nd: average of percentile 3 and 4

3rd: average of percentile 5 and 6

....:

`percentile lift   average1         13,14   8,862          4,59 3          3,91   3,314          2,70 5          2,70   2,596          2,47 7          2,61   2,418          2,20 9          2,38   2,1110         1,84 `

Can anyone help me please?

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Getting the average two by two

So we need to do a look-ahead also:

``````data have;
input lift :commax10.;
datalines;
1,83
1,53
1,05
1,38
1,23
1,17
1,44
1,08
0,99
1,26
1,29
1,41
0,78
0,96
;

data want;
merge
have
have (firstobs=2 rename=(lift=_lift))
;
average = ifn(mod(_N_,2)=0,(lift + lag(lift)) / 2,.);
retain flag 1;
if average ne . and average < 1 then flag = 0;
if average = . and (lift + _lift) / 2 < 1 then flag = 0;
drop _lift;
run;``````
10 REPLIES 10
Super User

## Re: Getting the average two by two

Use the MOD function to determine every second observation, and use the LAG() function within IFN:

``````data have;
input percentile lift :commax10.;
datalines;
1	        13,14
2	        4,59
3	        3,91
4	        2,70
5	        2,70
6	        2,47
7	        2,61
8	        2,20
9	        2,38
10	        1,84
;

data want;
set have;
average = ifn(mod(_N_,2)=0,(lift + lag(lift)) / 2,.);
run;``````

LAG within IFN is necessary to make sure that the LAG function is executed in every data step iteration.

Alternatively, you can do this:

``````data want;
set have;
average = (lift + lag(lift)) / 2;
if mod(_n_,2) = 1 then average = .;
run;``````
Fluorite | Level 6

## Re: Getting the average two by two

Thanks! My i ask how can i do a flag=1 until average is below 1? but flag=1 must be in all rows, even the ones with ".", until the average is below 1

Super User

## Re: Getting the average two by two

Add this to the code:

``````retain flag 1;
if average ne . and average < 1 then flag = 0;``````

The RETAIN initializes the variable to 1; this is kept until the condition is satisfied.

Fluorite | Level 6

## Re: Getting the average two by two

Thanks again for the explanaition!! Just one thing that i didn't explain:

I would like that the flag is set to 1 only for rows that make the average >=1 so for example in last two rows, flag should be zero because the average of last two lift's is <1

```LIFT	flag	average
1,83	1
1,53	1	1,675643327
1,05	1
1,38	1	1,211849192
1,23	1
1,17	1	1,196888091
1,44	1
1,08	1	1,256732496
0,99	1
1,26	1	1,122082585
1,29	1
1,41	1	1,346499102
0,78	1
0,96	0	0,867743866```

Thanks!

Super User

## Re: Getting the average two by two

So we need to do a look-ahead also:

``````data have;
input lift :commax10.;
datalines;
1,83
1,53
1,05
1,38
1,23
1,17
1,44
1,08
0,99
1,26
1,29
1,41
0,78
0,96
;

data want;
merge
have
have (firstobs=2 rename=(lift=_lift))
;
average = ifn(mod(_N_,2)=0,(lift + lag(lift)) / 2,.);
retain flag 1;
if average ne . and average < 1 then flag = 0;
if average = . and (lift + _lift) / 2 < 1 then flag = 0;
drop _lift;
run;``````
Fluorite | Level 6

## Re: Getting the average two by two

Thanks!!

Fluorite | Level 6

## Re: Getting the average two by two

I am loosing row number one here, is there a way that i don't loose it?

Super User

## Re: Getting the average two by two

The data step reads all observations and writes them, log:

``` NOTE: There were 14 observations read from the data set WORK.HAVE.
NOTE: There were 13 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 14 observations and 3 variables.
```
Fluorite | Level 6

## Re: Getting the average two by two

If we have another column it starts in the second one the output:

```data have;
input lift :commax10.;
datalines;
1,83
1,53
1,05
1,38
1,23
1,17
1,44
1,08
0,99
1,26
1,29
1,41
0,78
0,96
;

data have2;
set have;
i=_n_;
run;

data want;
merge
have2
have2 (firstobs=2 rename=(lift=_lift))
;
average = ifn(mod(_N_,2)=0,(lift + lag(lift)) / 2,.);
retain flag 1;
if average ne . and average < 1 then flag = 0;
if average = . and (lift + _lift) / 2 < 1 then flag = 0;
drop _lift;
run;```

Output:

```lift	i	average	flag
1.83	2	.	1
1.53	3	1.68	1
1.05	4	.	1
1.38	5	1.215	1
1.23	6	.	1
1.17	7	1.2	1
1.44	8	.	1
1.08	9	1.26	1
0.99	10	.	1
1.26	11	1.125	1
1.29	12	.	1
1.41	13	1.35	1
0.78	14	.	0
0.96	14	0.87	0```
Super User

## Re: Getting the average two by two

Add a KEEP= option for the look ahead:

``have2 (firstobs=2 keep=lift rename=(lift=_lift))``
Discussion stats
• 10 replies
• 938 views
• 1 like
• 2 in conversation