Select Page

DS2 topics

DS2_Options TYPEWARN

data type conversion

 

DS2 and Automatic 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.

 

Huh, there are more than two data types?!

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.

NameTypeDetails

CHAR(n) Character
(Fixed width)
One byte per character, n = maximum number of characters, uses the same number of bytes in every row of data.
NCHAR(n) Character
(Fixed width)
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.

Why DS2?

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).

  • DS2 can handle more data types, actually much more, well just about all ANSI SQL datatypes. So no more restrictions to just SAS 8-byte floating-point numeric or fixed-width character data types.
  • Syntax to code mutli-threaded processes,
  • Syntax to allow writing of multi-purpose reusable code. Actually DS2 looks a lot like typical programming languages with the init, run and term code blocks be implemented.

 

 

 

 

 

 

SAS Documentation Links

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)