topic Re: Dynamically Profile for Nulls in SAS Procedures

Dynamically Profile for Nulls

DavidPhillips2 — Thu, 15 Nov 2018 19:53:20 GMT

Is there a simple way to create a list of variables that contain nulls? I found a way to do it if I manually refer to the column by name.

E.g.

select count(*)
from my table
where employee_category is null
group by employee_category
having count(*) > 0) as employee_category_nulls

Returns number of nulls in column.

Even better is to only return a list of columns that contains nulls and the number of nulls.

Re: Dynamically Profile for Nulls

Shmuel — Thu, 15 Nov 2018 20:32:36 GMT

I believe you have to check each variable on all observations.

That can be done in one step using two arrays, as in next code (not tested):

data _NULL_;
  set have end=eof;
   array vx {*} _numeric_;    /* if need create separate array for _charcter_ */
   array nx  $  n1-n100;      /* up to 100 variables. adapt to need */ 
   length list  $1000;        /* adapt length to contain full list of var names */     

   /* fulfill variable names */
   do i=1 to dim(vx);
        nx(i) = vname(vx(i));
   end;

   /* check each numeric var in an observation */
   do i=1 to dim(vx);
        if vx(i) = .  and findw(list,nx(i)) = 0
        then catx(' ',trim(list), nx(i));
  end;
           
  if eof then put  list=;
run;

Re: Dynamically Profile for Nulls

DavidPhillips2 — Thu, 15 Nov 2018 20:35:36 GMT

Currently debugging this to see if it works.

Re: Dynamically Profile for Nulls

DavidPhillips2 — Thu, 15 Nov 2018 20:52:07 GMT

If there was a way to do the below and only include instances where the column is character and has missing:

PROC FORMAT;
	value $missfmt ' '='Missing' other='Not Missing';
	value  missfmt  . ='Missing' other='Not Missing';
RUN;

PROC FREQ DATA=CCW.INTGRD_INPA;
FORMAT _CHAR_ $missfmt.;
TABLES _CHAR_ / missing missprint nocum nopercent;
FORMAT _NUMERIC_ missfmt.;
TABLES _NUMERIC_ / missing missprint nocum nopercent;	
;
RUN;

Re: Dynamically Profile for Nulls

Shmuel — Thu, 15 Nov 2018 21:01:09 GMT

Here is the code adapted to check CHAR variables: (the formats not needed)

data _NULL_;
  set have end=eof;
   array vx $ _charcter_;
   array nx $  n1-n100;      /* up to 100 variables. adapt to need */ 
   length list  $1000;        /* adapt length to contain full list of var names */     

   /* fulfill variable names */
   do i=1 to dim(vx);
        nx(i) = vname(vx(i));
   end;

   /* check each numeric var in an observation */
   do i=1 to dim(vx);
        if missing(vx(i))  and findw(list,nx(i)) = 0
        then catx(' ',trim(list), nx(i));
  end;
           
  if eof then put  list=;
run;

Re: Dynamically Profile for Nulls

DavidPhillips2 — Thu, 15 Nov 2018 21:05:19 GMT

When I run this:

then catx(' ',trim(list), nx(i));
_
22
76
ERROR: Undeclared array referenced: catx.

Re: Dynamically Profile for Nulls

Shmuel — Thu, 15 Nov 2018 21:43:42 GMT

Sorry, line should be:

list = catx(' ',trim(list), nx(i));

Re: Dynamically Profile for Nulls

DavidPhillips2 — Thu, 15 Nov 2018 21:47:40 GMT

The output is list=_charcte

Re: Dynamically Profile for Nulls

Shmuel — Thu, 15 Nov 2018 22:09:06 GMT

Change array definitions (tested):

array vx {*} $ _character_;
array nx {*} $  n1-n100;

Re: Dynamically Profile for Nulls

Shmuel — Thu, 15 Nov 2018 22:10:44 GMT

add also:
length n1-n100 $10; /* adapt to max var name length */

Re: Dynamically Profile for Nulls

Ksharp — Fri, 16 Nov 2018 13:58:07 GMT

ods select none;
ods output nlevels=want;
proc freq data=sashelp.heart nlevels;
table _all_;
run;
ods select all;
proc print data=want(where=(NMissLevels ne 0));run;

Re: Dynamically Profile for Nulls

DavidPhillips2 — Tue, 20 Nov 2018 18:09:00 GMT

Ksharp, thank you, your code is extremely helpful for profiling. I found that adding table _CHARACTER_ is useful as there is not as much purpose to mass checking for null numbers.