04-01-2017 08:11 AM - edited 04-01-2017 08:41 AM
I have a table with say 3 columns 1.Accno 2.month 3.Bal
What is required :
In this table i have records for each account where balance is missing for random months.(no of months considered : 24only)
What i needed is, to take the immediate next available balance , immediate preceeding available balance wrt the missing bal record, take an average of these values and populate this avg in place of the missing bal record.
table format :
Accno MOnth Bal
1 oct16 100
1 sep16 .
1. aug16 50
As you can see, sep16 has a missing bal. so, avg(oct16 bal ,aug16) is 2 be populated in sep16 record.
Thankx in advance for any help!.
04-01-2017 08:48 AM
What if there are multiple series missing value ,what you gonna do ? data have; input Accno MOnth $ Bal; cards; 1 oct16 100 2 sep16 . 3. aug16 50 ; run; data want; merge have have(keep=bal firstobs=2 rename=(bal=_bal)); lag=lag(bal); if missing(bal) then bal=mean(lag,_bal); drop _bal lag; run;