Character to VARCHAR type conversion when loading into CAS

The script begins by initializing a CAS environment by defining a CASWORK LIBNAME and configuring user options to direct one-level tables to this CASLIB. It then uses `PROC CAS` to dynamically manage CASLIBs, deleting if necessary and adding a new 'sas^©7bdat' CASLIB pointing to a data path. Test data with character variables of various lengths are created via a DATA step. The central part of the script is the use of `PROC CASUTIL` with the `importoptions=(filetype="basesas" varcharconversion=16)` option to load the data table into CAS, converting character variables to VARCHAR and truncating them to a maximum length of 16 bytes. Finally, `PROC CAS` is used to display detailed column information of the resulting CAS table, allowing verification of the VARCHAR conversion.

Data Analysis

Type : CREATION_INTERNE

The source data ('table_with_char') is generated directly within the script via a SAS DATA step for demonstration purposes.

1 Code Block

Configuration

Explanation :
This block configures a `CASWORK` LIBNAME to establish a connection to the Cloud Analytic Services (CAS) engine via the 'casuser' CASLIB. The `options USER = CASWORK;` statement sets the default CASLIB for one-level tables. Finally, `%put &_sessref_;` displays the active CAS session identifier.

Copied!

1	LIBNAME CASWORK cas caslib=casuser;
2	options USER = CASWORK;
3
4	%put &_sessref_;

2 Code Block

PROC CAS

Explanation :
This block uses `PROC CAS` to interact directly with the CAS controller. It silently drops the 'sas7bdat' and 'sashdat' CASLIBs if they exist, to ensure a clean environment. Then, it adds a new CASLIB named 'sas7bdat' which points to a file system path (`&datapath`). The `caslib _all_ assign;` statement is used to bind all CAS librefs and default CASLIBs to the SAS client session.

Copied!

1	PROC CAS;
2	file log;
3	TABLE.dropCaslib /
4	caslib='sas7bdat' quiet = true;
5	RUN;
6	TABLE.dropCaslib /
7	caslib='sashdat' quiet = true;
8	RUN;
9	addcaslib /
10	datasource={srctype="path"}
11	name="sas7bdat"
12	path="&datapath" ;
13	RUN;
14	QUIT;
15	/* Binds all CAS librefs and default CASLIBs to your SAS client */
16	caslib _all_ assign;

3 Code Block

DATA STEP Data

Explanation :
This DATA step creates a SAS table named `table_with_char` in the 'sas7bdat' CASLIB. It initializes three character variables (`a`, `b`, `c`) with declared lengths of 300, 15, and 16 respectively. Records are created, some containing data whose length exceeds the declared length to demonstrate the effect of VARCHAR conversion and subsequent truncation.

Copied!

1	DATA sas7bdat.table_with_char;
2	LENGTH a $ 300 b $ 15 c $ 16;
3	a='a300'; b='b15' ; c='c16' ; OUTPUT;
4	a='a300300'; b='b151515'; c='c161616'; OUTPUT;
5	c='c161616161616161';
6	b='b15151515151515';
7	a="a300qzwsxedcrfvtgbyhnujmiklopqazwsxedcrfvtgbyhnujmikolp12345678901234567890123456789012345678901234567890123456789012345678901234567890"; OUTPUT;
8	RUN;

4 Code Block

PROC CASUTIL

Explanation :
This block uses `PROC CASUTIL` to load the SAS table `table_with_char` (located in 'sas7bdat') into the CAS engine, naming it `table_with_varchar` in the 'casuser' CASLIB. The `importoptions=(filetype="basesas" varcharconversion=16)` option is crucial: it specifies that character variables should be converted to VARCHAR type in CAS and their maximum lengths will be set to 16 bytes, truncating longer values if necessary. The `replace` option ensures that the table is recreated if it already exists.

Copied!

1	PROC CASUTIL;
2	load casdata="table_with_char.sas7bdat" incaslib="sas7bdat" outcaslib="casuser"
3	casout="table_with_varchar" importoptions=(filetype="basesas" varcharconversion=16) replace;
4	RUN;

5 Code Block

PROC CAS

Explanation :
This final block uses `PROC CAS` to query the freshly created CAS table. `sessionProp.setSessOpt` sets the active CASLIB to 'casuser'. Then, the `table.columninfo` action is executed on `table_with_varchar` to display detailed metadata of all columns, allowing verification of data types (especially VARCHAR conversion) and effective lengths after conversion.

Copied!

1	PROC CAS;
2	sessionProp.setSessOpt /
3	caslib="casuser";
4	RUN;
5	TABLE.columninfo / TABLE="table_with_varchar";
6	QUIT;

This material is provided "as is" by We Are Cas. There are no warranties, expressed or implied, as to merchantability or fitness for a particular purpose regarding the materials or code contained herein. We Are Cas is not responsible for errors in this material as it now exists or will exist, nor does We Are Cas provide technical support for it.

Retour à la liste