BookmarkSubscribeRSS Feed
Swordfish
Calcite | Level 5
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;
3 REPLIES 3
art297
Opal | Level 21
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
Super User
[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
Calcite | Level 5
Lots of thanks to both Art and Ksharp for their kind help.

regards

Tony

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 672 views
  • 0 likes
  • 3 in conversation