DATA Step, Macro, Functions and more

Replacing missing values

Reply
Contributor
Posts: 32

Replacing missing values

I would like to replace missing values of score based on the ID and date.

Below is the example. You will notice that I have replace ID 3001, 201601 with 3.7.

How can we achieve this with retain or lag statement? Is there any other technique?

 

Before

 

ID mo_id Score
1001 201601 3.5
1001 201602 3.6
1001 201603  
1001 201604 3.7
1001 201605 3.2
1001 201606  
1001 201607  
1001 201608 3.4
2001 201601 3.5
2001 201602  
2001 201603 3.6
2001 201604 3.7
2001 201605 3.2
2001 201606  
2001 201607  
2001 201608 3.6
3001 201601  
3001 201602 3.7
3001 201603  
3001 201604 3.7
3001 201605 3.2
3001 201606  
3001 201607 3.9
3001 201608  
4001 201601  
3001 201602 3.6
3001 201603  
3001 201604 3.7
3001 201605 3.2
3001 201606  
3001 201607  
3001 201608  

 

Retain

 

ID mo_id Score
1001 201601 3.5
1001 201602 3.6
1001 201603 3.6
1001 201604 3.7
1001 201605 3.2
1001 201606 3.2
1001 201607 3.2
1001 201608 3.4
2001 201601 3.5
2001 201602 3.5
2001 201603 3.6
2001 201604 3.7
2001 201605 3.2
2001 201606 3.2
2001 201607 3.2
2001 201608 3.6
3001 201601 3.7
3001 201602 3.7
3001 201603 3.7
3001 201604 3.7
3001 201605 3.2
3001 201606 3.2
3001 201607 3.9
3001 201608 3.9
4001 201601 3.6
3001 201602 3.6
3001 201603 3.6
3001 201604 3.7
3001 201605 3.2
3001 201606 3.2
3001 201607 3.2
3001 201608 3.2

 

Super User
Posts: 5,424

Re: Replacing missing values

Retain would work but requires that you sort your data descending by year-month.
Data never sleeps
Contributor
Posts: 32

Re: Replacing missing values

for some reason its not working....


##- Please type your reply above this line. Simple formatting, no
attachments. -##
SAS Super FREQ
Posts: 708

Re: Replacing missing values

Hi

 

You could use the SUM statement, this will retain the value automatically and ignore expressions that result in missing (.)

 

data have;
  infile cards missover;
  seqNr = _n_;
  input
    ID
    mo_id
    Score
  ;
cards;
1001  201601  3.5
1001  201602  3.6
1001  201603   
1001  201604  3.7
1001  201605  3.2
1001  201606   
1001  201607   
1001  201608  3.4
2001  201601  3.5
2001  201602   
2001  201603  3.6
2001  201604  3.7
2001  201605  3.2
2001  201606   
2001  201607   
2001  201608  3.6
3001  201601   
3001  201602  3.7
3001  201603   
3001  201604  3.7
3001  201605  3.2
3001  201606   
3001  201607  3.9
3001  201608   
4001  201601   
3001  201602  3.6
3001  201603   
3001  201604  3.7
3001  201605  3.2
3001  201606   
3001  201607   
3001  201608   
;

data want;
  set have;
  score2 + (-score2 + score);
run;

Bruno

Super User
Posts: 10,020

Re: Replacing missing values


data have;
  infile cards missover;
  n = _n_;
  input
    ID
    mo_id
    Score
  ;
cards;
1001  201601  3.5
1001  201602  3.6
1001  201603   
1001  201604  3.7
1001  201605  3.2
1001  201606   
1001  201607   
1001  201608  3.4
2001  201601  3.5
2001  201602   
2001  201603  3.6
2001  201604  3.7
2001  201605  3.2
2001  201606   
2001  201607   
2001  201608  3.6
3001  201601   
3001  201602  3.7
3001  201603   
3001  201604  3.7
3001  201605  3.2
3001  201606   
3001  201607  3.9
3001  201608   
4001  201601   
3001  201602  3.6
3001  201603   
3001  201604  3.7
3001  201605  3.2
3001  201606   
3001  201607   
3001  201608   
;

data temp;
  set have;
  by id notsorted ;
  retain x;
  if first.id then call missing(x);
  if not missing(score) then x=score;
run;
proc sort data=temp;by descending n;run;
data want;
  set temp;
  by id notsorted ;
  retain new_score;
  if first.id then call missing(new_score);
  if not missing(x) then new_score=x;
  drop x;
run;
proc sort data=want;by n;run;


Ask a Question
Discussion stats
  • 4 replies
  • 362 views
  • 0 likes
  • 4 in conversation