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

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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

10 REPLIES 10
Kurt_Bremser
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

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
Super User

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
Fluorite | Level 6

Thanks!!

msf2021
Fluorite | Level 6

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

Kurt_Bremser
Super User

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
Fluorite | Level 6

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

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
  • 10 replies
  • 938 views
  • 1 like
  • 2 in conversation