SAS sql not counting null values

[eagles_dare13]

I have:

proc sql;

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

quit;

If TBL contains:

col1  col2

A    

A     XX

Then my output says:

col1  cnt

A     1

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

[slchen]

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

[stat_sas]

This will include null value also

proc sql;

select col1,count(*) from have

group by col1;

quit;

[slchen]

Here count function count row number of dataset.

[Ksharp · Accepted solution]

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