Help using Base SAS procedures

Fill in the blanks with the previous value

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

Fill in the blanks with the previous value

PortfolioValueNew_Value
A22
A33
A 3
A 3
B22
B22
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
Frequent Contributor
Posts: 83

Re: Fill in the blanks with the previous value

Posted in reply to sweetgorgor

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

View solution in original post


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

Re: Fill in the blanks with the previous value

Posted in reply to sweetgorgor

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: 168

Re: Fill in the blanks with the previous value

Posted in reply to sweetgorgor

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

Re: Fill in the blanks with the previous value

Posted in reply to sweetgorgor

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 175 views
  • 6 likes
  • 3 in conversation