Date: Mon, 18 Apr 2011 12:50:58 -0400 "Bian, Haikuo" "SAS(r) Discussion" "Bian, Haikuo" Re: Goal is to drag the column upward without affecting the last observation and finding the row wise averages. To: SUBSCRIBE SAS-L Anonymou <201104170203.p3GAmgFn000636@waikiki.cc.uga.edu> text/plain; charset="us-ascii"

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.

Back to: Top of message | Previous page | Main SAS-L page