Dynamically Select Columns with Non-Null values After a Grouping

[DavidPhillips2]

Is there a way to query a list of columns in a table that have a value other than null?  I’m working with metadata to include a list of columns that have data in them when grouped by a particular column.  E.g. group by school and only include in the output columns that have a non-null value in the grouping.

Example Output:

College:    Variable

School_A   Gender

School_A   SAT

School_A   County

School_B   Gender

[DavidPhillips2]

Here is a partial code example using SASHelp.cars.  I'm having a hard time completing the commented out pseudo code:

/*query columns in sashelp.cars*/
data carsMetadata (keep=name); set sashelp.vcolumn;
where libname = 'SASHELP' and type = 'char' and memname = 'CARS';



run;

/*query columns in cars that have non-null values when grouped by Make*/
/*this part is what I am trying to work with*/
proc sql;
create table carsgrouping as 
select * from sashelp.cars
group by make

/* something on the lines of having distinct( count(*)) > 1;
/*join with CarsMetadata*/

quit;

[Tom · Accepted solution]

I just have to guess at your meaning, but perhaps the NLEVELS output of PROC FREQ is what you want?  With a BY statement.

proc sort data=sashelp.class out=class ;
 by sex;
run;
data HAVE ;
  set class;
  if sex='M' then call missing(height);
  else call missing(weight);
run;
ods output nlevels=nlevels;
proc freq data=HAVE nlevels ;
  by sex ;
  tables _all_ / noprint;
run;

data want ;
  if 0 then set have (keep=sex);
  length tablevar $32 nlevels nmisslevels nnonmisslevels 8;
  format nlevels nmisslevels nnonmisslevels comma12.;
  set nlevels;
run;

proc print data=WANT;
  where nnonmisslevels;
run;

Obs    Sex    tablevar         nlevels     nmisslevels    nnonmisslevels

  1     F      Name                  9               0                9
  2     F      Sex                   1               0                1
  3     F      Age                   5               0                5
  4     F      Height                9               0                9
  6     M      Name                 10               0               10
  7     M      Sex                   1               0                1
  8     M      Age                   6               0                6
 10     M      Weight               10               0               10

[DavidPhillips2]

Tom, this is exactly what I was looking for.   Thank you for your reply to my post.

[DavidPhillips2]

I'm running into an exception scenario when no columns in the table contain null values that column nnonmisslevels is not created.  How can I check if this column exists or create the column in all cases.  Currently i have a where clause of:

where nnonmisslevels > 0;

[Tom]

That is why I had an extra data step with a LENGTH statement to make sure that those "optional" variables were actually created.

Note that if you are using a data step then you could use an IF statement instead of a WHERE statement. The IF statement will test the data in the currently being generated data vector whereas the WHERE statement needs to find the variable in the dataset that is being read in.