BookmarkSubscribeRSS Feed
sasRus
Fluorite | Level 6

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

3 REPLIES 3
Reeza
Super User

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

Ksharp
Super User

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;
sasRus
Fluorite | Level 6
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.

sas-innovate-2024.png

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.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 882 views
  • 1 like
  • 3 in conversation