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.
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;
Attaching your current program or log would help.
Basically, you should retain a separate (new) variable, and conditionally assign (to and from) it.
Please post sample data, as text, not images since we can't paste images into SAS to try anything.
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;
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.
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.
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?
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.
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;
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.
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;
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;
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.
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?
Add additional code patterned on that code that works for one value. Use variable names that make sense to you.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: