topic Re: using sql to check missing rate for a long list of variables in SAS Enterprise Guide

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

zzzyyy — Tue, 10 Oct 2017 15:06:36 GMT

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

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

Reeza — Tue, 10 Oct 2017 15:12:35 GMT

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

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

zzzyyy — Tue, 10 Oct 2017 15:34:16 GMT

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

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

Ksharp — Wed, 11 Oct 2017 14:22:46 GMT

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;