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!
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.