topic Re: Getting rid of all variables for which there are ONLY missing values in SAS Procedures

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

EAIAAUA — Sun, 05 Oct 2014 23:18:53 GMT

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!

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

PGStats — Mon, 06 Oct 2014 01:04:21 GMT

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;

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

jh_ti_bw — Mon, 06 Oct 2014 11:12:18 GMT

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.

Jan

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

Ksharp — Mon, 06 Oct 2014 11:55:19 GMT

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

Check Mike Zdeb and me .

Xia Keshan