SAS Data Types in CAS

Difficulty Level

Beginner

Published on : 06/12/2024

Expert Advice

Michael
Responsable de l'infrastructure Viya.

Use proc cas; table.columnInfo; quit; frequently during your development phase. It is the most reliable way to verify how CAS has interpreted your LENGTH statements, especially when joining tables from different data sources (like SQL Server or Oracle) where type mapping can var

Data types in SAS^© Cloud Analytic Services define the nature of the information stored in each column of a table, directly influencing the amount of memory allocated and how values are processed.

CHAR(n): For fixed-length character strings, where `n` is the maximum number of characters. Shorter values are padded with spaces. This type does not support ANSI SQL NULL values.
VARCHAR(n): For variable-length character strings. Values are not padded with spaces. Ideal for data where length varies. `VARCHAR(*)` can be used when the maximum length is not known, but this can lead to conversion to `CHAR(32767)` when copying to libraries that do not support `VARCHAR`.
DOUBLE: Represents a double-precision (64-bit) floating-point number allowing a large magnitude and high precision, and supports missing values.

The following examples illustrate the use of these data types in the SAS^© Viya^™ 4 environment with CASL interactions.

Data Analysis

Type : CREATION_INTERNE

Examples use generated data (datalines) or SASHELP, ensuring their autonomy and direct execution.

1 Code Block

DATA STEP Data

Explanation :
This simple example creates a CAS table named `fixed_char` with a `name` column of type `CHAR(10)`. Assigned values are less than or equal to 10 characters in length and will be right-padded with spaces if necessary. `proc print` is used to display the table.

Copied!

1	/* Création d'une table CAS avec une colonne CHAR de longueur fixe */
2	cas libref=casuser;
3
4	DATA casuser.fixed_char;
5	LENGTH name char(10);
6	name = 'Alice';
7	OUTPUT;
8	name = 'Bob';
9	OUTPUT;
10	RUN;
11
12	/* Affichage du contenu de la table pour vérifier */
13	PROC PRINT DATA=casuser.fixed_char;
14	RUN;
15
16	/* Nettoyage */
17	PROC CAS;
18	TABLE.dropTable RESULT=r / name='fixed_char' caslib='casuser';
19	PRINT r;
20	RUN;
21	QUIT;

2 Code Block

DATA STEP Data

Explanation :
This example uses the `VARCHAR(50)` type for the `description` column. It is observed that values are stored with their actual length without padding, even if they are shorter than the defined maximum length. `proc columnInfo` is used to inspect column attributes, including their lengths.

Copied!

1	/* Création d'une table CAS avec une colonne VARCHAR de longueur explicite */
2	cas libref=casuser;
3
4	DATA casuser.varying_char_example;
5	LENGTH description varchar(50);
6	description = 'Ceci est une description courte.';
7	OUTPUT;
8	description = 'Ceci est une description beaucoup plus longue qui utilise plus de caractères.';
9	OUTPUT;
10	description = 'Court';
11	OUTPUT;
12	RUN;
13
14	/* Affichage du contenu et des informations sur les colonnes pour vérifier les longueurs réelles */
15	PROC PRINT DATA=casuser.varying_char_example;
16	RUN;
17
18	PROC CAS;
19	TABLE.columnInfo RESULT=c / TABLE='varying_char_example' caslib='casuser';
20	PRINT c;
21	RUN;
22	QUIT;
23
24	/* Nettoyage */
25	PROC CAS;
26	TABLE.dropTable RESULT=r / name='varying_char_example' caslib='casuser';
27	PRINT r;
28	RUN;
29	QUIT;

3 Code Block

DATA STEP Data

Explanation :
This example illustrates the use of the `DOUBLE` type to store numbers with high precision, including very small and very large numbers, as well as missing values. `proc columnInfo` confirms the column type, and `table.fetch` is used to retrieve and display the data, demonstrating the handling of missing values and floating-point precision.

Copied!

1	/* Création d'une table CAS avec une colonne DOUBLE incluant des valeurs manquantes et des nombres à haute précision */
2	cas libref=casuser;
3
4	DATA casuser.double_advanced;
5	INPUT num_val;
6	DATALINES;
7	100.123456789
8	.
9	1.23456789123456789E-10
10	-5.6789123456789E+12
11	;
12	RUN;
13
14	/* Affichage des informations sur les colonnes pour confirmer le type DOUBLE */
15	PROC CAS;
16	TABLE.columnInfo RESULT=c / TABLE='double_advanced' caslib='casuser';
17	PRINT c;
18	RUN;
19
20	/* Récupération et affichage des données pour montrer la précision et les manquants */
21	PROC CAS;
22	TABLE.fetch RESULT=f / TABLE='double_advanced' caslib='casuser';
23	PRINT f;
24	RUN;
25	QUIT;
26
27	/* Nettoyage */
28	PROC CAS;
29	TABLE.dropTable RESULT=r / name='double_advanced' caslib='casuser';
30	PRINT r;
31	RUN;
32	QUIT;

4 Code Block

DATA STEP / PROC CONTENTS Data

Explanation :
This advanced example shows how `VARCHAR(*)` and `CHAR(n)` types behave when copying a CAS table to a standard SAS library (non-CAS). It is observed that `VARCHAR(*)` can be converted to a `CHAR(32767)` column in a non-CAS context, while `CHAR(n)` will retain its fixed length, truncating data if it is too long. `PROC CONTENTS` is used to check column lengths after copying.

Copied!

1	/* Initialise une session CAS */
2	cas libref=casuser;
3
4	/* Crée une table CAS avec une colonne VARCHAR() /
5	DATA casuser.varchar_star_orig;
6	LENGTH comments varchar(*);
7	comments = 'Un commentaire.'; OUTPUT;
8	comments = 'Un commentaire très long, dont la taille maximale n''est pas prédéfinie, pour observer l''impact lors de la copie.'; OUTPUT;
9	RUN;
10
11	/* Crée une table CAS avec une colonne CHAR(10) */
12	DATA casuser.char_fixed_orig;
13	LENGTH short_text char(10);
14	short_text = 'Hello'; OUTPUT;
15	short_text = 'WorldLonger'; /* Sera tronqué */
16	OUTPUT;
17	RUN;
18
19	/* Copie les tables CAS vers une bibliothèque de travail SAS standard */
20	/* Remarque: La bibliothèque WORK est un chemin local non-CAS. */
21	/* Pour une exécution hors de SAS Studio/Viya, ajuster le LIBNAME si nécessaire */
22	LIBNAME work clear;
23
24	DATA work.varchar_star_copy;
25	SET casuser.varchar_star_orig;
26	RUN;
27
28	DATA work.char_fixed_copy;
29	SET casuser.char_fixed_orig;
30	RUN;
31
32	/* Vérifie les attributs des colonnes dans la table copiée */
33	PROC CONTENTS DATA=work.varchar_star_copy varnum; RUN;
34	PROC CONTENTS DATA=work.char_fixed_copy varnum; RUN;
35
36	/* Affiche le contenu de la table CHAR pour voir la troncature */
37	PROC PRINT DATA=work.char_fixed_copy;
38	RUN;
39
40	/* Nettoyage des tables CAS */
41	PROC CAS;
42	TABLE.dropTable RESULT=r / name='varchar_star_orig' caslib='casuser';
43	PRINT r;
44	TABLE.dropTable RESULT=r / name='char_fixed_orig' caslib='casuser';
45	PRINT r;
46	RUN;
47	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.

Difficulty Level

Published on : 06/12/2024

Expert Advice

Data Analysis

1 Code Block

2 Code Block

3 Code Block

4 Code Block

Related Documentation