Help using Base SAS procedures

Getting rid of all variables for which there are ONLY missing values

Reply
N/A
Posts: 1

Getting rid of all variables for which there are ONLY missing values

Hi,

I'm working with a dataset where some of the variables have only missing values associated with them. From this dataset, I would like to completely drop all of those particular variables. Is there a way to do this, or do I have to deal with each variable individually? I'm working with over 300 variables, and about 200 of them are the ones I want to drop, so doing a keep statement for the 100 other variables will still be way too time-consuming. I know I can do

if variable=. then delete

to get rid of my missing values, but I don't know how to tell SAS to get rid of all the variables that don't have any values associated.

Help would be greatly appreciated!!

Thank you!

Respected Advisor
Posts: 4,927

Re: Getting rid of all variables for which there are ONLY missing values

Yes you can automate that task (assuming you only want to get rid of empty NUMERIC variables) :

/* Add empty variables v1 and v2 to test dataset */

data test; set sashelp.class; call missing(v1, v2); run;

proc univariate data=test outtable=ot(keep=_var_ _nobs_) noprint;

var _numeric_;

run;

proc sql noprint;

select _var_ into :vList separated by " "

from ot where _nobs_=0;

quit;

data clean;

set test;

drop &vList;

run;

PG

PG
Contributor
Posts: 25

Re: Getting rid of all variables for which there are ONLY missing values

Hi,

the answer from PGStats works only for numeric variables. If you need a list of character or mixed type variables you can automate it with this macro:

/*

      Testdata

*/

data test;

  retain v1 . v2 " ";

  set sashelp.class;

run;

/*

      Macrodefinition

*/


%macro keep_not_null(data= , out=);

  %local varlist nvar keeplist droplist thisvar thisnr i dsid rc libpart mempart;

  %put;

  %let dsid = %sysfunc(open(&data.));

  %if &dsid. > 0 %then %do;

  Proc sql noprint;

    /*

      due to an inconsistency of

      function "attrn(dsid,NVAR)" and

      function "varname(dsid,pos)" by using

      dataset option "drop"

      i fetch the list of varnames from the sql dictionaries.

    */

    %let libpart = %Upcase(%sysfunc(attrc(&dsid., LIB)));

    %let mempart = %Upcase(%sysfunc(attrc(&dsid., MEM)));

      select name

      into :varlist separated by " "

      from dictionary.columns

      where upcase(libname) = "&libpart." and upcase(memname) =  "&mempart."

      ;

      %let nvar = &sqlobs.;

     

      /* for every variable in dataset counting the number of nonmissing */

      %do i = 1 %to &nvar.;

        %let thisvar = %scan(&varlist.,&i.,%str( ));

        /* probing of variable is in dataset */

        %if %sysfunc(varnum(&dsid.,&thisvar.)) > 0 %then %do;

          select count(&thisvar.) into :thisnr from  &libpart..&mempart.;

          %if &thisnr. > 0 %then %let keeplist = &keeplist. &thisvar.;

          %else %let droplist = &droplist. &thisvar.;

        %end; /* %if %sysfunc(varnum(&dsid.,&thisvar.)) > 0 */

      %end; /* %do i = 1 %to &nvar. */

    %let rc = %sysfunc(close(&dsid.));

   

    /* dropping the variables with missing values in the output dataset */

    %put Input dataset  = &data.;

    %put Output dataset = &out.;

    %put Keep variables = &keeplist.;

    %put Drop variables = &droplist.;

    %put;

    quit;

    data &out.;

      set &data.;

      %if %length(&keeplist.) > 0 %then %do;

        keep &keeplist.;

      %end; /* %if %length(&keeplist.) > 0 */

      %if %length(&droplist.) > 0 %then %do;

        drop &droplist.;

      %end; /* %if %length(&droplist.) > 0 */

    run;

  %end; /* %if &dsid. > 0 */

  %else %do;

    %put %STR(ERROR: Can%'t open the dataset "&data.");

    %abort;

  %end;

%mend;

/*

      Macro call

*/

option mprint;

%keep_not_null( data=test(drop=name) ,out=test_out);

option nomprint;

;

This macro proof for every variable, whether it contains only missing values or not.

LG

Jan

Super User
Posts: 10,041

Re: Getting rid of all variables for which there are ONLY missing values

This question has been discussed many times in this forum. Search it at this forum firstly.

Check Mike Zdeb and me .

Xia Keshan

Ask a Question
Discussion stats
  • 3 replies
  • 2073 views
  • 0 likes
  • 4 in conversation