|
Hi,
The approach that I have tried is a bit lengthier, while the concept involved is more basic. The assumption is that OP wants to fill up all of the missing values by moving up the rows except the last one. It is easier for me to do a transpose first, then deal with different variables instead of different records. The following codes will work on different missing scenarios besides OP's, as well as variables being more than 3.
data have;
infile cards;
input rate1 rate2 rate3;
cards;
40 . .
55 343 .
55 36 46
56 577 57
55 35 57
55 757 456
55 577 246
5 474 346
;;;
run;
proc transpose data=have out=need1;
run;
data need2 (drop= i j a);
set need1;
array tr (*) _numeric_;
do i=1 to dim(tr)-1;
if tr(i)=. then do;
again: a+1;
if a>dim(tr) then return;
do j=i to dim(tr)-2;
tr(j)=tr(j+1);
end;
tr(dim(tr)-1)=.;
end;
if i< dim(tr)-1 and tr(i)=. then goto again;
end;
a=0;
run;
proc transpose data=need2 out=need3 (drop=_name_);
run;
data need;
set need3;
avg=mean(of rate1-rate3);
run;
proc print; run;
Regards;
Haikuo
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of SUBSCRIBE SAS-L Anonymou
Sent: Saturday, April 16, 2011 10:03 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Goal is to drag the column upward without affecting the last observation and finding the row wise averages.
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;
-----------------------------------------
Email messages cannot be guaranteed to be secure or error-free as
transmitted information can be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
Centers for Medicare & Medicaid Services therefore does not accept
liability for any error or omissions in the contents of this
message, which arise as a result of email transmission.
CONFIDENTIALITY NOTICE: This communication, including any
attachments, may contain confidential information and is intended
only for the individual or entity to which it is addressed. Any
review, dissemination, or copying of this communication by anyone
other than the intended recipient is strictly prohibited. If you
are not the intended recipient, please contact the sender by reply
email and delete and destroy all copies of the original message.
| 
