You can use the TRANS NID function to mask United States
Social Security Numbers (SSN).
An SSN consists of 3 subfields with the following format: AAAGGSSSS.
- AAA
- Area number. The area is generally determined by the state in
which the SSN is issued.
- GG
- Group number. A group number is assigned based on the area number.
- SSSS
- Serial number.
The TRANS NID function generates a masked SSN according
to the following rules:
- A group number that is appropriate for the area number is generated.
The group number will be the most recent group used by the Social
Security Administration for the area.
- Serial numbers begin with 0001 and are incremented by 1 for each
additional SSN generated for the area number. When the serial number
exceeds 9999, the serial number will be reset to 0001 and the group
number preceding the number most recently issued for the area number
will be used.
- When MTD=mask, the output value will include an area number corresponding
to the same state as the source area number.
Data types
The following data types are
permitted for source and destination columns. Restrictions for each
data type are noted.
- CHAR and NCHAR
- The length of the column must be at least 9 characters.
- DECIMAL
- The precision of the column must be between 9 and 20 and the scale
must be 0.
- INTEGER
- No restrictions.
- VARCHAR and NVARCHAR
- The length of the column must be at least 9 characters.
Validation (VAL=Y)
If the VAL=Y parameter
is used, a source row will be skipped if any of the following apply:
- The source area number exceeds the maximum value.
- The source area number has not been used by the Social Security
Administration.
- The source group number has not been used for the source area
number.
Special processing
The following checks
will be made during processing:
- If the source value is 0 (INTEGER or DECIMAL data type), spaces,
or either a zero-length VARCHAR or NVARCHAR, and
- the destination column is an INTEGER or DECIMAL data type, the
output value will be 0.
- the destination column is a CHAR or NCHAR data type, the output
value will be spaces.
- the destination column is a VARCHAR or NVARCHAR data type, the
destination length will be 0.
- If a destination column length is 11 characters or more and separators
were specified for the output value, the separators will be included.
- If the source value is NULL, the output value will be NULL.
Skipped rows
A source row will be skipped
and not written to the destination table if the following occurs:
- Null value
- The source value is NULL but the destination column does not allow
a NULL value.
- Invalid SSN
- The source column is a CHAR, NCHAR, VARCHAR, or NVARCHAR, but
the source value is more or less than 9 characters (not including
separators).
- The source value includes a non-numeric character.
- The source value is 0 or a reserved value such as 078-05-1120
and 457-55-5462.