10-05-2014 07:18 PM
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!!
10-05-2014 09:04 PM
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;
proc sql noprint;
select _var_ into :vList separated by " "
from ot where _nobs_=0;
10-06-2014 07:12 AM
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:
retain v1 . v2 " ";
%macro keep_not_null(data= , out=);
%local varlist nvar keeplist droplist thisvar thisnr i dsid rc libpart mempart;
%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)));
into :varlist separated by " "
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.;
%if %length(&keeplist.) > 0 %then %do;
%end; /* %if %length(&keeplist.) > 0 */
%if %length(&droplist.) > 0 %then %do;
%end; /* %if %length(&droplist.) > 0 */
%end; /* %if &dsid. > 0 */
%put %STR(ERROR: Can%'t open the dataset "&data.");
%keep_not_null( data=test(drop=name) ,out=test_out);
This macro proof for every variable, whether it contains only missing values or not.