data type conversion
data type conversion
As discussed in the previous post (Huh, there are more than two data types?!) SAS has now access to 17 different data types. But hang on, most modern databases have had all these data types for years, how have I been able to read my data from for example Oracle or DB2? Well the answer is quite simple, SAS has been converting ‘foreign’ data types into the two SAS native data types, numeric and character on the fly. This conversion is handled typically by the respective SAS/Access engine being used. Unfortunately since databases have data types capable of higher precision that the SAS numeric data type (well actually due to the limitation of the 8 byte floating point), this conversion process can result in a loss of precision (!!!)
Since this conversion is carried out by the SAS/Access engine and therefore with a SAS libname, the way to get round this is to simply not use the Access engine. And this is exactly what DS2 does, by using its own driver DS2 is able to take full advantage of the rich set of data types offered in the ANSI standard.
So for anyone using SAS for a while we made do with just two data types – a fixed width character and an 8-byte floating point numeric. In a dataset, variables were either numeric or character. Well in DS2 things have become a lot more like the real world – there are now 17 data types to choose from. See Data Type Conversion.
|One byte per character, n = maximum number of characters, uses the same number of bytes in every row of data.|
|Unicode character data, two to four bytes per character, n = maximum number of characters, uses the same number of bytes in every row of data.|
|VARCHAR(n)||Character (Variable width)||One byte per character, uses just enough bytes to store the actual value in each row of data up to a maximum of n characters.|
|NVARCHAR(n)||Character (Variable width)||Unicode character data, two to four bytes per character, uses just enough bytes to store the actual value in each row of data up to a maximum of ncharacters.|
|DOUBLE||Approximate fractional numeric||8-byte (64-bit) signed, approximate floating-point numeric with a maximum of 16 significant digits of precision on ANSI systems.|
|REAL||Approximate fractional numeric||4-byte (32-bit) signed, approximate floating-point numeric with a maximum of 9 significant digits of precision on ANSI systems.|
|FLOAT(p)||Approximate fractional numeric||Signed, approximate, floating-point numeric with user-defined precision (p). The precision determines whether the value will be stored as DOUBLE or REAL.|
|DECIMAL(p,s)||Exact fractional numeric||Signed, exact, fixed decimal point numeric value of user-defined precision and scale. Precision (p) determines the maximum number of significant digits, up to a maximum of 52. Scale (s) determines how many of the significant digits are reserved for the fractional portion of the value.
NUMERIC(p,s) is an alias for DECIMAL.
|BIGINT||Integer numeric||Signed, exact whole numbers up to 19 significant digits.|
|INTEGER||Integer numeric||Signed, exact whole numbers up to 10 significant digits.|
|SMALLINT||Integer numeric||Signed, exact whole numbers up to 5 significant digits.|
|TINYINT||Integer numeric||Single-byte signed, exact whole numbers between -128 and 127.|
|BINARY(n)||Binary||Fixed-length binary data, n = number of bytes allocated to store data in every row.|
|VARBINARY(n)||Binary||Variable-length binary data, uses just enough bytes to store the actual value in each row of data up to a maximum of n.|
|TIME||Time||Stores ANSI time values.|
|DATE||Date||Stores ANSI date values.|
|TIMESTAMP||Datetime||Stores ANSI timestamp (datetime) values.|
I am currently learning the in’s and out’s of DS2, one of the languages currently available in the SAS system. So here’s what I have learned so far.
Why is DS2 needed?
First of all, I am assuming that DS2 is an abbreviation of Data Step 2, sounds logical right? So the tried and trusted datastep super-charged.
With each new release the SAS system continues to develop (well that’s one less worry) with many if not most SAS procedures providing multi-threading processing. This may simple be simply using the available multiple CPU’s on a computer or using MPP systems such as Hadoop or Teradata. In contrast to SAS procedures, the core of the SAS system, the Datastep has remained single threaded. DS2 however is about to change that, or at least it now gives developers another toolset to be able to really speed up their processes.
Here are some of the highlights I have discovered (well not actually discovered since I am still just ready the user manual).
This post provides a list of links to some of the more interesting SAS documentation titles. Who doesn’t love reading documentation?!
SAS® Intelligence Platform: Overview PDF(9.4)
SAS® Intelligence Platform: Installation and Configuration Guide PDF(9.4)
SAS® Guide to Software Updates PDF(9.4)
SAS® Intelligence Platform: System Administration Guide PDF(9.4)
SAS® Guide to BI Row-Level Permissions PDF(9.4)
SAS® Guide to Metadata-Bound Libraries PDF(9.4)
SAS® Intelligence Platform: Application Server Administration Guide PDF(9.4)
SAS® Intelligence Platform: Desktop Application Administration Guide PDF(9.4)
SAS® Intelligence Platform: Data Administration Guide PDF(9.4)
SAS® Intelligence Platform: Middle-Tier Administration Guide PDF(9.4)
SAS® Intelligence Platform: Web Application Administration Guide PDF(9.4)
SAS® Environment Manager: User’s Guide PDF(9.4)
SAS® Management Console: Guide to Users and Permissions PDF(9.4)
Scheduling in SAS® PDF(9.4)
SAS® Logging Configuration and Programming Reference PDF(9.4)
Administering SAS® Enterprise Guide PDF(4.3)
Refer to SAS Intelligence Platform: Desktop Application Administration Guide
SAS® Language Interfaces to Metadata PDF(9.4)
SAS® Open Metadata Interface: Reference and Usage PDF(9.4)
SAS® Metadata Model: Reference PDF(9.4)
SAS® Java Metadata Interface (JavaDoc) HTML(9.4)
SAS® 9.4 DS2 Language Reference PDF (9.4)
SAS® 9.4 Functions and CALL Routines PDF (9.4)
SAS(R) 9.4 FedSQL Language Reference, Third Edition PDF (9.4)