5.4.6 - Bin Code - Teradata Warehouse Miner

In-Database Analytic Functions User Guide

prodname
Teradata Warehouse Miner
vrm_release
5.4.6
created_date
November 2018
category
User Guide
featnum
B035-2306-118K

Purpose

Bin Coding replaces a continuous numeric column with a categorical one and produce ordinal values (for example, numeric categorical values where order is meaningful). Bin Coding uses the same techniques used in Histogram analysis, allowing you to choose between equal-width bins, equal-width bins with a user-specified minimum and maximum range, bins with a user-specified width, evenly distributed bins, or bins with user-specified boundaries.

If the minimum and maximum are specified, all values less than the minimum are put in to bin 0, while all values greater than the maximum are put into bin N+1. The same is true when the boundary option is specified.

Bin Coding supports numeric and date type columns. If date values are entered, the keyword DATE must precede the date value, and do not enclosed in single quotes.

There are two transformation parameters required for a Bin Code transformation:
  • binstyle
  • columns

Syntax

call twm .td_analyze('vartran','database=database name;tablename=table name;General Parameters;bincode{binstyle (bins,nbr of bins);columns (column name)};');

Required Parameters

bincode
Identifies the type of transformation being performed.
binstyle
The binstyle parameter has five parameters. The style and one or more numeric or date values are placed in parentheses after the keyword binstyle. If this parameter is not specified, 10 equal-width bins are created by default.
Example binstyle styles:
Syntax Example
binstyle (bins,nbr of bins) binstyle (bins,10)
binstyle (binswithboundaries,nbr of bins,lower bound,upper bound) binstyle (binswithboundaries,5,0,100)

This example specifies 5 equal-width bins between the boundaries 0 and 100.

All values less than the minimum are put in to bin 0, while all values greater than the maximum are put into bin N+1.

binstyle (boundaries,b1,b2,…) binstyle (boundaries, 0, 25, 50, 100)

This example specifies bins between 0 and 25, 25 and 50, and 50 and 100.

All values less than the first boundary value are put in to bin 0, while all values greater than the last boundary value are put into bin N+1.

binstyle (quantiles,nbr of quantiles) binstyle (quantiles,10)

This example specifies 10 approximately evenly distributed bins, with roughly the same number of observations allocated to each bin.

binstyle (width,bin width) binstyle (width,10)

This example automatically calculates the minimum and maximum values of the column, then beginning with the observations with the minimum value, bins are assigned using the requested width of 10.

call twm .td_analyze('vartran','database=twm_source;tablename=twm_customer_analysis;bincode={binstyle (bins,5),columns (cust_id/cid1,age/age1,income/inc1)}{binstyle (binswithboundaries,5,0,100),columns (cust_id/cid2,age/age2,income/inc2)}{binstyle (boundaries,0.0,100.0,200.00),columns (cust_id/cid3,age/age3,income/inc3)}{binstyle (width,25),columns (cust_id/cid4,age/age4,income/inc4)}{binstyle (quantiles,5),columns (cust_id/cid5,age/age5,income/inc5)};');
columns
Controls the name of the output (transformed) column and its data type. The columns parameter is required by all transformations except Derive. A separate transformation is performed for each column in the list. If a column name is followed by a forward slash and a name, the name after the slash becomes the name of the transformed column in the resultant output table. Otherwise the column name is used as the output column name.
For the Derive transformation, the outputname parameter controls the naming of the transformed output column.
database
The database containing the input table.
tablename
The input table to build a predictive model from.
vartran
Required to run a variable transformation. Enclose the 'vartran' parameter in single quotes.

Optional Parameters

datatype
For all transformation types, the datatype casts the column to a desired database data type provided it is compatible with the transformed data.
Allowed output types include:
  • byteint
  • char
  • date
  • decimal
  • float
  • integer
  • smallint
  • time
  • timestamp
  • varchar
  • bigint
  • number
fallback
When true, requests a mirrored copy of the output table in the Teradata Database when outputstyle=table.
gensqlonly
When true, the SQL for the requested transformations is returned as a result set but not run. When not specified or set to false, the SQL is run but not returned.
indexcolumns
When true, requests the output table contain the index columns when outputstyle=table.
indexunique
When true, requests the output table contain a unique primary index when outputstyle=table.
keycolumns
When null replacement is requested, either through a Null Replacement transformation or in combination with a Bin Code, Derive, Design Code, Recode, Rescale, Sigmoid, or Z Score transformation, the keycolumns parameter must be specified. The column or columns listed must form a unique key into the input and output table of the transformation.
lockingclause

Requests the generated SQL contain the given locking clause in the appropriate location depending on the output style.

An example of a locking clause when the output style defaults to select is:

LOCKING mydb.mytable FOR ACCESS;

multiset
When true, requests an output table that can contain duplicate rows when outputstyle=table.
noindex
When true, requests the output table contain no index columns when outputstyle=table.
nullstyle
Data types supported by various nullstyle parameters are:
Data Type Description Example
literal,value numeric, character, and date nullstyle (literal,value)
mean numeric and date nullstyle (mean)
median numeric and date nullstyle (median)
medianwithoutaveraging any supported data type nullstyle (medianwithoutaveraging)
mode any supported date type nullstyle (mode)
imputed,table any supported data type nullstyle (imputed,tablename)

If date values are entered, the keyword DATE must precede the date value, and do not enclose in single quotes.

outputdatabase
The database containing the resulting output table when outputstyle=table or view.
outputstyle
Allowed output styles are:
  • select
  • table
  • view
If outputstyle is not specified, the function generates a SELECT statement and does not create a table or view.
outputstyle={select|table|view}
outputtablename
The name of the output table when outputstyle=table or view.
overwrite

When overwrite is set to true (default), the output tables are dropped before creating new ones.

whereclause
Requests the generated SQL containing the given WHERE clause in appropriate places in the generated SQL. This is independent of the output style requested.

Examples

These examples show how to use binstyle. To run the provided examples, the td_analyze function must be installed in a database called twm and the TWM tutorial data must be installed in the twm_source database.

This example shows the binswithboundaries and boundaries styles applied to a column of type DATE. Notice the required literal DATE and the required lack of single quotes around the date value (for example, DATE 1995-07-01).

call twm.td_analyze('vartran','database=twm_source;tablename=twm_credit_acct;bincode={binstyle(binswithboundaries,5,DATE 1995-01-01,DATE 1995-12-31),columns(acct_start_date)}{binstyle(boundaries,DATe 1995-01-01,DATE 1995-07-01,DATE 1995-12-31),columns(acct_start_date/start2)};');

The example demonstrates combined null replacement. keycolumns must be included as a general parameter when null value replacement is performed.

call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;keycolumns=cust_id;bincode=binstyle(bins,5),nullstyle(literal,0),columns(income);');