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).
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
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;
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.