BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
jmmedina252
Fluorite | Level 6

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
quickbluefish
Quartz | Level 8
%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);

View solution in original post

4 REPLIES 4
ballardw
Super User

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
Quartz | Level 8
%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
Super User

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;

Ksharp_0-1732237714306.png

 

 

 

New_2_SAS
Fluorite | Level 6

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

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 333 views
  • 3 likes
  • 5 in conversation