How to find out if all the values of a variable (numeric or character) are missing?

[DrAbhijeetSafai]

@ballardw Thanks!

Your saying that "The OUT= option of the Tables data set only outputs the LAST result." is so much correct! Thanks for pointing it out. 

- Dr. Abhijeet Safai

[tom_grant]

If you are using SAS Studio, there is a Describe Missing Data task - the great thing about the Task is will show you patterns of "missingness"  (example: VAR1 & VAR2 are missing on X number of observations).

[DrAbhijeetSafai]

@tom_grant That is interesting! Thanks for information.

- Dr. Abhijeet Safai

[Mazi]

Hi @DrAbhijeetSafai ,

I know you've already received good responses for this. However, I decided to share my approach anyway which uses the max function in proc SQL.

proc datasets lib=work kill nolist nowarn; quit;

data test;
	set sashelp.class;
	call missing(age);
	if _n_ = 1 then call missing(sex);
	sex2='';
run;


proc contents data=test out=contents;
run;


data _null_;
	set contents end=eof;
	length COLS $32767;
	retain COLS '';
	if _N_ = 1 then do;
		call execute ('PROC SQL;');
		call execute ('CREATE TABLE CHECK AS SELECT');	
	end;
	COLS = catx(", ", COLS, catx(" ", cats('max(',name,')'), "as", name));
	if EOF then do;
		call execute (COLS);
		call execute ('FROM TEST;');
		call execute ('QUIT;');
	end;
run;

/*Drop columns if they are missing on all rows*/
data _null_;
	set check end=eof;
	array n [*] _NUMERIC_;
	array c [*] _CHARACTER_;
	length COLS $200;
	call execute ('proc sql;');
	call execute ('ALTER TABLE TEST');
	call execute ('DROP COLUMN');
	do i=1 to dim(n);
		if missing(n[i]) then COLS = catx(", ", COLS, vname(n[i]));
	end;
	do i=1 to dim(c);
		if missing(c[i]) then COLS = catx(", ", COLS, vname(c[i]));
	end;
	call execute(COLS);
	call execute(';quit;');
run;

[DrAbhijeetSafai · Accepted solution]

Many thanks to @ballardw , @Mazi , @data_null__ @yabwon @tom_grant , @PaigeMiller and all others for your response.

I was able to solve the issue but not easy way but by macro way. Somehow finally I found that the approach by macro will be better for me so I went ahead with that and the issue is solved. I would share the steps of what I did below.

1. First I used cmiss function (works for numeric too) to find out if particular value is missing in a variable.
2. Then by creating another variable I found accumulating total of that variable (value of cmiss is 1 if the value is missing and 0 if it it not missing). In this way now I have count of number of values that are missing in a variable.
3. I kept the last row of this dataset (by end= option) and kept only those variable which will have counts of missing values.
4. By proc contents I found out how many rows are there in the given dataset. 
5. I joined that dataset of proc contents with this dataset of one row.
6. If the dataset contains say 168 observations and if there 168 missing values for a variable, that means all the values are missing for that variable. (And that is what I was searching for: variables having all missing values).
7.  Once found, I created a flag for those variables for which number of missing values are number of records in the dataset. 
8. All this I put in the macro which will do it for all the variables one by one by using do while loop.

And in this way the issue was solved. I would have liked to share the code for this but I will not be able to do it because I will need to type out the code again and that is very time consuming. 

Many thanks to all those who responded. It was a fun exercise for me. I thought I will be able to avoid macro but later I found that macro is a better way to solve it. As I found this answer working for me, I will mark this as accepted answer. 

Thank you.

- Dr. Abhijeet Safai