Data Types for Yellowbrick

Overview of Data Types

Every column in a table has a name and a data type. The data type tells Yellowbrick how much physical storage to set aside for the column and the form in which the data is stored. This section includes information about Yellowbrick data types, null and default values, and data conversions.

For more information about Yellowbrick data types and to determine which data types are available for your version of Yellowbrick, see your Yellowbrick documentation.

Yellowbrick Null Values

Yellowbrick has a special value called NULL. A Yellowbrick NULL value signifies an absence of information and is analogous to a SAS missing value. When SAS/ACCESS reads a Yellowbrick NULL value, it interprets it as a SAS missing value.

You can define a column in a Yellowbrick table so that it requires data. To do this in SQL, you specify a column as NOT NULL. This tells SQL to allow a row to be added to a table only if a value exists for the field. For example, when NOT NULL is assigned to the CUSTOMER field in the SASDEMO.CUSTOMER table, a row cannot be added unless there is a value for CUSTOMER. When creating a table with SAS/ACCESS, you can use the DBNULL= data set option to indicate whether NULL is a valid value for specified columns.

You can also specify Yellowbrick columns as NOT NULL DEFAULT. For more information about using the NOT NULL DEFAULT value, see your Yellowbrick documentation.

When you know whether a Yellowbrick column enables NULLs or the host system provides a default value for a column that is specified as NOT NULL WITH DEFAULT, you can write selection criteria and enter values to update a table. Unless a column is specified as NOT NULL or NOT NULL DEFAULT, it allows NULL values.

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.

Working with Long Character Values in Yellowbrick

The default open-source driver automatically assigns a length of 255 characters to varying-length character variables, such as the VARCHAR(n) data type. Therefore, if you know that character data in a data set is longer than 255 characters, you should specify the MaxVarcharSize attribute for the CONOPTS= LIBNAME option when you connect to your Yellowbrick library. Specify a value for MaxVarcharSize that is as large as the longest column value to ensure that you retrieve all of the available data.

The following LIBNAME statement, sample tables, and SQL query would create a new data set that does not truncate the character data from the Testb data set.

libname x ybrick preserve_col_names=no 
  preserve_tab_names=no database=mydb1 dbmax_text=32767 
  server='myserver' port=5432 user=ybadmin password='ybpwd' 
  conopts='MaxVarcharSize=300;';
 
/* Create example tables Testa and Testb */
proc sql; 
create table testa(idnum int, myflag int, col1 varchar(20))
create table testb(idnum int, col1 varchar(300))

insert into testa values (1, 1, 'from a 1')
insert into testa values (2, 0, 'from a 2')

insert into testb values (1, 'from b 1*from b 1*from b 1*from b 1*from b 1*
   from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*
   from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*
   from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*from b 1*
   from b 1*274')
insert into testb values (2, 'from b 2*from b 2*from b 2*from b 2*from b 2*
   from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*
   from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*
   from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*
   from b 2*274')
quit;

/* Verify that data is not truncated */
proc sql;
create table work.SASTEST as 
   select case when myflag = 1 then a.col1 else b.col1 end as 
      col1 from x.testa a, x.testb b
      where a.idnum = b.idnum;

/* Select the end of col1 from position 200 to the end to show that all of */
/* the data is retrieved                                                   */
select substr(col1, 200) from work.SASTEST;
quit;

The output from the final SELECT statement appears as shown here. This statement selects a substring starting at column position 200 from the Col1 value.

rom b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*from b 2*274

You can see that the end of the value does include the ‘*274’ from the end of the VARCHAR value.

LIBNAME Statement Data Conversions

This table shows the default formats that SAS/ACCESS Interface to Yellowbrick assigns to SAS variables when using the LIBNAME statement to read from a Yellowbrick table. These default formats are based on Yellowbrick column attributes. SAS/ACCESS does not support Yellowbrick data types that do not appear in this table.

The following table shows the default data types that SAS/ACCESS Interface to Yellowbrick uses when creating tables. The Yellowbrick engine lets you specify nondefault data types by using the DBTYPE= data set option.

Items of Note for Yellowbrick Data

When you work with data from Yellowbrick, here are some items to keep in mind:

• Table indexes are not supported.
• Foreign keys are not supported.
• Primary and unique key constraints can be specified, but they are not enforced.
• Yellowbrick data storage is based on bytes rather than characters. For this reason, use the DBSERVER_MAX_BYTES= LIBNAME option and the DBCLIENT_MAX_BYTES= LIBNAME option to control data storage sizes.