Hi All,
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!.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.