Data Types SAS vs DBMS -saving space in DBMS
Data types can play an important role in saving space in DBMS table. Proper care has to be taken when DBMS table is created from SAS by using appropriate dataset option. This is explained through an example, with Teradata as DBMS.
When creating a Teradata table in SAS, important aspect to keep in mind is data type. Data type issues are not just related to Teradata but can occur with any relational databases. SAS has only two data types, num and char; which are mapped to float and char respectively in Teradata. In Teradata, various data types are available for both numeric and character data, which provides flexibility in terms of space utilization. For string variables, char and varchar data types and for numeric variables different data types like byteint, smallint, decimal, bigint etc are available in Teradata. Assigning appropriate data type is very important in context of space, especially with large datasets. In the below example, Teradata table is created from SAS with data types of char and float by default.
SAS Data Set option dbtype gives the flexibility of creating appropriate data types for Teradata tables. With help of dataset option dbtype in below example, numeric variable with smaller length is casted to byteint and char variable with varying length is casted to varchar (500). Byteint data type takes one byte of space as opposed to eight bytes of space taken by float. Saving seven bytes of space can have huge impact on large datasets. Unnecessary padding space is also avoided by casting to varchar instead of default char.
SAS App Migration, Modernisation, and Manifestation
6 年Great tip for creating database tables using data step - a pass through approach would certainly be more verbose in this case.