topic Re: Running Total with Missing Values in SAS Programming

Running Total with Missing Values

elwayfan446 — Thu, 31 Dec 2015 21:32:12 GMT

Hello,

I am trying to create a data set that includes a running total. In some observations, the value that is being added to the running total is missing. Instead of keeping the previous running total, the new running total value for the record that is missing is also missing.

I have been researching all day. I have tried the retain statement, just adding the running total to the next value (assuming that a missing value would act as Running Total=0) and nothing is working. I couldn't get it to work with proc sql either.

Any help would be appreciated.

Here is the code and current results I am getting:

Re: Running Total with Missing Values

mohamed_zaki — Thu, 31 Dec 2015 21:59:24 GMT

Your code and the results are not matching. How the first retianed variable is bigger than the first not missing values???

and many unnessesary statment is included like a=a;

The summing function and your code logic should handle the missing values?

Re: Running Total with Missing Values

elwayfan446 — Thu, 31 Dec 2015 22:02:13 GMT

Exactly my problem. I have no idea why they are not matching and why the summing function is not working. I tried that as well. I am hoping that someone can help me figure out what I am doing wrong. The running total should start with the first observation (earliest FALLOUT_DATE) and grow from there.

Re: Running Total with Missing Values

ballardw — Thu, 31 Dec 2015 22:02:55 GMT

I would first try only using one RETAIN for each variable. Multiples, especially when you have one initializing values and the other not may not be quite what you want.

Also, please post code, not pictures of code as we can't copy and paste elements for correction or suggestion.

I am not at all sure why there are statements like:

Fallout_date=Fallout_date;

Note_balance_20yr=Note_balance_20yr;

Waste of space.

Re: Running Total with Missing Values

mohamed_zaki — Thu, 31 Dec 2015 22:05:25 GMT

do you have any other proc or data step before or after this code in the workflow??

Re: Running Total with Missing Values

elwayfan446 — Thu, 31 Dec 2015 22:06:57 GMT

Here is the code.

As far as the waste of space issue, I was naming the variable for my new dataset (i.e Fallout_date=Fallout_date).

DATA FLOWFRED.FMFLOW_LOANSDROPPED_TOT_RPT;
   SET FLOWFRED.FMFLOW_LOANSDROPPED_RUN_TOT;
   RETAIN RUNNING_NOTE_BALANCE_15YR;
   RETAIN RUNNING_NOTE_BALANCE_20YR;
   RETAIN RUNNING_NOTE_BALANCE_30YR;
       FALLOUT_DATE=FALLOUT_DATE;
       NOTE_BALANCE_15YR=NOTE_BALANCE_15YR;
       retain RUNNING_NOTE_BALANCE_15YR 0;
       RUNNING_NOTE_BALANCE_15YR=SUM(RUNNING_NOTE_BALANCE_15YR,NOTE_BALANCE_15YR);
       NOTE_BALANCE_20YR=NOTE_BALANCE_20YR;
       retain RUNNING_NOTE_BALANCE_20YR 0;
       RUNNING_NOTE_BALANCE_20YR=SUM(RUNNING_NOTE_BALANCE_20YR,NOTE_BALANCE_20YR);
       NOTE_BALANCE_30YR=NOTE_BALANCE_30YR;
       retain RUNNING_NOTE_BALANCE_30YR 0;
       RUNNING_NOTE_BALANCE_30YR=SUM(RUNNING_NOTE_BALANCE_30YR,NOTE_BALANCE_30YR);
run;

proc print data=flowfred.fmflow_loansdropped_tot_rpt;
run;

Re: Running Total with Missing Values

elwayfan446 — Thu, 31 Dec 2015 22:07:40 GMT

Just the proc print I posted below

Re: Running Total with Missing Values

mohamed_zaki — Thu, 31 Dec 2015 22:13:13 GMT

Can you post snapshot from the first rows of the original dataset "FLOWFRED.FMFLOW_LOANSDROPPED_RUN_TOT"?

Re: Running Total with Missing Values

elwayfan446 — Thu, 31 Dec 2015 22:19:07 GMT

Sure, thanks.

As you can see... the running total works in the original data set (created with proc sql). The problem I have is that the running total doesn't carry forward if there is a missing value in NOTE_BALANCE_15YR. For example, on row 7 there is a missing value for NOTE_BALANCE_15YR so the RUNNING_NOTE_BALANCE_15YR is also missing. I need the RUNNING_NOTE_BALANCE_15YR from row 6 (FALLOUT_DATE 8/12/2015) to carry forward until the next record with a value occurs (row 12, FALLOUT_DATE 8/25/2015).

Re: Running Total with Missing Values

mohamed_zaki — Thu, 31 Dec 2015 22:30:33 GMT

Now i understand your problem.

The retained variables are already existing in your original data set and also contains data.

DATA FLOWFRED.FMFLOW_LOANSDROPPED_TOT_RPT;
    SET FLOWFRED.FMFLOW_LOANSDROPPED_RUN_TOT;
    RETAIN _RUNNING_NOTE_BALANCE_15YR;
    RETAIN _RUNNING_NOTE_BALANCE_20YR;
    RETAIN _RUNNING_NOTE_BALANCE_30YR;
        _RUNNING_NOTE_BALANCE_15YR=SUM(_RUNNING_NOTE_BALANCE_15YR,NOTE_BALANCE_15YR);
        _RUNNING_NOTE_BALANCE_20YR=SUM(_RUNNING_NOTE_BALANCE_20YR,NOTE_BALANCE_20YR);
        _RUNNING_NOTE_BALANCE_30YR=SUM(_RUNNING_NOTE_BALANCE_30YR,NOTE_BALANCE_30YR);
run;

Run the above code and you find the correct result in the new created variables starting with _ .

Re: Running Total with Missing Values

ballardw — Thu, 31 Dec 2015 22:34:47 GMT

Does your input data set FLOWFRED.FMFLOW_LOANSDROPPED_RUN_TOT have a variable already named Running_Note_Balance_15Yr? If so, then each time the input set is read it pulls that value from the input set.

If that is the case then either DROP the existing Running total variables on the set statement (data set option DROP)

SET FLOWFRED.FMFLOW_LOANSDROPPED_RUN_TOT (drop = RUNNING_NOTE_BALANCE_15YR RUNNING_NOTE_BALANCE_20YR RUNNING_NOTE_BALANCE_30YR );

or use entirely new variables in your Retain statement and calculations. And use only one retain statement per variable. And all of the variables could be on one statement:

Retain RUNNING_NOTE_BALANCE_15YR RUNNING_NOTE_BALANCE_20YR RUNNING_NOTE_BALANCE_30YR 0;

Re: Running Total with Missing Values

elwayfan446 — Thu, 31 Dec 2015 23:22:13 GMT

Thanks mohamed_zaki and ballardw... I will try this as soon as I can. I appreciate the help. I will mark this solved asap.

Happy New Year!

Re: Running Total with Missing Values

elwayfan446 — Fri, 01 Jan 2016 17:38:19 GMT

Thanks again, this worked great. However, now that I have the running totals, I would like to have everything sorted in descending order with the most recent date. That means I would like the running total descending along with the date. I can get the date sorted descending easily but the running total will not sort that way with the date. Any ideas?

Re: Running Total with Missing Values

Reeza — Fri, 01 Jan 2016 20:17:13 GMT

Sort first and then calculate your running total?

Re: Running Total with Missing Values

elwayfan446 — Mon, 04 Jan 2016 14:11:21 GMT

Thanks Reeza. After taking the sort out of my initial query this worked. I appreciate all of the answers everyone!