In the blog post Replace Missing Values With Zero in SAS, I demonstrate two different methods to replace missing values with zero. Now, we generalize this concept a bit. For various reasons, you may want to replace missing values with different quantities or statistics. For example, you may want to replace missing values in a data set with the group mean for one or more numeric variables.
First, let us create a small example data set and see how we can do this efficiently in SAS.
data Missing_Values; input ID$ var1 var2 var3 @@; datalines; 1 . 3 4 1 4 . 2 1 . . . 1 2 8 . 2 2 0 . 2 5 . 1 2 . 4 . 3 . . 3 3 5 . 7 3 3 1 7 3 . . 2 3 3 . 7 3 . 1 9 ;
SAS Code Example
First we sort the data after the group variable ID.
proc sort data=Missing_Values; by ID; run;
Next, I use PROC STDIZE to replace the values with the group mean. I specify the data= and out= options to be the desired data set names. Then I use the REPONLY option to specify that I do not want any standardization done. By default, PROC STDIZE standardizes SAS data. Finally, I specify the missing=mean option to specify that I want to change the missing values with variable mean values. I use a By Statement in the procedure and specify ID as the by variable to put group mean values instead.
proc stdize data=Missing_Values out=Missing_Values_Mean reponly missing=mean; by ID; run;
I do not specify any variables in the procedure. Consequently, I replace missing values with group means for all numeric variables in the data set.
Furthermore, this method of replacing values with a group statistic is not limited to mean values. Consult the documentation for PROC STDIZE to see what other statistics you can replace missing values with.
Missing values are part of the game when you are dealing with data in SAS. Replacing these values can be the solution to your problem. But you should be aware, that you should only alter them when it actually makes sense.
Also, read the related posts Replace Missing Values With The Previous Non Missing and Mean Imputation in SAS Using the Hash Object.
You can download the entire code from this blog post here.