BookmarkSubscribeRSS Feed
EAIAAUA
Calcite | Level 5

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!

3 REPLIES 3
PGStats
Opal | Level 21

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
jh_ti_bw
Obsidian | Level 7

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
Super User

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

Check Mike Zdeb and me .

Xia Keshan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 3 replies
  • 3648 views
  • 0 likes
  • 4 in conversation