topic Re: Missing Values % for character variables in SAS Programming

Missing Values % for character variables

Ujjawal — Mon, 04 May 2015 19:01:26 GMT

I am trying to create a macro that drops variables having missing values % greater than 90. It is working fine for numeric variables. Since PROC MEANS does not work for character variables and PROC FREQ produces clumsy output, i ain't able to replice it for character variables.

data have;

set sashelp.class;

if _n_ in (5 10 12) then do;

call missing(height,weight);

end;

if _n_ in (7 9) then do;

call missing(age);

end;

r = ranuni(1);

run;

ods output summary=nmiss;

proc means data=have stackods nmiss n;

var _numeric_;

run;

ods output close;

proc print data=nmiss;

run;

%let pmisslist=;

proc sql noprint;

select variable into :pmisslist separated by ' '

from nmiss where nmiss/(sum(n,nmiss)) ge .15;

quit;

run;

%put NOTE: &=PMISSLIST;

Re: Missing Values % for character variables

data_null__ — Mon, 04 May 2015 20:27:27 GMT

This should get you started.

proc format; 
   value $onelevel ' '='?' other='1'; 
   run; 
ods select none; 
proc freq data=sashelp.heart;
   tables _character_ / missing; 
   format _character_ $onelevel.; 
   ods output onewayfreqs=freqs;
   run; 
ods select all; 
data freqs(keep=variable percent);
   set freqs;
   variable = vnamex(scan(table,-1,' '));
   if vvaluex('F_'||variable) eq '?' and percent gt 2; 
   run; 
proc print; 
   run; 

   

Re: Missing Values % for character variables

Ksharp — Tue, 05 May 2015 13:32:51 GMT

Proc freq is not efficient for large table , But SQL was.


data have;
   set sashelp.class;
   if _n_ in (1:18) then do;
      call missing(height,weight);
      end;
   if _n_ in (7 9) then do;
      call missing(age);
      end;
   r = ranuni(1);
   run;

proc sql;
 select cat('nmiss(',strip(name),')/count(*) as ',strip(name)) into : list separated by ','
  from dictionary.columns
   where libname='WORK' and memname='HAVE';

 create table temp as
  select &list from have;
quit;
proc transpose data=temp out=temp1;run;
proc sql;
select _NAME_ into : keep separated by ','
 from temp1
  where col1 lt .9 ;

create table want as 
 select &keep from have;
quit;

Xia Keshan

Re: Missing Values % for character variables

data_null__ — Tue, 05 May 2015 14:45:12 GMT

Where is your proof?

xia keshan wrote:

Proc freq is not efficient for large table , But SQL was.

Re: Missing Values % for character variables

ballardw — Tue, 05 May 2015 17:36:47 GMT

If you have many levels and that is the issue try a FORMAT for the character variables that reduces everything to 2 levels:

proc format library=work;

value $dummy

' '='Missing'

other='Not Missing';

run;

and

Proc freq data=have;

table charactervariablename/ missing ;

format charactervariablename dummy.;

run;