Hi all, I have quarterly data that is sparse that I am hoping to use SAS to fill. I devised this process in Excel but it is currently a very slow and manual process. I would like to move it to SAS and hopefully make it easy enough to refresh periodically. Basically, I want to take some sparse data (i.e. missing months) and either fill backward (or forward if that's easier) from the data points I do have in order to fill in desired months. In Excel I did this by forming the data into a grid (one row per client, one column per month) and reading from the data where the grid was nonzero, and pulling from the right if the data was non-zero. But I'm thinking this may not be the best way to do it in SAS. Does anyone have any ideas for general strategy to do this? Thanks! -------------------- Example data would look like the following: Table 1 Client Year Month Premium A 2012 3 5000 A 2012 6 5500 A 2012 12 5250 A 2013 3 5400 A 2013 6 5000 A 2013 9 5100 I have another table that might tell me that the effective months for our partnership with client A were December 2011 through August 2013. In this case, I'd like to see the following information in a new (i.e. as records in the format of Table 1) Yr/Mo Premium 12/2011 5000 (assume this didn't show up in data, I want to fill back) 1/2012 5000 (filled back) 2/2012 5000 (filled back) 3/2012 5000 (showed up in data) 4/2012 5000 or 5500 (fill forward or backward) 5/2012 5000 or 5500 (fill forward or backward) 6/2012 5500 (from data) 7/2012 5500 or 5250 (fill forward or backward) 8/2012 5500 or 5250 (fill forward or backward) 9/2012 5500 or 5250 (fill forward or backward--note it's missing from Table 1 data) 10/2012 5500 or 5250 (fill forward or backward) 11/2012 5500 or 5250 (fill forward or backward) 12/2012 5250 (from data) 1/2013 5250 or 5400 (fill forward or backward) ... 6/2013 5000 (from data) 7/2013 5000 or 5100 (fill forward or backward) 8/2013 5000 or 5100 (fill forward or backward) Note that there should be no entry for September 2013, as it is past the relationship period--the system providing my data can be a bit slow.
... View more