Count where the value is not missing

Question

I want to count the occurrences of dates in a column where the value is not missing. So for example, I want to count the number of times a car has been serviced, rather then including the null values where there are no service dates.

I tried the simple count function but it brings all the records. I thought of using 'case' but I am not sure about the syntax. I am using the SAS Enterprise Guide.

sample date

id    car     service_Date
1     honda   01/01/2005
2     honda   01/01/2006
3     honda   
4     honda   01/01/2008
5     honda   
6     honda   01/01/2010

RESULT: I want to have only 4 as the answer so its not counting the null values.

PROC SQL;
CREATE TABLE WORK.QUERY_FOR_CARS AS 
SELECT 
t1.CAR,
t1.ID  
/* SERVICE_DATE */
(count (case when t1.S_DATE is not null then 0 end)) AS SERVICE_DATE
FROM WORK.QUERY_FOR_CAR_SERVICE
GROUP BY t1.S_DATE;
QUIT;

I have added the code that I am using. It gives me the count for the dates but also includes where the date value is null.

Solution 1

Given a SAS dataset named CARS containing the variables in your example, you can find the number of observations with non-missing values of service_date two different ways in SQL:

proc sql;

   select count(service_date) as num_not_missing
   from cars;

   select count(*) as num_not_missing
   from cars
   where service_date is not null;

quit;

The first example specifies the service_date variable in the count function, which will ignore missing values. The second counts all rows after applying the where clause. Both perform identically.

Solution 2

Two options for MS Sql Server:

SELECT COUNT([service_Date]) FROM [table1]

or

SELECT COUNT(*) FROM [table1] WHERE [service_Date] IS NOT NULL

For more on COUNT(), see MSDN.

Solution 3

by default count(service_date) excludes null values, as opposite to count(*) which counts all rows.

Comments

[Beanie]

@xQbert the service is is just a date.