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

[EAIAAUA]

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!

[PGStats]

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

[jh_ti_bw]

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

[Ksharp]

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

Check Mike Zdeb and me .

Xia Keshan