topic Find Columns with Missing Values in a list of tables in SAS Programming

Find Columns with Missing Values in a list of tables

jmmedina252 — Thu, 21 Nov 2024 22:10:05 GMT

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

Re: Find Columns with Missing Values in a list of tables

ballardw — Thu, 21 Nov 2024 22:28:03 GMT

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.

Re: Find Columns with Missing Values in a list of tables

quickbluefish — Thu, 21 Nov 2024 23:47:26 GMT

%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);

Re: Find Columns with Missing Values in a list of tables

Ksharp — Fri, 22 Nov 2024 01:08:44 GMT

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;

Re: Find Columns with Missing Values in a list of tables

New_2_SAS — Fri, 22 Nov 2024 21:12:12 GMT

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