Retaining variable from previous observation when variable is missing in current observation

[elwayfan446]

Hello.  I am trying to figure out how to retain an amount from a previous observation when there is no amount available for the current observation.  In my example, on 12/31 there was an amount for $43,011,231 for the TOTAL_NOTE_BALANCE_15YR variable.  On 1/1 there was a missing variable for that field.  In that instance, I would like to carry forward the value from 12/31 and continue that through each day until there is a new value (so until we get to 1/4 when there is a new amount for $43,473,936.  I would like to be able to do this for all variables in the observation.

I have tried using the retain statement with no luck.  Not sure where to go from here.  Any help would be appreciated.

[LinusH]

Attaching your current program or log would help.

Basically, you should retain a separate (new) variable, and conditionally assign (to and from) it.

[Reeza]

Please post sample data, as text, not images since we can't paste images into SAS to try anything. 

[elwayfan446]

Thanks guys.  Here is the code I have been working with.  I also attached an excel file with the data.

proc sql;
    insert into FLOWFRED.FMFLOW_PIPELINE_HISTSUM_ARCHIVE
        select
            d.Date,
            ln.DATE_ADDED,
            ln.PCT_TOTAL_UPB_15YR,
            ln.PCT_TOTAL_UPB_20YR,
            ln.PCT_TOTAL_UPB_30YR,
            ln.TOTAL_NOTE_BALANCE_15YR,
            ln.TOTAL_NOTE_BALANCE_20YR,
            ln.TOTAL_NOTE_BALANCE_30YR
        from MSRCOM.MSRCOM_DATE_TABLE d
            left join FLOWFRED.FMFLOW_LOANSINPIPELINE_HISTSUM ln on ln.DATE_ADDED = d.Date
            left join FLOWFRED.FMFLOW_BEGIN_DATES bd on bd.DATE_MOST_RECENT = ln.DATE_ADDED
            /*left join FLOWFRED.FMFLOW_PIPELINE_HISTSUM_UPDARC ar on ar.Date = d.Date*/
        where d.Date <= bd.DATE_MOST_RECENT;
quit;

DATA FLOWFRED.FMFLOW_PIPELINE_HISTSUM_CHART;
    SET FLOWFRED.FMFLOW_PIPELINE_HISTSUM_ARCHIVE;
        retain PREV_TOTAL_NOTE_BALANCE_15YR 0;
        retain PREV_TOTAL_NOTE_BALANCE_20YR 0;
        retain PREV_TOTAL_NOTE_BALANCE_30YR 0;
        IF MISSING(TOTAL_NOTE_BALANCE_15YR) = 1 THEN TOTAL_NOTE_BALANCE_15YR=SUM(PREV_RUNNING_NOTE_BALANCE_15YR,TOTAL_NOTE_BALANCE_15YR);
                ELSE TOTAL_NOTE_BALANCE_15YR=TOTAL_NOTE_BALANCE_15YR;
run;

[Astounding]

If you were to search for an answer, the term "LOCF" would be a good starting place.   You might even find similar questions and solutions in this forum in recent months.

You begin the final step like this:

DATA FLOWFRED.FMFLOW_PIPELINE_HISTSUM_CHART;
    SET FLOWFRED.FMFLOW_PIPELINE_HISTSUM_ARCHIVE;
        retain PREV_TOTAL_NOTE_BALANCE_15YR 0;

You could continue it like this:

if TOTAL_NOTE_BALANCE_15YR > . then PREV_TOTAL_NOTE_BALANCE_15YR = TOTAL_NOTE_BALANCE_15YR;

else TOTAL_NOTE_BALANCE_15YR = PREV_TOTAL_NOTE_BALANCE_15YR;

You would need to apply similar logic for the remaining 3 variables.

Good luck.

[elwayfan446]

Thanks Astounding....

When I change my code to run this I get all zero's in that column.

I am running

DATA FLOWFRED.FMFLOW_PIPELINE_HISTSUM_CHART;
	SET FLOWFRED.FMFLOW_PIPELINE_HISTSUM_ARCHIVE;
		retain PREV_TOTAL_NOTE_BALANCE_15YR 0;
		IF TOTAL_NOTE_BALANCE_15YR > . THEN PREV_RUNNING_NOTE_BALANCE_15YR=TOTAL_NOTE_BALANCE_15YR;
				ELSE TOTAL_NOTE_BALANCE_15YR=PREV_TOTAL_NOTE_BALANCE_15YR;
run;

I attached new sample data from this run.  As you can see, the PREV_TOTAL_NOTE_BALANCE_15YR variable shows 0 for every observation.  The PREV_RUNNING_NOTE_BALANCE_15YR is only populating for the observations with data.  I need the data to carry over from the previous observation if the current is missing. So, 12/31/15 data (identical) through 1/3/2016 since those observations have no data. 

[ballardw]

I think you may be thinking of or naming one or more variables inconsisitently.

PREV_RUNNING_NOTE_BALANCE_15YR is not in your input data and is not retained and nothing is done withit except the assignment.

So what is the purpose of this variable?

[elwayfan446]

My understanding is that I would need to create it to hold the previous obvservation's variable.  If it is not necessary then it is not needed.  I am sure I was making it more complicated than it needs to be but I just can't get there.

[Astounding · Accepted solution]

You just typed in the wrong variable is all:

		IF TOTAL_NOTE_BALANCE_15YR > . THEN PREV_RUNNING_NOTE_BALANCE_15YR=TOTAL_NOTE_BALANCE_15YR;
				ELSE TOTAL_NOTE_BALANCE_15YR=PREV_TOTAL_NOTE_BALANCE_15YR;

Should be:

		IF TOTAL_NOTE_BALANCE_15YR > . THEN PREV_TOTAL_NOTE_BALANCE_15YR=TOTAL_NOTE_BALANCE_15YR;
				ELSE TOTAL_NOTE_BALANCE_15YR=PREV_TOTAL_NOTE_BALANCE_15YR;

[elwayfan446]

I can't believe I missed that.  Sometimes it just takes a second pair of eyes I guess.

I reran this and it works perfectly.  Thank you to everyone in this thread for your help.  It is much appreciated.  I love these forums.

[Steelers_In_DC]

Is there a unique identifier you are leaving out of your sample data?  If you have an ID you can use this:

data want;
update have(obs=0)
          have;
by id;
output;
run;

[Ksharp]

Code not tested.

data want;
 do until(not missing(TOTAL_NOTE_BALANCE_15YR ) or last);
   set have end=last;
 end;
   NEW_TOTAL_NOTE_BALANCE_15YR =TOTAL_NOTE_BALANCE_15YR ;
 do until(not missing(TOTAL_NOTE_BALANCE_15YR ) or last);
   set have end=last;
   output;
 end;
run;

[elwayfan446]

Thank you Xia,

This is a lot closer, however, it looks like it is populating the value of the next observation rather than the previous.

[elwayfan446]

Also, I have 2 more variables that I would need to do the same thing on for the same observations.  This seems to only look at TOTAL_NOTE_BALANCE_15YR.  How would I make this work for all of the variables and still look back at the previous number?

[ballardw]

Add additional code patterned on that code that works for one value. Use variable names that make sense to you.