Find Columns with Missing Values in a list of tables

[jmmedina252]

Hello,

I have a group of tables where I need to identify columns that do not have any values/ all missing values.  If I could do this in some iterative fashion like a macro that would be ideal as well.  Below is example of input and ideal output:

Input:

work. table_1

ID  Name  Date  Result   

1    tim         .           1

2   bob         .          .

3  louis        .          5

work. table_2

ID  Name  Date                Result   

1    tim        1/1/2024        .

2   bob        .                        .

3  louis      1/4/2024       .

work. table_3

ID  Name  Date                Result   

1                  1/1/2024        1

2                  .                       .

3                 1/4/2024       5

Output:

Table        Missing_values_column

table_1      Date

table_2      Result

table_3      Name

[ballardw]

Proc Freq NLEVELS is the easiest tool to find if any variables have any missing values:

ods select nlevels;
proc freq data=mydataset nlevels;
ODS OUTPUT NLEVELS=mydatasetnelels;
run;

This will tell you some additional information but if the the Missing has any count shown you have some.

[quickbluefish · Accepted solution]

%macro miss(dslist=);

    %if %sysfunc(exist(_all_miss)) %then %do;
        proc sql; drop table _all_miss; quit;
    %end;

	%let dslist=%lowcase(%cmpres(&dslist));
	%let nds=%sysfunc(countW(&dslist, ' '));
	
	%do dsn=1 %to &nds;
	
		%let ds=%scan(&dslist,&dsn,' ');
		
		proc contents data=&ds noprint out=vars; run;
		
		proc sql noprint;
		select count(*) into :nn trimmed from vars where type=1; 
		select count(*) into :nc trimmed from vars where type=2; 
		quit;
		
		data _miss;
		set &ds end=last;
		array _n {*} _numeric_;
		array _c {*} _character_;
		array _nT {&nn} _temporary_;
		array _cT {&nc} _temporary_;
		retain _recnum 0;
		_recnum+1;
		do i=1 to dim(_n);
			_nT[i]+missing(_n[i]);
		end;
		do i=1 to dim(_c);
			_cT[i]+missing(_c[i]);
		end;
		if last then do;
			length dsname var $32;
			dsname="&ds";
			do i=1 to dim(_nT);
				if _nT[i]=_recnum then do;
					var=vname(_n[i]);
					output;
				end;
			end;
			do i=1 to dim(_cT);
				if _cT[i]=_recnum then do;
					var=vname(_c[i]);
					output;
				end;
			end;
		end;
		keep dsname var;
		run;
		
		proc append data=_miss base=_all_miss; run;
		
		proc sql;
		drop table _miss;
		quit;
		
	%end;
	
	proc print data=_all_miss; run;
	
%mend; *miss();

data test1;
x=3; y='a'; z=.; output;
x=3; y='a'; z=.; output;
run;

data test2;
x=3; y=''; z=.; output;
x=3; y=''; z=.; output;
run;
* sample call ;
%miss(dslist=test1 test2);

[Ksharp]

I wrote a paper related to this question.

https://support.sas.com/resources/papers/proceedings20/4737-2020.pdf

I proposed three ways to solve this.

And I suggest to use PROC IML way due to fast and no need to use MACRO ,if your dataset was not very big.

The following is the PROC SQL way.

data have;
set sashelp.class;
call missing(age,name);
run;
data heart;
set sashelp.heart;
call missing(weight,height);
run;
data class;
set sashelp.class;
run;

%let library=work; /*Here is the library I want to search*/
%macro find_missing_var(dsn);
proc transpose data=&dsn(obs=0) out=vname;
var _all_;
run;
proc sql noprint;
select catx(' ','n(',_name_,') as',_name_) into :vnames separated by ','
from vname;
create table temp as
select &vnames from &dsn;
quit;
proc transpose data=temp out=temp1;
var _all_;
run;
data temp2;
length table_name _name_ $ 100;
table_name="&dsn";
set temp1;
if col1=0;
run;
proc append base=want data=temp2 force;
run;
%mend;
proc delete data=want;
run;
data _null_;
set sashelp.vtable(
 keep=libname memname
 where=(libname="%upcase(&library)")
);
call execute(cats('%nrstr(%find_missing_var(',libname,'.',memname,'))'));
run;
title 'COLUMNS WITHOUT DATA';
proc print;
run;

[New_2_SAS]

Thank you so much for writing the paper, really great and helpful insight!