The 48 new columns are the quarter/year from the 3rd quarter of 2002 to the 2nd quarter of 2012. The panel is going to be largely zeros. When the home sells the first time, the value in that row is "-1" and it must populate in the correct quarter/year column. On the same row, a value of "1" must be populated in the correct quarter and year column for the subsequent sale. I know that address is not usable but for illustration purposes, say I can use address as the key Address 1 Sold 02/16/2003: -1 in Q1_2003 and 0 in other 47 columns. Address 1 Sold 05/17/2006: in the same row as the first sale, retain -1 in Q1_2003, insert 1 in Q2_2006, and 0 in other 46 columns. This is relatively straightforward and can be accomplished with first.address and last.address and brute force copy and paste 48 if-then statements, lag them into 48 new columns, (lag log(price) at the same time), remove if lag(address) ne address and then use select-when to insert "1" using 48 new statements for the second occurrence. Some of this may be accomplished with a 2-dimensional array indexed as do year = 2002 to 2013; do quarter = 1 to 4; I think the main issue with these methods is that they will do not account for a second pair for the same property (i.e., need to know the 2nd of the pair is also the 1st of the next pair). Continuing the example: Address 1 Sold 10/24/2010: on a new row, -1 in Q2_2006, 1 in Q4_2010, and 0 in other 46 columns Address 2 Sold mm/dd/yyyy: recognize this as a new home and repeat above procedure Thanks! Darren
... View more
