Conditional Imputation SAS -

Conditional Imputation SAS -

my info list of schools , performances on subject assessments, along percentage of gender enrolled in course. i've created sample info set below:

data have; input school $ subject $ perc_male perc_female score similar_school $; datalines; x math 51 49 93 y x english language 48 52 95 y x tech 60 40 90 y x science 57 43 92 y y math . . 87 x y english language . . 83 x y science . . 81 x y language . . 91 x z math 40 60 78 z z english language 50 50 76 z z science 45 55 80 z ; run;

as can see, no gender percentages collected school y. research shows school x has similar gender distribution, wish impute subject-specific percentages x y. problem y has score languages, while x did not take assessment. in case, wish take mean of imputed values (51, 48, 57) 52 percentages of language course-takers male.

executing demonstrate desired output:

data want; input school $ subject $ perc_male perc_female score; datalines; x math 51 49 93 y x english language 48 52 95 y x tech 60 40 90 y x science 57 43 92 y y math 51 49 87 x y english language 48 52 83 x y science 57 43 81 x y language 52 48 91 x z math 40 60 78 z z english language 50 50 76 z z science 45 55 80 z ; run;

got downvote, adding i've tried me need be. whoever downvoted, i'd know if have constructive feedback. thanks! i'm wondering if there way build in mean imputation part current snippet. plus, thinking there may more efficient way this. help appreciated.

proc sql; select distinct cats("'",similar_school,"'") :school_list separated ',' have perc_male=.; quit; proc sql; create table stuff select similar_school school, subject, perc_male, perc_female have school in (&school_list.); quit; proc sql; create table want2 select a.school, a.subject, coalesce(a.perc_male,b.perc_male), coalesce(a.perc_female,b.perc_female), a.score, a.similar_school have left bring together stuff b on a.school=b.school , a.subject=b.subject ; quit;

based on expected data, palin simple sql can solve problem. can first self bring together based on school , similar school info , coalesce perc_male & perc_female information. take care of first issue.. 2nd part of issue can calculate mean per school , coalesce perc_male & perc_female info respective mean of school. check out below sql , allow me know if helps.

proc sql; create table want select aa.school , aa.subject , coalesce(aa.perc_male, mean(aa.perc_male)) perc_male , coalesce(aa.perc_female,mean(aa.perc_female)) perc_female , score , similar_school ( select a.school , a.subject , coalesce(a.perc_male ,b.perc_male) perc_male , coalesce(a.perc_female,b.perc_female) perc_female , a.score , a.similar_school have left bring together have b on b.school=a.similar_school , a.subject=b.subject ) aa grouping aa.school ; quit;

sas