- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
%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);
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
%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);
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you so much for writing the paper, really great and helpful insight!