Hi ,
I wanted to check percentage of missing values in a dataset by variable Category . I have used below code from other forum it worked and now i wanted same by variable Category (which has values A,b,C).
proc format;
value $ missfmt ' '="Missing"
other="Not Missing"
;
value nmissfmt . ="Missing"
other="Not Missing"
;
run;
data have;
set have;
format _numeric_ nmissfmt. _character_ $missfmt.;
run;
proc freq data=have;
run;
But here's my macro. It needs work since I chopped it together from another macro I use, so not all the code in here is actually required.
%macro sum_missing(libname, dsetin, dsetout);
*Delete old dataset;
proc datasets nodetails nolist;
delete &dsetout;
quit;
*Upcase all macro variables to have consistency;
data _null_;
call symput ("libname", put(upcase("&libname."), $8.));
call symput ("dsetin", put(upcase("&dsetin."), $32.));
run;
*Formats for missing for character and numeric variables;
proc format;
value $ missfmt ' '="Missing"
other="Not Missing"
;
value nmissfmt . ="Missing"
other="Not Missing"
;
run;
*Copy dataset and apply missing formats to it;
data step1;
set &libname..&dsetin;
format _character_ $missfmt. _numeric_ nmissfmt.;
run;
options mprint symbolgen;
*Get variable list;
proc sql noprint;
select name into : var_list separated by " "
from dictionary.columns
where upcase(libname) ="&libname"
and upcase(memname)="&dsetin";
*Get count of total number of observations into macro variable;
select count(*) into : obs_count
from &libname..&dsetin.;
quit;
*Start looping through the variable list here;
%let i=1;
%do %while (%scan(&var_list, &i, " ") ^=%str());
%let var=%scan(&var_list, &i, " ");
*Get format of missing;
data _null_;
set step1;
call symput("var_fmt", vformat(&var));
call symput("var_label", vlabel(&var));
run;
*Get count of missing;
proc freq data=step1 noprint;
table &var/missing out=cross_tab1;
run;
data cross_tab2;
set cross_tab1;
length variable $50.;
category=put(&var, &var_fmt.);
variable="&var_label.";
if _n_=1 and category='Not Missing' then do;;
Number_Missing=&obs_count-count;
Percent_Missing=Number_Missing/&obs_count.;
percent=percent/100;
output;
end;
else if _n_=1 and category='Missing' then do;
Number_Missing=count;
Percent_Missing=percent/100;
output;
end;
format percent: percent10.1;
keep variable Number_Missing Percent_Missing;
run;
proc append base=&dsetout data=cross_tab2 force;
run;
proc datasets nodetails nolist;
delete cross_tab1 cross_tab2;
run; quit;
*Increment counter;
%let i=%eval(&i+1);
%end; *Categorical;
proc datasets nodetails nolist;
delete step1;
run; quit;
%mend;
Can anyone please help ?
... View more
