Help using Base SAS procedures

Replace missing values with previous values

Accepted Solution Solved
Reply
Regular Contributor
Posts: 247
Accepted Solution

Replace missing values with previous values

Hi....I am trying to replace the missing values for the Start Date and Completion Date with the previous non missing dates and can't seem to get it to work. Any suggestions...thanks

 

 

StudentStatusID StudentUID Programs CompletionDate StartDate q
68509 16710 Technical Drafting 2014-06-20 2013-09-05 2013-09-05
68509 16710 Technical Drafting 2014-06-27 2013-09-02 2013-09-02
68509 16710 Technical Drafting 2014-06-20 2013-07-02 2013-07-02
68509 16710 Technical Drafting 2014-06-20 2013-07-02  
68509 16710 Technical Drafting 2014-06-20 2013-07-02  
68509 16710 Technical Drafting 2014-06-20 2013-07-02  
68509 16710 Technical Drafting 2014-06-20 2013-07-02  
68509 16710 Technical Drafting 2014-06-20 2013-07-02  
68509 16710 Technical Drafting 2014-06-20 2013-07-02 2013-07-02
68509 16710 Technical Drafting          .          .          .
68509 16710 Technical Drafting          .          .          .

 

 

proc sort data=StudentList26a;
by StudentUID StudentStatusID Programs descending StartDate;
run;

data StudentList26c;
do until(last.StudentUID);
set StudentList26a;
by StudentUID StudentStatusID Programs StartDate notsorted;
if last.StartDate and not missing(StartDate) then do;
q = StartDate;
end; 
if missing(StartDate) then do;
StartDate=q;
end; 
output;
end;
run;

Accepted Solutions
Solution
‎12-06-2017 11:21 AM
Super User
Posts: 6,785

Re: Replace missing values with previous values

I'm not sure you should be sorting in DESCENDING order, but that part is up to you.  Once you have sorted, here is an easy way:

 

data want;

update have (obs=0) have;

by StudentStatusID StudentUID Programs;

output;

run;

View solution in original post


All Replies
Respected Advisor
Posts: 3,066

Re: Replace missing values with previous values

You can use the RETAIN statement to achieve the result you want. See SAS help for examples.

 

You could also probably use the LAG statement.

--
Paige Miller
Solution
‎12-06-2017 11:21 AM
Super User
Posts: 6,785

Re: Replace missing values with previous values

I'm not sure you should be sorting in DESCENDING order, but that part is up to you.  Once you have sorted, here is an easy way:

 

data want;

update have (obs=0) have;

by StudentStatusID StudentUID Programs;

output;

run;

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 197 views
  • 0 likes
  • 3 in conversation