topic Re: Fill in the blanks with the previous value in SAS Procedures

Fill in the blanks with the previous value

sweetgorgor — Thu, 10 Oct 2013 21:47:13 GMT

Portfolio	Value	New_Value
A	2	2
A	3	3
A		3
A		3
B	2	2
B	2	2
B		2

Hi, I have a table with the first 2 columns (Portfolio, Value) where I want to create a 3rd column to fill in all the blanks with the previous (Latest value), by portfolio. Essentially I am trying to derive column 3. I tried to use Lag and retain in a data step I can't seem to get it to work. can someone help?

Thanks!

Re: Fill in the blanks with the previous value

Mit — Thu, 10 Oct 2013 23:34:11 GMT

data have;
input portfolio $ value;
cards;
A 2
A 3
A
A
B 2
B
B
B 4
B
B
,
run;

proc sort data=have ;
by portfolio;
run;

data want;
set have;
by portfolio;
retain new_value;
if not missing(value) then new_value=value;
run;

portfolio

value

new_value

Re: Fill in the blanks with the previous value

sam369 — Fri, 11 Oct 2013 02:10:46 GMT

Hi,

another apporach, i used Mit Have dataset

data have;

input portfolio : $1. value;

cards;

A 2

A 3

B 2

B 4

;

run;

data want;

do until(last.portfolio);

set have;

by portfolio notsorted;

new_val=ifn(value ne .,value,new_val);

output;

end;

run;

Thanks

Sam

Re: Fill in the blanks with the previous value

sweetgorgor — Fri, 11 Oct 2013 14:06:03 GMT

Thanks guys! I used Mit's and it worked like a charm! Sam yours also works! Thank you!