topic Dynamically Select Columns with Non-Null values After a Grouping in SAS Programming

Dynamically Select Columns with Non-Null values After a Grouping

DavidPhillips2 — Fri, 28 Sep 2018 20:27:07 GMT

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

Re: Dynamically Select Columns with Non-Null values After a Grouping

DavidPhillips2 — Fri, 28 Sep 2018 20:37:26 GMT

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;

Re: Dynamically Select Columns with Non-Null values After a Grouping

Tom — Fri, 28 Sep 2018 21:07:09 GMT

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

Re: Dynamically Select Columns with Non-Null values After a Grouping

DavidPhillips2 — Fri, 28 Sep 2018 21:10:23 GMT

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

Re: Dynamically Select Columns with Non-Null values After a Grouping

DavidPhillips2 — Mon, 01 Oct 2018 18:28:27 GMT

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;

Re: Dynamically Select Columns with Non-Null values After a Grouping

Tom — Mon, 01 Oct 2018 20:30:55 GMT

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.