topic Re: SAS sql not counting null values in SAS Enterprise Guide

SAS sql not counting null values

eagles_dare13 — Sat, 31 May 2014 02:13:55 GMT

I have:

proc sql;

select COL1, count(distinct COL2 ) as cnt from TBL group by COL1;

quit;

If TBL contains:

col1 col2

A XX

Then my output says:

col1 cnt

A 1

But the cnt should be 2. Why is it neglecting the null value?

Re: SAS sql not counting null values

slchen — Sat, 31 May 2014 02:32:05 GMT

Count function in proc sql only count nonmissing value of column.

Re: SAS sql not counting null values

stat_sas — Sat, 31 May 2014 02:35:56 GMT

This will include null value also

proc sql;

select col1,count(*) from have

group by col1;

quit;

Re: SAS sql not counting null values

slchen — Sat, 31 May 2014 05:04:05 GMT

Here count function count row number of dataset.

Re: SAS sql not counting null values

Ksharp — Sat, 31 May 2014 06:22:37 GMT

set a value to missing value if you need it.

data TBL;
input col1 $  col2 $;
cards;
A    .
A     XX
;
run;

proc sql;
select COL1, count(distinct  coalescec(COL2,'......' )) as cnt from TBL group by COL1;
select COL1, (count(distinct COL2)+(sum(missing(col2)) ne 0)) as cnt from TBL group by COL1;

quit;

Xia Keshan

Message was edited by: xia keshan