filling in missing values with the (only) non-missing value within a by-group(s)

[AllSoEasy]

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

[RW9]

Hi,

I am leaving now so no full example, however you want to look at the by statement in a datastep, with a retain or lag on the variables, like:

data want;

     set have;

     by acct date;

     retain Day_1_CurBal day_2_curbal_day...;

     if last.date then output;

run;

[AllSoEasy]

Hmm, I've tried this suggestion, however it had no effect on the dataset, it remains exactly the same as before running this datastep. I also tried re-ordering by date,acct and changing the output statement to last.acct and vice versa, but these don't seem to do anything other than when I use last.acct in the output statement all of the rows are deleted except 3, and all balance fields are missing.

[Astounding]

OK, here's a strange-looking DATA step.  It's untested, but it should do the trick.

data want;

   update have (obs=0) have;

   by acct;

   array balance {*} Day_:;

   if Day_5_CurBal > . then do;

      last_date = date;

      do until (date=last_date);

          set have (keep=date);

          output;

      end;

      do _n_=1 to dim(balance);

           balance{_n_}=.;

      end;

   end;

   drop last_date;

run;

If you have more than 5 dates, you may need to change the hard-coded reference to Day_5_CurBal.  Good luck.

[AllSoEasy]

Astounding,

Thank you very much! That is extremely helpful. It almost gives me exactly what I need. The only thing is that it's not change as the month changes. See, we reset at the start of each month, so there will be 3 different "day_1_curbal" (and day_2_curbal, day_2_curbal, etc) values for the same account if there are 3 months, this datastep you've provided keeps the same day_n_curbal values for all 3 months (the last month's). I tried adding 'date' to the by statement, and also tried creating a field, month = month(date), and grouping by this/editing the do-loop to use 'month' rather than 'date', but this does not provide expected results. Would you know how I could go about properly doing this? If not it's not a huge deal, because at the very least I can just separate the data into 3 separate datasets, one for each month, and then run your provided code on each individual 1-month dataset, and then merge them back together. However it would be nice to have it fully automated. Thank you again though, you are SAS genius as I would have never come close to coming up with that datastep!!

[Astounding]

AllSoEasy,

I'm having a difficult time figuring out how the results differ from what you need.  The data should re-set every time Day_5_CurBal is found, regardless of the month.  Perhaps if you posted the results you are getting for ACCT1 it would make more sense to me.

[AllSoEasy]

Astounding,

I think it is actually the fact that each month has a different number of days. I changed the code you provided from "day_5" to "day_22" as there are 22 days in the last month (these balances are only reported in the data on certain days, holidays/weekends/etc are excluded, so that's why there is such a varying number of days for each month), the 22 days happened to be the last month so that's why all I'm seeing are the last months balance fields. The first month has 18 days of balance values and the second has 21. So I'm assuming the way to go about this would just break the data into a separate dataset for each month, adjust your datastep code to hard-code the proper day number for each month, and then run it 3 separate times, for each month, and then merge the datasets back together by date. I'm not sure if it would be simple to modify your datastep to handle different numbers of days for each month, but the solution of repeating for each month in different datasets would satisfy my needs. Thank you again!

EDIT:

For your reference, these are the results for account 1 (here im using a "balindex" field rather than curbal, I need to do this on both, same exact idea/concept just different values):

ACCT	date	Day_1_BalIndex	Day_2_BalIndex	Day_3_BalIndex	Day_4_BalIndex	Day_5_BalIndex	Day_6_BalIndex	Day_7_BalIndex	Day_8_BalIndex	Day_9_BalIndex	Day_10_BalIndex	Day_11_BalIndex	Day_12_BalIndex	Day_13_BalIndex	Day_14_BalIndex	Day_15_BalIndex	Day_16_BalIndex	Day_17_BalIndex	Day_18_BalIndex	Day_19_BalIndex	Day_20_BalIndex	Day_21_BalIndex	Day_22_BalIndex
acct1	02/04/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/05/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/06/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/07/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/10/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/11/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/12/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/13/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/14/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/18/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/19/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/20/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/21/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/24/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/25/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/26/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/27/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	02/28/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/03/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/04/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/05/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/06/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/07/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/10/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/11/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/12/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/13/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/14/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/17/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/18/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/19/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/20/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/21/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/24/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/25/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/26/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/27/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/28/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	03/31/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/01/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/02/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/03/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/04/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/07/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/08/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/09/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/10/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/11/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/14/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/15/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/16/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/17/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/18/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/21/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/22/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/23/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/24/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/25/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/28/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/29/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581
acct1	04/30/2014	1	0.854460246	0.837642231	0.803686301	0.767984254	0.840662074	1.443311726	1.611532344	1.383568025	1.865382628	3.26734942	1.896847799	2.233296449	1.602814141	1.258567519	1.340849859	1.443721175	1.383565799	1.567310619	1.244954987	1.496690298	1.486971581

[Astounding · Accepted solution]

Given that each month will have a different number of days in it, it looks like you will need to add a variable MONTH to the data.  It can take on values 1 to 12, and related changes could be incorporated into the program in this way:

data want;

   update have (obs=0) have;

   by acct month notsorted;

   array balance {*} Day_:;

   if last.month then do;

      last_date = date;

      do until (date=last_date);

          set have (keep=date);

          output;

      end;

      do _n_=1 to dim(balance);

           balance{_n_}=.;

      end;

   end;

   drop last_date;

run;

Should be fine ... give it a shot and let's see.

[AllSoEasy]

Hmm I get the following error:

21            by acct month notsorted;

                            _________

                            287

ERROR 287-185: NOTSORTED/NOBYSORTED may not be used with MERGE or UPDATE statements.

However if I just remove the 'notsorted' keyword and sort the dataset by acct,month -- rather than acct,date -- it seems to work perfectly!

Thank you so much!!!

[Astounding]

That will get you by as long as you don't cross year boundaries.  You might need to create your MONTH variable in slightly different form, YYYY-MM for example.

Interesting problem!

[AllSoEasy]

Ahh yes that is true...For now I won't be crossing year boundaries, but that is an excellent suggestion, I will keep that in mind for the future in case we do end up going cross years. Thanks again!

[AllSoEasy]

Astounding -- Sorry, I'm not sure if you'll even still be reading this, but I have a question about your code. I was trying to wrap my head around why the code works, and I couldn't figure out the reason for the:

      do _n_=1 to dim(balance);

           balance{_n_}=.;

      end;

loop statement. I re-ran the code with this loop, and the creation of the 'balance{*}' array removed, and it still produced exactly the same results. Is this loop really needed, what exactly was it intended to do? (Either way your code works, I'd just like to understand better!)

Thanks!!

[Astounding]

That's true, you can remove the extra code.  In the original code, there was no MONTH variable.  So values of the Day_ variables were retained for the duration of an ACCT's records (and it was theoretically possible that the number of records in one month might be less than the number of records in the previous month).  Now that a MONTH variable is added to the BY statement, the UPDATE statement clears out the retained values for the Day_ variables when beginning to process a new combination of ACCT MONTH.  The final program becomes remarkably short considering the complexities built in:

data want;

   update have (obs=0) have;

   by acct month;

   if last.month then do;

      last_date = date;

      do until (date=last_date);

          set have (keep=date);

          output;

      end;

   end;

   drop last_date;

run;

Finally, notice that you can add to the variable list being kept (keep=last_date) if there are additional variables on the original records that need to be replicated.

[AllSoEasy]

Okay that makes perfect sense. Thank you so much!

[stat_sas]

Try this.

data have;
set test; /* Available dataset */
mon=month(date); /*Get month from date */
run;

proc sql;
create table want as
select a.*,b.Day_1_CurBal_,b.Day_2_CurBal_, b.Day_3_CurBal_, b.Day_4_CurBal_, b.Day_5_CurBal_
from have a
inner join (
select acct, mon,sum(Day_1_CurBal) as Day_1_CurBal_ , sum(Day_2_CurBal) as Day_2_CurBal_, sum(Day_3_CurBal) as Day_3_CurBal_,
sum(Day_4_CurBal) as Day_4_CurBal_ , sum(Day_5_CurBal) as Day_5_CurBal_
from have
group by acct,mon) b
on a.acct=b.acct
and a.mon=b.mon
order by a.acct, a.date;
quit;

data final(keep=acct date Day_1_CurBal Day_2_CurBal Day_3_CurBal Day_4_CurBal Day_5_CurBal);
set want;
array bal{5} Day_1_CurBal Day_2_CurBal Day_3_CurBal Day_4_CurBal Day_5_CurBal;
array bal_{5} Day_1_CurBal_ Day_2_CurBal_ Day_3_CurBal_ Day_4_CurBal_ Day_5_CurBal_;
do i=1 to dim(bal);
if bal{i}=. then bal{i}=bal_{i};
end;
run;

proc print data=final;
run;