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

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

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

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.

 

Capture.JPG


Accepted Solutions
Solution
‎01-11-2016 01:04 PM
Esteemed Advisor
Posts: 5,007

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

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;

 

 

View solution in original post


All Replies
Esteemed Advisor
Posts: 5,202

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

Attaching your current program or log would help.

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

Data never sleeps
Grand Advisor
Posts: 17,465

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

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

 

Frequent Contributor
Posts: 138

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

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;

Esteemed Advisor
Posts: 5,007

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

[ Edited ]

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.

Frequent Contributor
Posts: 138

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

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. 

Grand Advisor
Posts: 10,253

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

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?

Frequent Contributor
Posts: 138

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

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.

Solution
‎01-11-2016 01:04 PM
Esteemed Advisor
Posts: 5,007

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

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;

 

 

Frequent Contributor
Posts: 138

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

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.

Valued Guide
Posts: 856

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

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;

Grand Advisor
Posts: 9,596

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

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;
Frequent Contributor
Posts: 138

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

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.

Capture.JPG

 

Frequent Contributor
Posts: 138

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

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?

Grand Advisor
Posts: 10,253

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

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

☑ This topic is SOLVED.

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

Discussion stats
  • 19 replies
  • 1152 views
  • 2 likes
  • 8 in conversation