[SAS advance] counting values and having clauses are implemented using the count function.

I. Count FunctionsIn actual statistical applications, the number of occurrences of observed values needs to be calculated. This function can be implemented through the count function. It has three forms and functions:

Form you used	Return Value	Example
Count(*)	Number of rows in the entire table or group	Select count (*) as Count
Count(Column)	When no missing value is set for the column selected in the parameter, the number of rows of the variable in the output table or group	Select count (jobcode) as Count
Count(DistinctColumn)	Number of rows in the distinct values Column	Select count (distinct jobcode) as Count

[Note ]:

1. Count summary function only count is not a missing value, and the missing value is ignored. If you need to calculate the missing value, consider the nmiss function;

2. Count summary function is the only summary function that allows users to use asterisks (*) as parameters;

3. If the group by clause is added after proc SQL, the number of rows of each group is output;

4. If the parameter column specified by Count contains missing values, Proc SQL considers the missing values as one type, which may lead to unexpected results.

 

Ii. Having clause

We have already demonstrated how to use the group by clause to group data. For example, in the following example, a table with 16 rows and two columns is output, that is, the average salary value in the group of each type of job code.

proc sql;    select jobcode,              avg(salary) as AvgSalary  format=dollar11.2        from sasuser.payrollmaster       group by jobcode;

[Suppose]

The user wants to select only a subset of groups for your query output, that is, to filter data in each group by one condition, this can be achieved by adding a having clause after gourp. In fact, having clauses have similar functions as where clauses, but where clauses can only be used for individual rows, rather than after group. For example:

proc sql;    select jobcode,              avg(salary) as AvgSalary format=dollar11.2     from sasuser.payrollmaster    group by jobcode    having avg(salary)>56000;

[Note] if the group by clause is omitted, and the query Code contains the having clause, the having clause and the summary function regard the entire table as a group, and the function is the same as the WHERE clause.