DATA Step, Macro, Functions and more

Missing Values % for character variables

Accepted Solution Solved
Reply
Regular Contributor
Posts: 181
Accepted Solution

Missing Values % for character variables

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 Smiley Tonguemisslist separated by ' '

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

   quit;

   run;

%put NOTE: &=PMISSLIST;


Accepted Solutions
Solution
‎05-05-2015 09:32 AM
Super User
Posts: 9,676

Re: Missing Values % for character variables

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

View solution in original post


All Replies
Respected Advisor
Posts: 3,777

Re: Missing Values % for character variables

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;

  
Solution
‎05-05-2015 09:32 AM
Super User
Posts: 9,676

Re: Missing Values % for character variables

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

Respected Advisor
Posts: 3,777

Re: Missing Values % for character variables

Where is your proof?

xia keshan wrote:

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

Super User
Posts: 10,500

Re: Missing Values % for character variables

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 266 views
  • 3 likes
  • 4 in conversation