BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

19 REPLIES 19
LinusH
Tourmaline | Level 20

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
Reeza
Super User

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

 

elwayfan446
Barite | Level 11

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
PROC Star

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
Barite | Level 11

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
Super User

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
Barite | Level 11

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
PROC Star

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
Barite | Level 11

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
Barite | Level 11

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
Super User

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
Barite | Level 11

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

 

elwayfan446
Barite | Level 11

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
Super User

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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