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!
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 |
A | 2 | 2 |
A | 3 | 3 |
A | 3 | |
B | 2 | 2 |
B | 2 | |
B | 4 | 4 |
B | 4 |
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 |
A | 2 | 2 |
A | 3 | 3 |
A | 3 | |
B | 2 | 2 |
B | 2 | |
B | 4 | 4 |
B | 4 |
Hi,
another apporach, i used Mit Have dataset
data have;
input portfolio : $1. value;
cards;
A 2
A 3
A
A
B 2
B
B
B 4
B
B
;
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
Thanks guys! I used Mit's and it worked like a charm! Sam yours also works! Thank you!
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.