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

Solved
Regular Contributor
Posts: 161

# 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.

Accepted Solutions
Solution
‎01-11-2016 01:04 PM
Super User
Posts: 6,751

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

Posted in reply to elwayfan446

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;``````

All Replies
Super User
Posts: 5,876

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

Posted in reply to elwayfan446

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
Super User
Posts: 23,663

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

Posted in reply to elwayfan446

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

Regular Contributor
Posts: 161

## 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;

Super User
Posts: 6,751

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

[ Edited ]
Posted in reply to elwayfan446

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.

Regular Contributor
Posts: 161

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

Posted in reply to Astounding

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.

Super User
Posts: 13,498

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

Posted in reply to elwayfan446

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?

Regular Contributor
Posts: 161

## 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
Super User
Posts: 6,751

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

Posted in reply to elwayfan446

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;``````

Regular Contributor
Posts: 161

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

Posted in reply to Astounding

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

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

Posted in reply to elwayfan446

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;

Super User
Posts: 10,761

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

Posted in reply to elwayfan446

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;``````
Regular Contributor
Posts: 161

## 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.

Regular Contributor
Posts: 161

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

Posted in reply to 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?

Super User
Posts: 13,498

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

Posted in reply to elwayfan446

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

🔒 This topic is solved and locked.

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

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