| Title: Delete variables that have only missing values. |

| Goal: To identify and remove any variables, character or numeric, that have only |

| missing values. |

\*************************************************************************/

/* The sample data set to work with. */

data work.ds1;

input a s d z $ x $ c $;

cards;

1 . 3 qwe . yui

. . . . . hjk

3 . 3 zxc . .

run;

proc print;

run;

/* The Library and data set to be modified. */

%let lib=WORK;

%let mem=DS1;

/* Use SQL to create a list of character variables and a list of numeric variables from */

/* the data set. The lists are out into macro variables. The count of each type is */

/* also captured. */

proc sql noprint;

select name, put(count(name),5.-L) into :clist separated by ' ' , :charct

from dictionary.columns

where libname=upcase("&lib") and memname=upcase("&mem") and type='char';

select name, put(count(name),5.-L) into :nlist separated by ' ', :numct

from dictionary.columns

where libname=upcase("&lib") and memname=upcase("&mem") and type='num';

quit;

/* In a DATA _NULL_ create an array for the character variables and an array for the */

/* numeric variables. Create two more arrays, one for character and one for numeric, */

/* where the variables will serve as flags. The values are initially set to 'false' */

/* to indicate that they have only missing values. Any time a non-missing values is */

/* found for a variable, the cooresponding flag variable is set to 'true'. */

data _null_;

array char(*) $ &clist;

array num(*) &nlist;

array c_allmiss (&charct) $ (&charct*'true');

array n_allmiss (&numct) $ (&numct*'true');

set ds1 end=done;

do i=1 to dim(c_allmiss);

if char(i) ne ' ' then c_allmiss(i)='false';

end;

do i=1 to dim(n_allmiss);

if num(i) ne . then n_allmiss(i)='false';

end;

/* Once the entire data set has been processed, loop through the flag arrays and create */

/* a macro variable for any variable that still has a flag set to 'false'. Keep count */

/* of how many there are and put that number into a macro variable as well. */

if done then do;

cnt=0;

do i= 1 to dim(c_allmiss);

if c_allmiss(i) ='true' then do;

cnt+1;

call symput('var'||put(cnt,3.-l),vname(char(i)));

end;

end;

do i=1 to dim(n_allmiss);

if n_allmiss(i)='true' then do;

cnt+1;

call symput('var'||put(cnt,3.-l),vname(num(i)));

end;

end;

call symput('cnt',put(cnt,3.-l));

end;

run;

/* This macro generates the list of variables to be dropped for the DROP statement. */

%macro dropem;

%do i = 1 %to &cnt;

&&var&i.

%end;

%mend;

/* Finally, this DATA Step creates a new data set similarly named to the original and */

/* issues the DROP statement. If you approve of the results, rename the new data set. */

data &lib..&mem._;

set &lib..&mem.;

drop %dropem ;

run;