Desktop productivity for business analysts and programmers

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

Reply
Occasional Contributor
Posts: 9

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

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 lineWoman Embarassed)

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

Super User
Posts: 23,928

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

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

 

 

Occasional Contributor
Posts: 9

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

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

Super User
Posts: 10,846

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

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;
Ask a Question
Discussion stats
  • 3 replies
  • 242 views
  • 0 likes
  • 3 in conversation