topic Re: How to make PROC SQL count missing values? in New SAS User

How to make PROC SQL count missing values?

cosmid — Wed, 12 May 2021 19:26:25 GMT

Here's the scenario:

data sample;
  input x;
datalines;
1
2
.
4
run;

proc sql;
  select count(*) from sample; /*returns 4*/
  select count(x) from sample; /*returns 3*/
quit;

Is there a way to count that missing value for x?

Is there a built in variable in proc sql that represents the total number of observations?

Re: How to make PROC SQL count missing values?

ballardw — Wed, 12 May 2021 19:27:53 GMT

Use the NMISS function

proc sql;
  select nmiss(x) from sample; /*returns 1*/
quit;

Re: How to make PROC SQL count missing values?

Tom — Wed, 12 May 2021 19:36:14 GMT

Either just use subtraction:

proc sql;
  select count(*) as N
       , count(x) as NX
       , count(*)-count(x) as MISSX
  from sample
;
quit;

or SUM() a boolean expression. SAS evaluates boolean expressions to 1 or 0.

proc sql;
  select count(*) as N
       , count(x) as NX
       , sum(missing(x)) as MISSX
  from sample
;
quit;

Re: How to make PROC SQL count missing values?

cosmid — Wed, 12 May 2021 19:40:59 GMT

Wow! Thanks for the quick reply. I"m guessing there isn't a way to count a var that include its missing values? I thought there's something like a count_miss(x) that will return a 4:) Thanks for your help!

Re: How to make PROC SQL count missing values?

cosmid — Wed, 12 May 2021 19:43:44 GMT

Re: How to make PROC SQL count missing values?

Steve_SAS — Thu, 01 Jun 2023 16:26:34 GMT

Very elegant answer.

So, it appears and confirmed after testing, that count(var) does not count missing values of var.

Re: How to make PROC SQL count missing values?

Steve_SAS — Thu, 01 Jun 2023 16:27:46 GMT

nmiss(x) is the elegant answer.