Data Analysis
Type : CREATION_INTERNE
Examples use generated data (datalines) for CAS tables or connect to an existing PostgreSQL database (with credentials and server/database names to be replaced by the user).
1 Code Block
CASLIB statement
Explanation :
This example shows the minimal syntax to establish a connection to a PostgreSQL database from CAS using the CASLIB statement. The 'srctype' parameter is set to 'postgres' and essential connection information such as the server, username, password, database, and schema are provided. The command 'proc casutil; list caslib=PostgreSQLcaslib;' allows verifying that the connection is established and viewing the files (tables) accessible via this caslib.
This example shows the minimal syntax to establish a connection to a PostgreSQL database from CAS using the CASLIB statement. The 'srctype' parameter is set to 'postgres' and essential connection information such as the server, username, password, database, and schema are provided. The command 'proc casutil; list caslib=PostgreSQLcaslib;' allows verifying that the connection is established and viewing the files (tables) accessible via this caslib.
Copied!
| 1 | /* Remplacez 'PGserver', 'user1', 'myPwd', 'PGdatabase' et 'myschema' par vos propres informations de connexion. */ |
| 2 | caslib PostgreSQLcaslib desc='PostgreSQL Caslib' |
| 3 | dataSource=(srctype='postgres', |
| 4 | server='PGserver', |
| 5 | username='user1', |
| 6 | password='myPwd', |
| 7 | database='PGdatabase', |
| 8 | schema='myschema'); |
| 9 | |
| 10 | /* Pour vérifier la connexion et lister les tables si la caslib est active */ |
| 11 | PROC CASUTIL; |
| 12 | list caslib=PostgreSQLcaslib; |
| 13 | QUIT; |
2 Code Block
PROC CASUTIL
Explanation :
This example uses the PROC CASUTIL procedure to load a specific table ('myPGdata') from a PostgreSQL database (accessible via 'PostgreSQLcaslib') into SAS Cloud Analytic Services. The resulting CAS table is named 'class_from_PostgreSQLcaslib' and stored in the 'casuser' caslib. It also illustrates how specific credentials can be substituted at the load operation level via the 'dataSourceOptions' option, thereby overriding those defined during caslib creation.
This example uses the PROC CASUTIL procedure to load a specific table ('myPGdata') from a PostgreSQL database (accessible via 'PostgreSQLcaslib') into SAS Cloud Analytic Services. The resulting CAS table is named 'class_from_PostgreSQLcaslib' and stored in the 'casuser' caslib. It also illustrates how specific credentials can be substituted at the load operation level via the 'dataSourceOptions' option, thereby overriding those defined during caslib creation.
Copied!
| 1 | /* Créez d'abord la caslib PostgreSQL si ce n'est pas déjà fait (voir Exemple 1). */ |
| 2 | /* Assurez-vous que la table 'myPGdata' existe dans votre base de données PostgreSQL. */ |
| 3 | |
| 4 | PROC CASUTIL; |
| 5 | /* Charger la table 'myPGdata' de la caslib 'PostgreSQLcaslib' dans une nouvelle table CAS 'class_from_PostgreSQLcaslib' dans la caslib 'casuser'. */ |
| 6 | /* Les identifiants spécifiés dans dataSourceOptions écraseront ceux définis dans la CASLIB. */ |
| 7 | load casdata="myPGdata" incaslib="PostgreSQLcaslib" outcaslib="casuser" |
| 8 | casout="class_from_PostgreSQLcaslib" replace |
| 9 | dataSourceOptions=(username='user5', password='myNewPwd'); |
| 10 | |
| 11 | /* Lister les fichiers dans la caslib 'casuser' pour vérifier que la table a été chargée. */ |
| 12 | list files incaslib="casuser"; |
| 13 | |
| 14 | /* Afficher les informations sur la structure de la table chargée. */ |
| 15 | contents casdata="class_from_PostgreSQLcaslib" incaslib="casuser"; |
| 16 | QUIT; |
3 Code Block
DATA STEP / PROC CASUTIL Data
Explanation :
This example demonstrates bulk loading a CAS table containing date and time values to PostgreSQL. A temporary CAS table ('timedata') is first created with specifically formatted time values ('time16.6'). Then, the PROC CASUTIL procedure is used with the 'bulkload=true' option for optimized transfer. The 'dbtype="d_timefmt='time'"' option is crucial to tell PostgreSQL how to interpret the time column, thus ensuring that values are correctly inserted without truncation or conversion errors.
This example demonstrates bulk loading a CAS table containing date and time values to PostgreSQL. A temporary CAS table ('timedata') is first created with specifically formatted time values ('time16.6'). Then, the PROC CASUTIL procedure is used with the 'bulkload=true' option for optimized transfer. The 'dbtype="d_timefmt='time'"' option is crucial to tell PostgreSQL how to interpret the time column, thus ensuring that values are correctly inserted without truncation or conversion errors.
Copied!
| 1 | /* Création d'une table CAS avec des données de date/heure */ |
| 2 | DATA casuser.timedata; |
| 3 | INPUT id d_timefmt : time16.6; |
| 4 | FORMAT d_timefmt time16.6; |
| 5 | DATALINES; |
| 6 | 1 10:30:00.000000 |
| 7 | 2 14:45:30.123456 |
| 8 | 3 08:00:00.000000 |
| 9 | ; |
| 10 | RUN; |
| 11 | |
| 12 | /* Création de la caslib PostgreSQL si ce n'est pas déjà fait (voir Exemple 1). */ |
| 13 | /* Assurez-vous d'avoir une caslib PostgreSQL configurée et active, par exemple 'PostgreSQLcaslib'. */ |
| 14 | |
| 15 | PROC CASUTIL; |
| 16 | /* Enregistrez la table CAS 'timedata' vers PostgreSQL en utilisant le chargement en masse. */ |
| 17 | /* Le paramètre dbtype assure le formatage correct de la colonne de temps. */ |
| 18 | save casdata="timedata" incaslib="casuser" |
| 19 | outcaslib="PostgreSQLcaslib" casout="PG_time_data" replace |
| 20 | options={bulkload=true, dbtype="d_timefmt='time'"}; |
| 21 | |
| 22 | /* Vérifiez la nouvelle table dans PostgreSQLcaslib. */ |
| 23 | list files incaslib="PostgreSQLcaslib"; |
| 24 | QUIT; |
4 Code Block
CASLIB statement
Explanation :
This example illustrates connecting to a PostgreSQL instance hosted on Microsoft Azure using Single Sign-on (SSO) via Microsoft Entra ID. It is imperative NOT to specify the 'username=' or 'password=' options directly in the CASLIB declaration, as this would disable SSO. The 'srctype' option is still 'postgres'. The server, database, and schema are provided, and authentication is managed by the SAS Viya platform's SSO configuration. Verification via 'proc casutil' confirms that the SSO connection is operational and resources are accessible.
This example illustrates connecting to a PostgreSQL instance hosted on Microsoft Azure using Single Sign-on (SSO) via Microsoft Entra ID. It is imperative NOT to specify the 'username=' or 'password=' options directly in the CASLIB declaration, as this would disable SSO. The 'srctype' option is still 'postgres'. The server, database, and schema are provided, and authentication is managed by the SAS Viya platform's SSO configuration. Verification via 'proc casutil' confirms that the SSO connection is operational and resources are accessible.
Copied!
| 1 | /* Remplacez 'mydataenvironment.postgres.database.azure.com', 'mypgdata' et 'myschema' par vos propres informations. */ |
| 2 | /* AUCUN 'username=' ou 'password=' n'est spécifié pour activer le SSO. */ |
| 3 | caslib pgres_azure datasource=(srctype='postgres', |
| 4 | server="mydataenvironment.postgres.database.azure.com", |
| 5 | database=mypgdata, |
| 6 | schema=myschema); |
| 7 | |
| 8 | /* Pour vérifier la connexion et lister les tables si la caslib est active */ |
| 9 | PROC CASUTIL; |
| 10 | list caslib=pgres_azure; |
| 11 | 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.
