`g_nacnt(G;S;X)`

Returns the number of N/A values within a given group.

Function type

Vector only

Syntax

g_nacnt(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_nacnt returns the integer value corresponding to the number of N/A values in the column listed in X that are in the same group as that row.

If no 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_nacnt_1" value="g_nacnt(state;include;value)"/>
<willbe name="g_nacnt_2" value="g_nacnt(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 N/A values are in the Unemployed column for every state.

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

Create a computed column using the g_nacnt(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="naentries" value="g_nacnt(state;;unemp)" label="# of N/A`Unemployment`Values"/>

Since we didn't specify a selection column, the naentries column will show the number of N/A unemployment entries for every state. For example, Arkansas (AK) has 0 N/A entries, but Indiana (IA) has 60. 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 for every state how many of the months that had missing unemployment values also reported an unemployment rate over 6% for that month. 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_nacnt(G;S;X) function.

Create a computed column that evaluates to true if the Unemployment Rate value is greater than 6%:

<willbe name="over_six" value="unemp_rate>6.0" label="Over 6%"/>

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

<willbe name="naentries_over_six" value="g_nacnt(state;over_six;unemp)" label="# of N/A`Unemployed`Over 6%"/>

The naentries_over_six column now shows for each state the total number of months which had missing unemployment values and also reported an unemployment rate of over 6% for those months. So, in our example, Indiana had 18 months where they reported an unemployment rate of over 6% but for which they didn't report the actual unemployment values for those months.