Help using Base SAS procedures

PROC SQL to Impute missing values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

PROC SQL to Impute missing values

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 ;


Accepted Solutions
Solution
‎03-26-2014 02:27 PM
Occasional Contributor
Posts: 8

Re: PROC SQL to Impute missing values

That did it! Thank you so much!

View solution in original post


All Replies
Respected Advisor
Posts: 4,654

Re: PROC SQL to Impute missing values

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)

PG

PG
Solution
‎03-26-2014 02:27 PM
Occasional Contributor
Posts: 8

Re: PROC SQL to Impute missing values

That did it! Thank you so much!

☑ This topic is SOLVED.

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

Discussion stats
  • 2 replies
  • 734 views
  • 0 likes
  • 2 in conversation