using sql to check missing rate for a long list of variables

[zzzyyy]

I want to check the missing rate for a long list of variables, about 800 variables.

Below is my code by using the sashelp dataset.

This code produce two tables mmm4 and mmm5 and show the result. Is there any way I can get one table to have the missing rates for all variables? ( And I don't want the results to be in report form by deleting the create table line)

data have;
  set sashelp.class;
  if _n_ in (5,10,12) then do;
    call missing(height,weight);
  end;
  call missing(age);
run;
proc sql ;
 select name into : list separated by ' '
  from dictionary.columns
   where libname='WORK' and memname='HAVE';
quit;


%macro horizon;
%local i item;
%*write the horizontal list;
%do I=4 %TO 5;
  %let item=%qscan(&list,&i,' ');
  %put item&i is &item;
proc sql;
create table mmm_&i as
select count(case when (&item =.)then 1 end) as &item
from have;
quit;
%end;
%mend horizon;
%horizon

Thanks

[Reeza]

I have two examples here, neither use SQL though.

This uses PROC FREQ and formats.

https://gist.github.com/statgeek/2de1faf1644dc8160fe721056202f111

This is an older method, the one above is more efficient:

https://gist.github.com/statgeek/c3a9ddcb002c469e9d61

[zzzyyy]

Thank you very much! Your two examples help a lot!

[Ksharp]

Here is an example.

data have;
  set sashelp.class;
  if _n_ in (5,10,12) then do;
    call missing(height,weight);
  end;
  call missing(age);
run;


 
proc format;
 value fmt
  ._-.z='missing'
  other='nonmiss';
 value $fmt
  ' '='missing'
  other='nonmiss';
run;
proc freq data=have;
table _all_/missing;
format _numeric_ fmt. _character_ $fmt.;
run;