Getting the average two by two

[msf2021]

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   average
1         13,14   8,86
2          4,59 
3          3,91   3,31
4          2,70 
5          2,70   2,59
6          2,47 
7          2,61   2,41
8          2,20 
9          2,38   2,11
10         1,84 

Can anyone help me please?

Thanks!

[Kurt_Bremser]

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;

[msf2021]

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

[Kurt_Bremser]

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.

[msf2021]

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!

[Kurt_Bremser · Accepted solution]

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;

[msf2021]

Thanks!!

[msf2021]

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

[Kurt_Bremser]

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.

[msf2021]

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

[Kurt_Bremser]

Add a KEEP= option for the look ahead:

have2 (firstobs=2 keep=lift rename=(lift=_lift))