SAS-L provides an excellent source for such challenges. It is accessible either as comp.soft-sys.sas in Google Groups or at
http://www.listserv.uga.edu/archives/sas-l.html. These loosely mirror each other.
A quick search in SAS-L archives or Google Group comp.soft-sys.sas for "empty columns" will return a thread from earlier this year.
From discussions on SAS-L the most common method uses SQL to acquire the list of entirely-missing columns.
The following code creates syntax to create a table with like-named columns and just one row, holding the count of rows where that column is non-missing. When the count is zero, the column is entirely missing.
SQL does not support the missing() function because it already has
{column} is null .
I have created a test table in work.demo in which some character columns are entirely missing.
In the code that follows the first SQL statement creates the "counting syntax". The second SQL statement then uses that to create the table of summary counts [pre]proc sql noprint ;
select 'sum( not ' !! trim(name) !! ' is null ) as ' !! name
into :counters separated by ', '
from dictionary.columns
where libname='WORK' and memname= 'DEMO'
;
create table emptys as
select &counters
from demo
;
quit; [/pre]
A common reason for creating this information is to build a fresh table excluding the empty columns. The following code achieves that. [pre]proc transpose ; run;
proc sql noprint ;
select _name_ into :wanted separated by ', '
from _last_ where col1 gt 0
;
create table reduced as select &wanted from demo;
quit; [/pre]
Here proc transpose has taken the default options. It reads the latest dataset
work.emptys, which has just come out of proc sql. By Default it writes to the next dataset in the work.data{n} sequence. In the proc SQL that follows, the output from proc transpose is referred as _last_.
Although such extended use of defaults may be deprecated by some, I find it helpful in reducing code to the more-relevant issues.
Another $0.02
from
PeterC
