Hey guys, I have a question on something I've always wondered how to do in SAS. I have a data set that is grouped by account numbers and dates, with then a separate variable giving a balance for each day, like this: ACCT date Day_1_CurBal Day_2_CurBal Day_3_CurBal Day_4_CurBal Day_5_CurBal acct1 02/04/2014 $1,060,469.38 . . . . acct1 02/05/2014 . $1,042,084.95 . . . acct1 02/06/2014 . . $1,147,287.37 . . acct1 02/07/2014 . . . $1,134,815.78 . acct1 02/10/2014 . . . . $1,172,548.66 acct1 03/03/2014 $1,226,216.86 . . . . acct1 03/04/2014 . $1,320,541.79 . . . acct1 03/05/2014 . . $1,124,167.01 . . acct1 03/06/2014 . . . $824,289.70 . acct1 03/07/2014 . . . . $755,211.07 acct1 04/01/2014 $1,238,184.15 . . . . acct1 04/02/2014 . $1,449,083.39 . . . acct1 04/03/2014 . . $1,478,177.80 . . acct1 04/04/2014 . . . $1,540,631.15 . acct1 04/07/2014 . . . . $1,612,252.00 acct2 02/04/2014 $180,320.43 . . . . acct2 02/05/2014 . $180,320.43 . . . acct2 02/06/2014 . . $180,320.43 . . acct2 02/07/2014 . . . $180,320.43 . acct2 02/10/2014 . . . . $178,014.18 acct2 03/03/2014 $178,014.18 . . . . acct2 03/04/2014 . $254,889.18 . . . acct2 03/05/2014 . . $254,889.18 . . acct2 03/06/2014 . . . $254,889.18 . acct2 03/07/2014 . . . . $254,889.18 acct2 04/01/2014 $52,582.93 . . . . acct2 04/02/2014 . $129,457.93 . . . acct2 04/03/2014 . . $129,457.93 . . acct2 04/04/2014 . . . $129,457.93 . acct2 04/07/2014 . . . . $129,457.93 Really the rows extend out as there is a "day_x_curbal" for every day of the month, and we reset at the start of each month, I've just given a very small sample of the dataset here to just show the structure. What I'd like is for the missing values to all be populated with the (only) non-missing value for that month, so that the table above with result in this: ACCT date Day_1_CurBal Day_2_CurBal Day_3_CurBal Day_4_CurBal Day_5_CurBal acct1 02/04/2014 $1,060,469.38 $1,042,084.95 $1,147,287.37 $1,134,815.78 $1,172,548.66 acct1 02/05/2014 $1,060,469.38 $1,042,084.95 $1,147,287.37 $1,134,815.78 $1,172,548.66 acct1 02/06/2014 $1,060,469.38 $1,042,084.95 $1,147,287.37 $1,134,815.78 $1,172,548.66 acct1 02/07/2014 $1,060,469.38 $1,042,084.95 $1,147,287.37 $1,134,815.78 $1,172,548.66 acct1 02/10/2014 $1,060,469.38 $1,042,084.95 $1,147,287.37 $1,134,815.78 $1,172,548.66 acct1 03/03/2014 $1,226,216.86 $1,320,541.79 $1,124,167.01 $824,289.70 $755,211.07 acct1 03/04/2014 $1,226,216.86 $1,320,541.79 $1,124,167.01 $824,289.70 $755,211.07 acct1 03/05/2014 $1,226,216.86 $1,320,541.79 $1,124,167.01 $824,289.70 $755,211.07 acct1 03/06/2014 $1,226,216.86 $1,320,541.79 $1,124,167.01 $824,289.70 $755,211.07 acct1 03/07/2014 $1,226,216.86 $1,320,541.79 $1,124,167.01 $824,289.70 $755,211.07 acct1 04/01/2014 $1,238,184.15 $1,449,083.39 $1,478,177.80 $1,540,631.15 $1,612,252.00 acct1 04/02/2014 $1,238,184.15 $1,449,083.39 $1,478,177.80 $1,540,631.15 $1,612,252.00 acct1 04/03/2014 $1,238,184.15 $1,449,083.39 $1,478,177.80 $1,540,631.15 $1,612,252.00 acct1 04/04/2014 $1,238,184.15 $1,449,083.39 $1,478,177.80 $1,540,631.15 $1,612,252.00 acct1 04/07/2014 $1,238,184.15 $1,449,083.39 $1,478,177.80 $1,540,631.15 $1,612,252.00 acct2 02/04/2014 $180,320.43 $180,320.43 $180,320.43 $180,320.43 $178,014.18 acct2 02/05/2014 $180,320.43 $180,320.43 $180,320.43 $180,320.43 $178,014.18 acct2 02/06/2014 $180,320.43 $180,320.43 $180,320.43 $180,320.43 $178,014.18 acct2 02/07/2014 $180,320.43 $180,320.43 $180,320.43 $180,320.43 $178,014.18 acct2 02/10/2014 $180,320.43 $180,320.43 $180,320.43 $180,320.43 $178,014.18 acct2 03/03/2014 $178,014.18 $254,889.18 $254,889.18 $254,889.18 $254,889.18 acct2 03/04/2014 $178,014.18 $254,889.18 $254,889.18 $254,889.18 $254,889.18 acct2 03/05/2014 $178,014.18 $254,889.18 $254,889.18 $254,889.18 $254,889.18 acct2 03/06/2014 $178,014.18 $254,889.18 $254,889.18 $254,889.18 $254,889.18 acct2 03/07/2014 $178,014.18 $254,889.18 $254,889.18 $254,889.18 $254,889.18 acct2 04/01/2014 $52,582.93 $129,457.93 $129,457.93 $129,457.93 $129,457.93 acct2 04/02/2014 $52,582.93 $129,457.93 $129,457.93 $129,457.93 $129,457.93 acct2 04/03/2014 $52,582.93 $129,457.93 $129,457.93 $129,457.93 $129,457.93 acct2 04/04/2014 $52,582.93 $129,457.93 $129,457.93 $129,457.93 $129,457.93 acct2 04/07/2014 $52,582.93 $129,457.93 $129,457.93 $129,457.93 $129,457.93 OR, just as well, if I could just collapse by month, so that I got something this: ACCT month Day_1_CurBal Day_2_CurBal Day_3_CurBal Day_4_CurBal Day_5_CurBal acct1 feb-2014 $1,060,469.38 $1,042,084.95 $1,147,287.37 $1,134,815.78 $1,172,548.66 acct1 march-2014 $1,226,216.86 $1,320,541.79 $1,124,167.01 $824,289.70 $755,211.07 acct1 apri-2014 $1,238,184.15 $1,449,083.39 $1,478,177.80 $1,540,631.15 $1,612,252.00 acct2 feb-2014 $180,320.43 $180,320.43 $180,320.43 $180,320.43 $178,014.18 acct2 march-2014 $178,014.18 $254,889.18 $254,889.18 $254,889.18 $254,889.18 acct2 apri-2014 $52,582.93 $129,457.93 $129,457.93 $129,457.93 $129,457.93 Either way will serve my purposes just as well; I'm not sure which would be easier. I also realize the data could easily be transposed to be longitudinal, but for my visualization purposes I really need the balance variables separated by column like this. I thought that I could do this by simply using the retains/lag statements, but as I often find these functions do not act as I expect (not because I think something is wrong with these functions, just no matter how much material I read on them, they still seem to have effects that I do not expect at times). I've also tried using proc sql to group out 2 separate tables with the non-missing values and merge, but can't get even close to what I want. I feel like this shouldn't be very hard to do in SAS though, it seems like a fairly straight-forward data operation. Would anyone have any suggestions on this? I greatly appreciate any feedback whatsoever. Thanks so much!! -Ryan
... View more