## Fill in the blanks with the previous value

Solved
Occasional Contributor
Posts: 8

# Fill in the blanks with the previous value

 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!

Accepted Solutions
Solution
‎10-10-2013 07:34 PM
Frequent Contributor
Posts: 83

## Re: Fill in the blanks with the previous value

data have;
input portfolio \$ value;
cards;
A 2
A 3

B 2

B 4

,
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 A 2 2 A 3 3 A 3 B 2 2 B 2 B 4 4 B 4

All Replies
Solution
‎10-10-2013 07:34 PM
Frequent Contributor
Posts: 83

## Re: Fill in the blanks with the previous value

data have;
input portfolio \$ value;
cards;
A 2
A 3

B 2

B 4

,
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 A 2 2 A 3 3 A 3 B 2 2 B 2 B 4 4 B 4
Regular Contributor
Posts: 171

## Re: Fill in the blanks with the previous value

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

Occasional Contributor
Posts: 8