Desktop productivity for business analysts and programmers

SAS sql not counting null values

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

SAS sql not counting null values

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?


Accepted Solutions
Solution
‎05-31-2014 02:22 AM
Super User
Posts: 9,878

Re: SAS sql not counting null values

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

View solution in original post


All Replies
Super Contributor
Posts: 275

Re: SAS sql not counting null values

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

Trusted Advisor
Posts: 1,228

Re: SAS sql not counting null values

This will include null value also

proc sql;

select col1,count(*) from have

group by col1;

quit;

Super Contributor
Posts: 275

Re: SAS sql not counting null values

Here count function count row number of dataset.

Solution
‎05-31-2014 02:22 AM
Super User
Posts: 9,878

Re: SAS sql not counting null values

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 2185 views
  • 0 likes
  • 4 in conversation