Every column in a table has a name and a data type. The data type tells the Microsoft SQL Server how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about Microsoft SQL Server null and default values and data conversions.
Microsoft SQL Server has a special value called NULL. A Microsoft SQL Server NULL value means an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a Microsoft SQL Server NULL value, it interprets it as a SAS missing value.
Microsoft SQL Server columns can be specified as NOT NULL so that they require data—they cannot contain NULL values. When a column is specified as NOT NULL, the DBMS does not add a row to the table unless the row has a value for that column. When creating a DBMS table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.
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 all data types that SAS/ACCESS Interface to Microsoft SQL Server supports. This table also shows the default SAS format that corresponds to each data type.
|
Microsoft SQL Server Data Type |
Default SAS Format |
|---|---|
|
CHAR(n) |
$w. |
|
NCHAR(n) |
$w. |
|
VARCHAR(n) |
$w. |
|
NVARCHAR(n) |
$w. |
|
TEXT |
$1024. |
|
NTEXT |
$1024. |
|
UNIQUEIDENTIFIER |
$36. |
|
BINARY(n) |
$w.1 |
|
VARBINARY(n) |
$w.1 |
|
VARBINARY(max) |
$w.1 |
|
DECIMAL(p, s) |
w. or w.d or none if w and d are not specified |
|
NUMERIC(p, s) |
w. or w.d or none if w and d are not specified |
|
BIGINT |
20. |
|
INT |
11. |
|
SMALLINT |
6. |
|
TINYINT |
4. |
|
BIT |
1. |
|
FLOAT(24) |
none |
|
FLOAT(n) |
none |
|
FLOAT(53) |
none |
|
DATE |
DATE9. |
|
TIME(n) |
TIME8. SAS/ACCESS Interface to Microsoft SQL Server cannot support fractions of seconds for time values. |
|
DATETIME |
DATETIMEw.d where w and d depend on precision |
|
DATETIME2 |
DATETIMEw.d where w and d depend on precision |
|
SMALLDATETIME |
DATETIMEw.d where w and d depend on precision |
|
DATETIMEOFFSET |
DATETIMEw.d where w and d depend on precision |
|
MONEY |
21.4 |
|
SMALLMONEY |
12.4 |
| 1 Because the Microsoft SQL Server driver does the conversion, this field is displayed as if the $HEXw. format was applied. | |
This table shows the default data types that the Microsoft SQL Server interface uses when creating tables.
|
SAS Variable Format |
Default Microsoft SQL Server Data Type |
|---|---|
|
w.d |
DOUBLE(m.n) or NUMERIC(m.n)1 |
|
$w. |
VARCHAR(n)2 |
|
Datetime formats |
DATETIME23 |
|
Date formats |
DATE |
|
Time formats |
TIME(n) |
|
DOLLARw.d |
MONEY or SMALLMONEY |
|
$HEXw. |
VARBINARY(n) |
| 1 m and n in Microsoft SQL Server numeric data types are equivalent to w and d in SAS formats. | |
| 2 n in Microsoft SQL Server character data types is equivalent to w in SAS formats. | |
| 3 It is possible that Datetime formats are exported to DATETIME values for older versions of an underlying DBMS driver. However, most current drivers export this data to DATETIME2 values. | |
The Microsoft SQL Server interface allows non-default data types to be specified with the DBTYPE= data set option.
Whether SAS/ACCESS converts data in a DOLLARw.d format to MONEY or SMALLMONEY in Microsoft SQL Server depends on the length and precision that are used in the DOLLARw.d format. If the difference between the length and the precision is 6 or less, then the data values are converted to SMALLMONEY when the data is written to Microsoft SQL Server. If the difference is 7 or larger, then the data values are converted to the MONEY data type. For example, a column that is formatted as DOLLAR10.4 in SAS would be converted to SMALLMONEY in Microsoft SQL Server, because the difference between the length (10) and the precision (4) is 6.
|
SAS Format |
Data Type in Microsoft SQL Server |
|---|---|
|
DOLLAR6.2 DOLLAR6.1 DOLLAR6. |
SMALLMONEY SMALLMONEY SMALLMONEY |
|
DOLLAR7.2 DOLLAR7.1 DOLLAR7. |
SMALLMONEY SMALLMONEY MONEY |
|
DOLLAR9.3 DOLLAR9.2 DOLLAR9.1 DOLLAR9. |
SMALLMONEY MONEY MONEY MONEY |