BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ujjawal
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

4 REPLIES 4
data_null__
Jade | Level 19

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;

  
Ksharp
Super User

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

data_null__
Jade | Level 19

Where is your proof?

xia keshan wrote:

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

ballardw
Super User

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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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