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
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
Thank you very much! Your two examples help a lot!
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.