topic Re: PROC SQL to Impute missing values in SAS Procedures

PROC SQL to Impute missing values

LOLO — Wed, 26 Mar 2014 16:19:04 GMT

Hi!

I'm trying to take the average of a variable and impute that value back into the variable whenever there is a missing value. This code below works except for that I wind up with two variables called cstelass_gr8 and readss_gr8. I need the imputed mean to go directly into the original variable.

proc sql ;

select cstelass_gr8, readss_gr8, avg(cstelass_gr8) as impmean_ca, avg(readss_gr8) as impmean_pa, i_priorachieve, i_statid,

case

when i_priorachieve= 0 and i_statid = "A"

then calculated impmean_ca

end as cstelass_gr8,

case

when i_priorachieve= 0 and i_statid = "B"

then calculated impmean_pa

end as readss_gr8

from a ;

quit ;

Re: PROC SQL to Impute missing values

PGStats — Wed, 26 Mar 2014 17:49:07 GMT

The SQL query in your message will not work, it isn't SQL. If you want to impute variables cstelass_gr8 and readss_gr8 with the overall mean, simply use:

proc sql ;

select

coalesce(cstelass_gr8, mean(cstelass_gr8)) as cstelass_gr8,

coalesce(readss_gr8, mean(readss_gr8)) as readss_gr8,

i_priorachieve,

i_statid

from a;

quit ;

(not tested)

Re: PROC SQL to Impute missing values

LOLO — Wed, 26 Mar 2014 18:27:55 GMT

That did it! Thank you so much!