Good afternoon, everyone, After reviewing the many entries regarding filling in missing values, I could not find anything that would help me with my conundrum. This is a fairly specific case of a more general problem of filling in missing observations in a panel, so bear with me and thank you for any help you might be able to provide. I am working with loan data and am trying to account for an instance where many loans are not reported for a specific lender, for several periods, but different number of periods depending on the loan. Here is the detail (data sample at the bottom of the post): 1) Data is sorted by consumer ID, then loan ID, then quarter. 2) Different loans belong to different lenders, who have lender IDs associated with them. 3) The panel is not balanced, as the loans span different time periods and there are occasional missing values. 4) I know that a specific lender, in the example below "Purple" had a good chunk (but not all) of their loans go missing in Q1-2011, and the loans trickled back in over the next four quarters, with virtually all of them returning by Q1-2012. For example, loan Z is missing for four quarters, loan B for two quarters, and loan C for one quarter. Loan Y, on the other hand, is reported every period during this time. 5) Occasionally, the loan ID changes, or the loan is transferred to a different lender - e.g. loan C was with lender Purple before it went missing in Q1-2011, and with lender Pink once it came back. I do NOT want to fill in rows in those cases. 6) I also do not want to fill in for any other loans/lenders, or different time periods for lender Purple, e.g. loan X with lender Blue is missing for one quarter, and I want to leave that as is. 7) For inserted rows, I want to fill in missing values with the values from Q4-2010, before the loans went missing. 😎 I have a large number of numeric and string variables in addition to the panel identifiers. If the loans were missing for the same amount of time, and if the loan IDs and lenders on the loans did not change, this would be a relatively simple problem. But these two issues are tripping me up. I have looked into the EXPAND procedure, but it does not appear to do what I would expect. If you have read this far, THANK YOU! I would appreciate any advice the group may be able to offer. Below is the sample data: Client Loan Lender Quarter NumVar StringVar 123 X Blue Q3-2010 3 Summer 123 X Blue Q4-2010 7 Winter 123 X Blue Q1-2011 34 Fall 123 X Blue Q3-2011 7 Summer 123 X Blue Q4-2011 3 Winter 123 X Blue Q1-2012 5 Fall 123 X Blue Q2-2012 2 Spring 123 X Blue Q3-2012 6 Summer 123 X Blue Q4-2012 5 Winter 123 Y Purple Q1-2010 6 Fall 123 Y Purple Q2-2010 3 Spring 123 Y Purple Q3-2010 5 Summer 123 Y Purple Q4-2010 6 Winter 123 Y Purple Q1-2011 3 Fall 123 Y Purple Q2-2011 7 Spring 123 Y Purple Q3-2011 53 Summer 123 Y Purple Q4-2011 5 Winter 123 Y Purple Q1-2012 7 Fall 123 Y Purple Q2-2012 3 Spring 123 Z Purple Q1-2010 6 Fall 123 Z Purple Q2-2010 5 Spring 123 Z Purple Q3-2010 6 Summer 123 Z Purple Q4-2010 3 Winter 123 Z Purple Q1-2012 55 Fall 123 Z Purple Q2-2012 5 Spring 123 Z Purple Q3-2012 7 Summer 123 Z Purple Q4-2012 3 Winter 456 A Green Q4-2010 5 Winter 456 A Green Q1-2011 6 Fall 456 A Green Q2-2011 3 Spring 456 A Green Q3-2011 5 Summer 456 A Green Q4-2011 6 Winter 456 A Green Q1-2012 3 Fall 456 A Green Q2-2012 7 Spring 456 A Green Q3-2012 23 Summer 456 A Green Q4-2012 5 Winter 456 B Purple Q1-2010 7 Fall 456 B Purple Q2-2010 3 Spring 456 B Purple Q3-2010 5 Summer 456 B Purple Q4-2010 2 Winter 456 B Purple Q3-2011 6 Summer 456 B Purple Q4-2011 3 Winter 456 B Purple Q1-2012 5 Fall 456 B Purple Q2-2012 6 Spring 456 B Purple Q3-2012 3 Summer 456 B Purple Q4-2012 7 Winter 456 C Purple Q1-2010 17 Fall 456 C Purple Q2-2010 5 Spring 456 C Purple Q3-2010 7 Summer 456 C Purple Q4-2010 3 Winter 456 C Pink Q2-2011 2 Spring 456 C Pink Q3-2011 6 Summer 456 C Pink Q4-2011 5 Winter 456 C Pink Q1-2012 6 Fall 456 C Pink Q2-2012 3 Spring 456 C Pink Q3-2012 4 Summer
... View more
