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

Columns with no data -- check all datasets in library

sasRus — Mon, 12 Feb 2018 03:34:21 GMT

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).

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

Reeza — Mon, 12 Feb 2018 03:45:26 GMT

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

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

Ksharp — Mon, 12 Feb 2018 10:55:28 GMT

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;

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

sasRus — Mon, 12 Feb 2018 15:21:55 GMT

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.