`g_valcnt(G;S;X)`

Returns the number of valid (non-N/A) values within a given group.

Function type

Vector only

Syntax

g_valcnt(G;S;X)

Input

Argument Type Description

Argument	Type	Description
`G`	any	A space- or comma-separated list of column names Rows are in the same group if their values for all of the columns listed in `G` are the same. If `G` is omitted, all rows are considered to be in the same group. If any of the columns listed in `G` contain N/A, the N/A value is considered a valid grouping value.
`S`	integer	The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation If `S` is omitted, all rows will be considered by the function (subject to any prior row selections). If any of the values in `S` are neither 1 nor 0, an error is returned.
`X`	any simple type	A column name

G

any

A space- or comma-separated list of column names

Rows are in the same group if their values for all of the columns listed in G are the same.

If G is omitted, all rows are considered to be in the same group.

If any of the columns listed in G contain N/A, the N/A value is considered a valid grouping value.

S

integer

The name of a column in which every row evaluates to a 1 or 0, which determines whether or not that row is selected to be included in the calculation

If S is omitted, all rows will be considered by the function (subject to any prior row selections).

If any of the values in S are neither 1 nor 0, an error is returned.

X any simple type A column name

Return Value

For every row in each group defined by G (and for those rows where S=1, if specified), g_valcnt returns the integer value corresponding to the number of valid (non N/A) values in the column listed in X that are in the same group as that row.

If all values for a particular group are N/A, the result is 0.

Sample Usage

<base table="pub.doc.samples.ref.func.g_func_sample_usage"/>
<willbe name="g_valcnt_1" value="g_valcnt(state;include;value)"/>
<willbe name="g_valcnt_2" value="g_valcnt(state city;include;value)"/>

Text N/A Values

For g_functions, there is effectively no text N/A value since the empty string ('') is considered a valid value. Because of this, g_valcnt(G;S;X) and g_nacnt(G;S;X) may not return the expected results when dealing with text values. Therefore, you should test explicitly for empty strings when using this function.

Consider the following table:

The last four columns in the table were created using:


<willbe name="valcnt" value="g_valcnt(department;;item_description)"/>
<willbe name="nacnt" value="g_nacnt(department;;item_description)"/>
<willbe name="isvalid" value="department_description<>''"/>
<willbe name="valcnt_correct" value="g_cnt(department;isvalid)"/>

You can see that in the Department Description (department_description) column, there is only one valid value in department 22 (SNACKS). However, the g_valcnt(department;;item_description) function in the valcnt column returns 4, and the g_nacnt(department;;item_description) function in the nacnt column returns 0. To find the number of valid values, you can create a computed column isvalid with the value department_description<>''. You can then create a computed column valcnt_correct with the value g_cnt(department;isvalid) to determine the correct number of valid values for each department. In the case of department 22, valcnt_correct displays the correct number of valid values (1).

Example

Let's say we want to know, out of all the entries in the Monthly Statewide Non-Seasonally Adjusted Unemployment Statistics table (pub.fin.fred2.bls.smsu), how many valid values are in the Unemployed column for every state.

<base table="pub.fin.fred2.bls.smsu"/>

Create a computed column using the g_valcnt(G;S;X) function. Specify state as the group (G), since we're grouping by state; specify unemp as X since we want the valid number of values in that column; and leave the selection parameter (S) blank, since we want to take into account all of the entries in the table (not just a subset of those entries):

<willbe name="validentries" value="g_valcnt(state;;unemp)" label="# of Valid`Unemployment`Values"/>

Since we didn't specify a selection column, the validentries column will show the number of valid unemployment entries for every state. For example, Arkansas (AK) has 455 valid entries, but Indiana (IA) only has 395. You can see there are missing values in the unemp column for Indiana from 01/08 through 12/12.

Now let's see if we can find out how many how many valid values over 150,000 in the Unemployed column for every state. We essentially want to find out the same information, but for a subset of our data. For this, we can use the selection column parameter of the g_valcnt(G;S;X) function.

Create a computed column that evaluates to true if the Unemployed value is greater than 150,000:

<willbe name="over_150" value="unemp>150000" label="Over`150,000"/>

Now, use the over_150 computed column as the selection column in the g_valcnt(G;S;X) function:

<willbe name="validentries_over_150" value="g_valcnt(state;over_150;unemp)" label="# of Valid`Unemployed`Over 150,000"/>

The validentries_over_150 column shows the total number of non N/A values over 150,000 in the Unemployed column for every state.