Replacing missing values

[skallamp]

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

[LinusH]

Retain would work but requires that you sort your data descending by year-month.

[skallamp]

for some reason its not working....


##- Please type your reply above this line. Simple formatting, no
attachments. -##

[BrunoMueller]

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

[Ksharp]

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;