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

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

elwayfan446 — Fri, 08 Jan 2016 15:35:13 GMT

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.

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

LinusH — Fri, 08 Jan 2016 15:45:37 GMT

Attaching your current program or log would help.

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

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

Reeza — Fri, 08 Jan 2016 15:49:05 GMT

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

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

elwayfan446 — Fri, 08 Jan 2016 16:21:41 GMT

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;

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

Astounding — Fri, 08 Jan 2016 16:56:44 GMT

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.

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

Steelers_In_DC — Fri, 08 Jan 2016 18:33:20 GMT

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;

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

elwayfan446 — Fri, 08 Jan 2016 21:46:05 GMT

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.

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

ballardw — Fri, 08 Jan 2016 23:04:03 GMT

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?

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

elwayfan446 — Fri, 08 Jan 2016 23:51:57 GMT

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.

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

Ksharp — Mon, 11 Jan 2016 04:56:16 GMT

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;

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

elwayfan446 — Mon, 11 Jan 2016 14:08:22 GMT

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.

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

elwayfan446 — Mon, 11 Jan 2016 16:28:32 GMT

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?

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

Astounding — Mon, 11 Jan 2016 17:54:55 GMT

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;

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

elwayfan446 — Mon, 11 Jan 2016 18:03:57 GMT

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.

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

ballardw — Mon, 11 Jan 2016 20:39:53 GMT

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

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

Ksharp — Tue, 12 Jan 2016 01:27:24 GMT

I know what is going on .

data have;
input TOTAL_NOTE_BALANCE_15YR ;
cards;
45746
43473
.
.
.
43011
;
run;

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

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

SAS_inquisitive — Wed, 13 Jan 2016 04:49:16 GMT

Hi Xia, How two DoW-loop is working here? Can you explain little bit? Thanks !

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

Ksharp — Wed, 13 Jan 2016 08:02:58 GMT

When _n_ eq 1 :

the first do until will rolling here

45746 <---
43473
.
.
.
43011
;

assign 45746 to new variable.

the second do until will output this obs , so new variable retain 45746 and export to WANT dataset.

When _n_ eq 2 :

the first do until will rolling here

45746 
43473 <---
.
.
.
43011
;

assign 43473 to new variable.

the second do until will output this obs , so new variable retain 43473 and export to WANT dataset.

When _n_ eq 3 :

the first do until will rolling here

45746 
43473 
.
.
.
43011 <---
;

assign 43011 to new variable.

the second do until will output the following 4 obs utill 43011 , so new variable retain 43011 and export to WANT dataset with these four obs .

etc...........

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

SAS_inquisitive — Wed, 13 Jan 2016 14:26:35 GMT

Thanks, Xia. So it is kind of nested DoW loop?

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

Ksharp — Thu, 14 Jan 2016 00:59:08 GMT

Yes. It is DOW skill has been mentioned by many people and many times.