ACCESS Procedure Data Conversions

The following table shows the default formats that SAS assigns to each CA-Datacom/DB data type. The default formats also become the default informats. len is the value of the LENGTH attribute of the CA-Datacom/DB field. dec is the value of the DECIMALS attribute of the CA-Datacom/DB field.

Default SAS Column Formats for CA-Datacom/DB Data Types
Field Type	Field Description	Default SAS Format
C	character	$len.
B	binary:
	for length ≤ 8, unsigned	(2xlen+1).dec
	for length 8, signed	(2xlen+2).dec
	for length > 8	$HEX(2xlen).
	for length = 4, semantic-type= SQL-DATE	DATE9.
	for length = 3, semantic-type= SQL-TIME	TIME8.
	for length = 10, semantic-type= SQL-STMP	DATETIME30.6
D	packed decimal:
	for length ≤16, unsigned	(2xlen+1).dec
	for length 16, signed	(2xlen+2).dec
	for length > 16	$HEX(2xlen).
E	extended floating-point	$HEX(2xlen).
G	graphics data	$HEX(2xlen).
H	hexadecimal character	$len.
K	kanji (same as Y)	$HEX(2xlen).
L	long floating-point	E24.
N	numeric (zoned decimal):
	for length 16, unsigned	len.dec
	for length 16, signed	(len+1).dec
	for length > 16	$HEX(2xlen).
S	short floating-point	E14.
T	PL/I bit representation	$HEX(2xlen).
Y	double-byte character set (DBCS)	$HEX(2xlen).
Z	mixed DBCS and single byte	$HEX(2xlen).
2	halfword binary (aligned), unsigned	5.dec
2	halfword binary (aligned), signed	6.dec
4	fullword binary (aligned), unsigned	9.dec
4	fullword binary (aligned), signed	10.dec
8	doubleword binary (aligned), unsigned	17.0

Note that CA-Datacom/DB numeric fields are copied into SAS character columns with a $HEX. format if they are too long to fit in a SAS numeric column. For example, a CA-Datacom/DB field of type B with a length of 30 is copied into a SAS column with $HEX60. format. A field of type B with a length of 5 and dec of 2 is copied into a SAS column with 11.2 format. An error message appears if any precision is lost.

The maximum SAS format width is 200, so SAS uses 200 for CA-Datacom/DB fields whose length exceeds 200.

You might want to change the default format whenever it does not seem appropriate for the values stored in the table. For example, a packed decimal field of length 4 and 2 decimal places would have a default SAS format of 7.2. A very large negative number with a decimal (such as -99,999.99) might not fit.

If SAS software discovers the output format is too small, it issues the following warning message to the error log:

AT LEAST ONE W.D FORMAT WAS TOO
                     SMALL FOR THE NUMBER TO BE PRINTED. THE DECIMAL POINT MIGHT BE SHIFTED
                     BY THE BEST FORMAT

. The message can occur, such as when you invoke the PRINT procedure. If this message appears, you should specify a larger width.

The format determines how values in the SAS column are displayed. It does not affect how those values are stored. Their storage is determined by their CA-Datacom/DB type and length. Therefore, you cannot replace a character format with a numeric format or conversely.

If numeric values in the table are a lot smaller than their length implies, space on the output print line can be conserved by specifying smaller w. or w.d formats.

Each key is converted to one SAS character column, even if the key is numeric or has more than one component field. The length of a key becomes its default format width. You cannot change the format for a key.

If you assign a date format to a numeric field, make sure that you also specify the SAS date format in the DB Content field to indicate you are storing dates in your table.

For binary data types, if the SEMANTIC-TYPE attribute is not set to SQL-DATE, SQL-TIME, nor SQL-STMP, the data is treated as normal binary data. To store a SAS date for a normal binary field, use the ACCESS procedure CONTENT statement to assign a date format.