SQL 计数值不丢失的地方
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14446976/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count where the value is not missing
提问by Beanie
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.
我尝试了简单的计数功能，但它带来了所有记录。我想过使用“case”，但我不确定语法。我正在使用 SAS 企业指南。
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.
结果：我只想有 4 个作为答案，所以它不计算空值。
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.
我已经添加了我正在使用的代码。它给了我日期的计数，但还包括日期值为空的地方。
回答by BellevueBob
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:
给定一个名为 CARS 的 SAS 数据集，其中包含您的示例中的变量，您可以在 SQL 中以两种不同的方式找到具有 service_date 非缺失值的观察数：
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 whereclause. Both perform identically.
第一个示例指定计数函数中的 service_date 变量，它将忽略缺失值。第二个在应用where子句后计算所有行。两者表现相同。
回答by Jacco
回答by Zdravko Danev
by default count(service_date) excludes null values, as opposite to count(*) which counts all rows.
默认情况下，count(service_date) 排除空值，与计数所有行的 count(*) 相反。
回答by Alexandru Calin
You could try, WHERE column != ""or if you don't have NULL maybe you could use WHERE CHAR_LENGTH(column) > 3
你可以试试，WHERE column != ""或者如果你没有 NULL 也许你可以使用WHERE CHAR_LENGTH(column) > 3
回答by Nick Herro
Lamak,
拉马克，
There are a few ways to go about this. Here is the method I would use, avoiding PROC SQL.
有几种方法可以解决这个问题。这是我将使用的方法，避免 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.
根据您将如何使用该计数，您可以在最后一个数据步骤的末尾添加一个“调用 symputx()”函数，以在其他地方将其作为宏变量调用。
The result is 4.
结果是 4。
回答by Jay Corbett
If it can be from another Proc (Proc Means)
如果它可以来自另一个 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;
