BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AllSoEasy
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

14 REPLIES 14
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
Astounding
PROC Star

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

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
Obsidian | Level 7

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
PROC Star

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
Obsidian | Level 7

Okay that makes perfect sense. Thank you so much!

stat_sas
Ammonite | Level 13

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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