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

I am trying to do average. 

1. When the value starts at least 171, I would like to see if the next value is above 200. 

Option #1 - If the value >200 then I would like to see if the next value after that is also >200. If it is then I want to take average of those too or atleast FLAG them as yes.

Option #2 - if the value is between 171 and 200 then I would like to take average of those two numbers only

Option #3 - if it is <171 then skip to entries to see if they are >171 

 

Sample:

IDVALUESEQ_ID
11531
11712
12013
11504
11745
21431
21572
21753
22054
22105
21536
31541
31842
31533
31864
31785
31336

Here's an output:

 

IDVALUESEQ_IDaverage
11531 
11712186
12013 
11504 
11745 
21431 
21572 
21753 
22054208
22105 
21536 
31541 
31842 
31533 
31864182
31785 
31336 

 

THANK YOU!!

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

 

data have;
input ID$   value  seq_id;
cards;
1	153	1
1	171	2
1	201	3
1	150	4
1	174	5
2	143	1
2	157	2
2	175	3
2	205	4
2	210	5
2	153	6
3	154	1
3	184	2
3	153	3
3	186	4
3	178	5
3	133	6
;

data want;
n=_n_+1;
set have;
set have(keep=id value rename=(value=_value) ) point=n;
run;

data want2;
length cnt $10;
set want;
by id;
retain cnt val;
if last.id then _value=.;
if first.id then do;cnt='';val=.;end;
if value >=171 and _value>=200 then check='1';
if value in (171:200) and _value in (171:200) then check='1';
if check ne '' then cnt=cats(check,cnt);
if check='' then cnt='';
if cnt in ('1','11') then do;
val=round(sum(value,_value)/2);
end;
if check='' then val=.;
if cnt ne '';
run;

data want3;
set want2;
by id ;
if last.id ;
run;

proc sort data=want;
by id seq_id ;
run;

data want4(drop=cnt _value );
merge want(in=a) want3(in=b keep=id val cnt seq_id);
by id seq_id;
if a;
run;
Thanks,
Jag

View solution in original post

2 REPLIES 2
avama
Calcite | Level 5
 
Jagadishkatam
Amethyst | Level 16

 

data have;
input ID$   value  seq_id;
cards;
1	153	1
1	171	2
1	201	3
1	150	4
1	174	5
2	143	1
2	157	2
2	175	3
2	205	4
2	210	5
2	153	6
3	154	1
3	184	2
3	153	3
3	186	4
3	178	5
3	133	6
;

data want;
n=_n_+1;
set have;
set have(keep=id value rename=(value=_value) ) point=n;
run;

data want2;
length cnt $10;
set want;
by id;
retain cnt val;
if last.id then _value=.;
if first.id then do;cnt='';val=.;end;
if value >=171 and _value>=200 then check='1';
if value in (171:200) and _value in (171:200) then check='1';
if check ne '' then cnt=cats(check,cnt);
if check='' then cnt='';
if cnt in ('1','11') then do;
val=round(sum(value,_value)/2);
end;
if check='' then val=.;
if cnt ne '';
run;

data want3;
set want2;
by id ;
if last.id ;
run;

proc sort data=want;
by id seq_id ;
run;

data want4(drop=cnt _value );
merge want(in=a) want3(in=b keep=id val cnt seq_id);
by id seq_id;
if a;
run;
Thanks,
Jag

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 849 views
  • 0 likes
  • 2 in conversation