DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 105
Accepted Solution

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

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:

ACCTdateDay_1_CurBalDay_2_CurBalDay_3_CurBalDay_4_CurBalDay_5_CurBal
acct102/04/2014$1,060,469.38....
acct102/05/2014.$1,042,084.95...
acct102/06/2014..$1,147,287.37..
acct102/07/2014...$1,134,815.78.
acct102/10/2014....$1,172,548.66
acct103/03/2014$1,226,216.86....
acct103/04/2014.$1,320,541.79...
acct103/05/2014..$1,124,167.01..
acct103/06/2014...$824,289.70.
acct103/07/2014....$755,211.07
acct104/01/2014$1,238,184.15....
acct104/02/2014.$1,449,083.39...
acct104/03/2014..$1,478,177.80..
acct104/04/2014...$1,540,631.15.
acct104/07/2014....$1,612,252.00
acct202/04/2014$180,320.43....
acct202/05/2014.$180,320.43...
acct202/06/2014..$180,320.43..
acct202/07/2014...$180,320.43.
acct202/10/2014....$178,014.18
acct203/03/2014$178,014.18....
acct203/04/2014.$254,889.18...
acct203/05/2014..$254,889.18..
acct203/06/2014...$254,889.18.
acct203/07/2014....$254,889.18
acct204/01/2014$52,582.93....
acct204/02/2014.$129,457.93...
acct204/03/2014..$129,457.93..
acct204/04/2014...$129,457.93.
acct204/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:

ACCTdateDay_1_CurBalDay_2_CurBalDay_3_CurBalDay_4_CurBalDay_5_CurBal
acct102/04/2014$1,060,469.38$1,042,084.95$1,147,287.37$1,134,815.78$1,172,548.66
acct102/05/2014$1,060,469.38$1,042,084.95$1,147,287.37$1,134,815.78$1,172,548.66
acct102/06/2014$1,060,469.38$1,042,084.95$1,147,287.37$1,134,815.78$1,172,548.66
acct102/07/2014$1,060,469.38$1,042,084.95$1,147,287.37$1,134,815.78$1,172,548.66
acct102/10/2014$1,060,469.38$1,042,084.95$1,147,287.37$1,134,815.78$1,172,548.66
acct103/03/2014$1,226,216.86$1,320,541.79$1,124,167.01$824,289.70$755,211.07
acct103/04/2014$1,226,216.86$1,320,541.79$1,124,167.01$824,289.70$755,211.07
acct103/05/2014$1,226,216.86$1,320,541.79$1,124,167.01$824,289.70$755,211.07
acct103/06/2014$1,226,216.86$1,320,541.79$1,124,167.01$824,289.70$755,211.07
acct103/07/2014$1,226,216.86$1,320,541.79$1,124,167.01$824,289.70$755,211.07
acct104/01/2014$1,238,184.15$1,449,083.39$1,478,177.80$1,540,631.15$1,612,252.00
acct104/02/2014$1,238,184.15$1,449,083.39$1,478,177.80$1,540,631.15$1,612,252.00
acct104/03/2014$1,238,184.15$1,449,083.39$1,478,177.80$1,540,631.15$1,612,252.00
acct104/04/2014$1,238,184.15$1,449,083.39$1,478,177.80$1,540,631.15$1,612,252.00
acct104/07/2014$1,238,184.15$1,449,083.39$1,478,177.80$1,540,631.15$1,612,252.00
acct202/04/2014$180,320.43$180,320.43$180,320.43$180,320.43$178,014.18
acct202/05/2014$180,320.43$180,320.43$180,320.43$180,320.43$178,014.18
acct202/06/2014$180,320.43$180,320.43$180,320.43$180,320.43$178,014.18
acct202/07/2014$180,320.43$180,320.43$180,320.43$180,320.43$178,014.18
acct202/10/2014$180,320.43$180,320.43$180,320.43$180,320.43$178,014.18
acct203/03/2014$178,014.18$254,889.18$254,889.18$254,889.18$254,889.18
acct203/04/2014$178,014.18$254,889.18$254,889.18$254,889.18$254,889.18
acct203/05/2014$178,014.18$254,889.18$254,889.18$254,889.18$254,889.18
acct203/06/2014$178,014.18$254,889.18$254,889.18$254,889.18$254,889.18
acct203/07/2014$178,014.18$254,889.18$254,889.18$254,889.18$254,889.18
acct204/01/2014$52,582.93$129,457.93$129,457.93$129,457.93$129,457.93
acct204/02/2014$52,582.93$129,457.93$129,457.93$129,457.93$129,457.93
acct204/03/2014$52,582.93$129,457.93$129,457.93$129,457.93$129,457.93
acct204/04/2014$52,582.93$129,457.93$129,457.93$129,457.93$129,457.93
acct204/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:

ACCTmonthDay_1_CurBalDay_2_CurBalDay_3_CurBalDay_4_CurBalDay_5_CurBal
acct1feb-2014$1,060,469.38$1,042,084.95$1,147,287.37$1,134,815.78$1,172,548.66
acct1march-2014$1,226,216.86$1,320,541.79$1,124,167.01$824,289.70$755,211.07
acct1apri-2014$1,238,184.15$1,449,083.39$1,478,177.80$1,540,631.15$1,612,252.00
acct2feb-2014$180,320.43$180,320.43$180,320.43$180,320.43$178,014.18
acct2march-2014$178,014.18$254,889.18$254,889.18$254,889.18$254,889.18
acct2apri-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


Accepted Solutions
Solution
‎06-05-2014 02:08 PM
Super User
Posts: 5,071

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

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.

View solution in original post


All Replies
Super User
Super User
Posts: 7,392

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

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;

Frequent Contributor
Posts: 105

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

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.

Super User
Posts: 5,071

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

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.

Frequent Contributor
Posts: 105

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

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!!

Super User
Posts: 5,071

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

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.

Frequent Contributor
Posts: 105

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

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):

ACCTdateDay_1_BalIndexDay_2_BalIndexDay_3_BalIndexDay_4_BalIndexDay_5_BalIndexDay_6_BalIndexDay_7_BalIndexDay_8_BalIndexDay_9_BalIndexDay_10_BalIndexDay_11_BalIndexDay_12_BalIndexDay_13_BalIndexDay_14_BalIndexDay_15_BalIndexDay_16_BalIndexDay_17_BalIndexDay_18_BalIndexDay_19_BalIndexDay_20_BalIndexDay_21_BalIndexDay_22_BalIndex
acct102/04/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/05/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/06/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/07/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/10/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/11/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/12/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/13/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/14/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/18/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/19/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/20/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/21/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/24/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/25/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/26/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/27/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct102/28/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/03/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/04/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/05/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/06/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/07/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/10/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/11/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/12/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/13/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/14/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/17/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/18/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/19/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/20/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/21/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/24/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/25/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/26/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/27/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/28/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct103/31/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/01/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/02/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/03/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/04/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/07/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/08/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/09/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/10/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/11/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/14/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/15/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/16/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/17/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/18/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/21/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/22/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/23/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/24/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/25/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/28/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/29/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
acct104/30/201410.8544602460.8376422310.8036863010.7679842540.8406620741.4433117261.6115323441.3835680251.8653826283.267349421.8968477992.2332964491.6028141411.2585675191.3408498591.4437211751.3835657991.5673106191.2449549871.4966902981.486971581
Solution
‎06-05-2014 02:08 PM
Super User
Posts: 5,071

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

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.

Frequent Contributor
Posts: 105

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

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!!!

Super User
Posts: 5,071

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

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!

Frequent Contributor
Posts: 105

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

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!

Frequent Contributor
Posts: 105

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

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!!

Super User
Posts: 5,071

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

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.

Frequent Contributor
Posts: 105

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

Okay that makes perfect sense. Thank you so much!

Trusted Advisor
Posts: 1,204

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

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 517 views
  • 7 likes
  • 4 in conversation