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!
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;
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;
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
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.
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!
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;
Thanks!!
I am loosing row number one here, is there a way that i don't loose it?
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.
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
Add a KEEP= option for the look ahead:
have2 (firstobs=2 keep=lift rename=(lift=_lift))
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.