Goal is to drag the column upward without affecting the last observation an

[Swordfish]

I have 3 rates data which are given below
rate1 rate2 rate3
40 . .
55 343 .
55 36 46
56 577 57
55 35 57
55 757 456
55 577 246
5 474 346
Goal is to drag the column upward without affecting the last observation and finding the row wise averages.
rate1 rate2 rate3 Averages
40 343 46 143.00
55 36 57 49 .33
55 577 57 229.67
56 35 456 182.33
55 757 246 352.67
55 577 . 316.00
55 . . 55.00
5 474 346
I have been successful to certain extent. I feel there is definite need to make it more compact and if there is easier way to handle this situation then please do share the same:
data rate1;
input rate1;
datalines;
40
55
55
56
55
55
55
5
;
data rate2;
input rate2;
datalines;
.
343
36
577
35
757
577
474
;
data rate3;
input rate3;
datalines;
.
.
46
57
57
456
246
346
;
data all_rates;
merge rate1 rate2 rate3;
run;
data lastobservation;
set all_rates end=last;
if last;
run;
data rate1;
set rate1 end=last;
if rate1=. then delete;
if last then delete;
run;
data rate2;
set rate2 end=last;
if rate2=. then delete;
if last then delete;
data rate3;
set rate3 end=last;
if rate3=. then delete;
if last then delete;
run;
data all_rates_table;
merge rate1 rate2 rate3;
Averages=mean(rate1, rate2, rate3);
run;
data final;
set all_rates_table lastobservation;
run;

[art297]

There are probably numerous ways of doing what you want. One possibility might be:

data want (keep=rate: averages);
set have nobs=nobs;
current_rec=_n_;
set have ( firstobs = 2 keep = rate2 rename = (rate2 = Next2) )
have ( obs = 1 drop = _all_ );
set have ( firstobs = 3 keep = rate3 rename = (rate3 = Next3) )
have ( obs = 1 drop = _all_ )
have ( obs = 1 drop = _all_ );
rate2 = ifn( current_rec eq nobs,rate2,
ifn(current_rec lt nobs-1, Next2,(.)));
rate3 = ifn( current_rec eq nobs,rate3,
ifn(current_rec lt nobs-2, Next3,(.)));
averages=ifn( current_rec lt nobs,mean(of rate:),(.));
run;

HTH,
Art
---------
> I have 3 rates data which are given below
> rate1 rate2 rate3
> 40 . .
> 55 343 .
> 55 36 46
> 56 577 57
> 55 35 57
> 55 757 456
> 55 577 246
> 5 474 346
> Goal is to drag the column upward without affecting
> the last observation and finding the row wise
> averages.
> rate1 rate2 rate3 Averages
> 40 343 46 143.00
> 55 36 57 49 .33
> 55 577 57 229.67
> 56 35 456 182.33
> 55 757 246 352.67
> 55 577 . 316.00
> 55 . . 55.00
> 5 474 346
> I have been successful to certain extent. I feel
> there is definite need to make it more compact and if
> there is easier way to handle this situation then
> please do share the same:
> data rate1;
> input rate1;
> datalines;
> 40
> 55
> 55
> 56
> 55
> 55
> 55
> 5
> ;
> data rate2;
> input rate2;
> datalines;
> .
> 343
> 36
> 577
> 35
> 757
> 577
> 474
> ;
> data rate3;
> input rate3;
> datalines;
> .
> .
> 46
> 57
> 57
> 456
> 246
> 346
> ;
> data all_rates;
> merge rate1 rate2 rate3;
> run;
> data lastobservation;
> set all_rates end=last;
> if last;
> run;
> data rate1;
> set rate1 end=last;
> if rate1=. then delete;
> if last then delete;
> run;
> data rate2;
> set rate2 end=last;
> if rate2=. then delete;
> if last then delete;
> data rate3;
> set rate3 end=last;
> if rate3=. then delete;
> if last then delete;
> run;
> data all_rates_table;
> merge rate1 rate2 rate3;
> Averages=mean(rate1, rate2, rate3);
> run;
> data final;
> set all_rates_table lastobservation;
> run;

[Ksharp]

[pre]
data rate1;
input rate1;
datalines;
40
55
55
56
55
55
55
5
;
data rate2;
input rate2;
datalines;
.
343
36
577
35
757
577
474
;
data rate3;
input rate3;
datalines;
.
.
46
57
57
456
246
346
;
data all_rates;
merge rate1 rate2 rate3;
run;
data temp;
set all_rates end=last;
if not last;
run;
data lastobservation;
set all_rates end=last;
if last;
run;
data want;
merge temp temp(keep=rate2 rename=(rate2=_rate2) where=(_rate2 is not missing))
temp(keep=rate3 rename=(rate3=_rate3) where=(_rate3 is not missing));
drop rate2 rate3 ;
run;
data want;
set want(rename=(_rate2=rate2 _rate3=rate3)) lastobservation;
average=mean(of rate:);
run;
[/pre]


Ksharp

[Swordfish]

Lots of thanks to both Art and Ksharp for their kind help.

regards

Tony