CODEWITHSUNDEEP
sqlsas
Beanie
Beanie

Reputation: 41

Count where the value is not missing

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.

Upvotes: 3

Views: 41334

Answers (6)

Nick Herro
Nick Herro

Reputation: 23

Lamak,

There are a few ways to go about this. Here is the method I would use, avoiding PROC SQL.

/* Set up example data */    
data   YourTable;
input id car $ service_Date;
informat service_Date mmddyy10.;
format service_Date mmddyy10.;
cards;

1 honda 01-01-2005
2 honda 01-01-2006
3 honda ""
4 honda 01-01-2008
5 honda ""
6 honda 01-01-2010

run;

/* Count non null records */
data work.CountTable(keep=NbrNulls);
 set work.YourTable(where=(service_date ne .)) end=last;
 if last;
 NbrNulls=_N_;
run;

Depending on how you were going to use that count, you could add a "call symputx()" function at the end of the last data step to call it as a macro variable elsewhere.

The result is 4.

Upvotes: 0

Jay Corbett
Jay Corbett

Reputation: 28411

If it can be from another Proc (Proc Means)

data have;
 input car $ service_date mmddyy10.;
 format service_date mmddyy10.;
datalines;
honda 01/01/2005
honda 01/01/2006
honda
honda 01/01/2008
honda
honda 01/01/2010
toyota 01/01/2005
toyota 01/01/2006
toyota
toyota 01/01/2008
toyota
toyota 01/01/2010
;

Proc Means N data=work.have noprint; 
 var service_date;
 by car;
 output out=work.want(drop= _type_ _freq_)  n=count;
run;

Upvotes: 0

BellevueBob
BellevueBob

Reputation: 9618

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.

Upvotes: 7

Alexandru Calin
Alexandru Calin

Reputation: 218

You could try, WHERE column != "" or if you don't have NULL maybe you could use WHERE CHAR_LENGTH(column) > 3

Upvotes: 0

Jacco
Jacco

Reputation: 3272

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.

Upvotes: 4

Z .
Z .

Reputation: 12837

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

Upvotes: 3

Related Questions