DATA Step, Macro, Functions and more

Columns with no data -- check all datasets in library

Reply
Occasional Contributor
Posts: 6

Columns with no data -- check all datasets in library

I have a library of about 50 SAS datasets. I would like to print the dataset name, and column(s) that are blank (no data). The format of these dataset columns can be of mixed type. There is a solution to a similar problem here: https://communities.sas.com/t5/SAS-Enterprise-Guide/Columns-with-No-Data/td-p/283014 but it is only for one dataset.

 

My solution is to wrap a macro around the solution in the link, so that proc freq is ran on every single dataset and checks for empty columns. Could there be a more efficient way? I looked into PROC SQL (dictionary.columns) but it doesn't have a flag column for empty columns (other than length = 1, however variables like Gender can be length 1). 

Super User
Posts: 23,293

Re: Columns with no data -- check all datasets in library

This gets asked once a week. In my experience, PROC FREQ is the most versatile and fast solution with the MISSING format. 

 

Here's a slightly more elaborate version, but I like the output.

https://gist.github.com/statgeek/2de1faf1644dc8160fe721056202f111

Super User
Posts: 10,689

Re: Columns with no data -- check all datasets in library

[ Edited ]

The more efficient way is using IML code. Would you like to use IML code ?

 


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


proc iml;
dsn=datasets(work);
do i=1 to nrow(dsn);
  use (dsn[i]);
  read all var _char_ into char[c=vname_char];
  read all var _num_  into num[c=vname_num];
  close;
  n_char=countn(char,'col');
  n_num=countn(num,'col'); 
  if ^ isempty(loc((t(n_char)//t(n_num))=0)) then do;
    idx=loc((t(n_char)//t(n_num))=0);
    name=(t(vname_char)//t(vname_num))[idx];
    table=repeat(dsn[i],nrow(name));
    
    want_table=want_table//table;
    want_name=want_name//name;
  end;
end;
create want var{want_table want_name};
append;
close;
quit;

proc print noobs;run;
Occasional Contributor
Posts: 6

Re: Columns with no data -- check all datasets in library

hi Ksharp, thank you for your solution. Though I nor other users have proc IML installed. I think I will stick with the proc freq macro so that other users can run the program without having to install proc IML.
Ask a Question
Discussion stats
  • 3 replies
  • 148 views
  • 1 like
  • 3 in conversation