Desktop productivity for business analysts and programmers

SAS query not counting null records

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 82
Accepted Solution

SAS query not counting null records

In this sas query:

select col1, count(distinct col2) as cnt from table1 group by col1;

If there are two values of col2 for the same col1, the cnt =2 which is correct. But if one of those 2 values is null, the cnt = 1. i.e it does not consider null as a distinct value. How to rewrite the query so null is considered a distinct value?


Accepted Solutions
Solution
‎05-12-2014 11:26 AM
Grand Advisor
Posts: 10,211

Re: SAS query not counting null records

Missing isn't a distinct value per se. Add a count(missing(col2)) as NullCount or similar (untested)

or count(distinct col2) + count(missing(col2)) as cnt.

View solution in original post


All Replies
Solution
‎05-12-2014 11:26 AM
Grand Advisor
Posts: 10,211

Re: SAS query not counting null records

Missing isn't a distinct value per se. Add a count(missing(col2)) as NullCount or similar (untested)

or count(distinct col2) + count(missing(col2)) as cnt.

☑ This topic is SOLVED.

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

Discussion stats
  • 1 reply
  • 378 views
  • 0 likes
  • 2 in conversation