Every column in a table has a name and a data type. The data type tells Amazon Redshift how much physical storage to set aside for the column and the form in which the data is stored.
For more information about Amazon Redshift data types and to determine which data types are available for your version of Amazon Redshift, see your Amazon Redshift documentation.
Amazon Redshift uses a special value called NULL to identify an absence of information for a column. When SAS/ACCESS reads an Amazon Redshift NULL value, it interprets it as a SAS missing value.
You can specify a column in an Amazon Redshift table so that it requires data. To do this in SQL, you specify a column as NOT NULL. This tells SQL to allow a row to be added to a table only if a value exists for the column. For example, when you assign NOT NULL to the CUSTOMER column in the SASDEMO.CUSTOMER table, you cannot add a row unless there is a value for CUSTOMER.
For more information about how SAS handles NULL values, see Potential Result Set Differences When Processing Null Data.
To control how SAS missing character values are handled, use the NULLCHAR= and NULLCHARVAL= data set options.
This table shows the default formats that SAS/ACCESS Interface to Amazon Redshift assigns to SAS variables when using the LIBNAME statement to read from an Amazon Redshift table. These default formats are based on Amazon Redshift column attributes. SAS/ACCESS does not support Amazon Redshift data types that do not appear in this table.
|
Amazon Redshift Data Type |
ODBC Data Type |
Default SAS Format |
|---|---|---|
|
CHAR(n)1 |
SQL_CHAR |
$w. |
|
VARCHAR(n)1 |
SQL_LONGCHAR(n) |
|
|
DECIMAL |
SQL_DECIMAL |
w or w.d or none if you do not specify w and d2 |
|
INTEGER |
SQL_INTEGER |
11. |
|
SMALLINT |
SQL_SMALLINT |
6. |
|
SQL_TINYINT |
4. |
|
|
REAL |
SQL_REAL |
none |
|
FLOAT |
SQL_FLOAT |
|
|
DOUBLE PRECISION |
SQL_DOUBLE |
|
|
BIGINT |
SQL_BIGINT |
20. |
|
BOOLEAN |
SQL_BOOLEAN |
1. |
|
DATE |
SQL_TYPE_DATE |
DATE9. |
|
TIMESTAMP |
SQL_TYPE_TIMESTAMP |
DATETIME formats |
| 1 n in Amazon Redshift character data types is equivalent to w in SAS formats. | ||
| 2 m and n in Amazon Redshift numeric data types are equivalent to w and d in SAS formats. | ||
The following table shows the default data types that SAS/ACCESS Interface to Amazon Redshift uses when creating tables. The Amazon Redshift engine lets you specify nondefault data types by using the DBTYPE= data set option.
|
Default SAS Format |
ODBC Data Type |
Amazon Redshift Data Type |
|---|---|---|
|
w.d |
SQL_DOUBLE or SQL_NUMERIC using m,n if the DBMS allows it |
NUMERIC(m,n)2 |
|
$w. |
SQL_VARCHAR using n |
VARCHAR(n)1 |
|
DATETIME formats |
SQL_TIMESTAMP |
TIMESTAMP3 |
|
DATE formats |
SQL_DATE |
DATE |
|
TIME formats |
SQL_TIMESTAMP |
TIMESTAMP4 |
| 1 n in Amazon Redshift character data types is equivalent to w in SAS formats. | ||
| 2 m and n in Amazon Redshift numeric data types are equivalent to w and d in SAS formats. | ||
| 3 Although the Amazon Redshift engine supports TIMESTAMP, it has no TIMESTAMP WITH TIMEZONE data type that maps to the corresponding Amazon Redshift data type. | ||
| 4 A SAS time value is converted to a TIMESTAMP value of 1960-01-01 <time> in Amazon Redshift. | ||