SAS
This article will introduce how to get data from the SAS connection to Canner Enterprise.
Overview
In SAS, Canner Enterprise supports the following two functions. The first is SQL Pass-Through Facility, through which Canner Enterprise SQL can be used in PROC SQL syntax to obtain information. The second is SQL Functions, through this function, PROC SQL will use aggregate functions used by SAS such as MIN , MAX, COUNT, etc. are sent to Canner Enterprise for execution. For current Canner Enterprise support for SQL Functions, please refer to the support list at the bottom of the article.
Using SQL Pass-Through Facility
Here we demonstrate the functionality of using the SQL Pass-Through Facility.
Step 1: Configure connection settings with Canner Enterprise in SAS
First, we add a new Program File in SAS and fill in the parameters for connecting with Canner Enterprise.
Enter the following parameters for connection settings.
libname A1
postgres server='testing-aws-standalone.dev.apps.cannerflow.com'
port=7432
user=canner
password='Mzc3ZTg4MzgtOGM2Zi00NWRmLTgwMWQtMzYwNGMxMzBhNWFiX1Rlc3RfU0FTOlpmTkdjTWVRUXFwbUhXV3N6ZlduSFdnc0dWb0s1MlUx'
database=wire_protocol_test_84077;
libname
: build library pathpostgres server
: fill in the Host or IP address of Canner Enterpriseport
: fill in7432
user
: Fill in the cannerpassword
: Fill in the Personal Access Token (please refer to Personal Access Token for how to create it) document)database
: Fill in the SQL Name in Canner Workspace (e.g.wire_protocol_test_84077
). The SQL Name location is in the Workspace, click the Share button and switch to the PostgreSQL Wire Protocol page.
When the connection is completed, we can see the following connection success message in the log.
Step 2: Read data from Canner Enterprise
Use the SQL syntax of Canner Enterprise in PROC SQL to read the data of Table lineitem_56783
in this Workspace in Canner Enterprise.
proc sql noerrorstop;
connect using A1;
Select * From connection to A1 (Select * From lineitem_56783 Limit 10);
quit;
SAS provides CONNECT TO
and CONNECT USING
two syntaxes, here we use the second one as an example; for details on other syntax usages, please refer to [SAS product documentation](https://documentation.sas.com/doc/ en/pgmsascdc/9.4_3.5/acreldb/n0rn6hhsizv3trn1cl3e0ofosawi.htm).
Done: Test connection result
After a successful reading, the result of reading Canner Enterprise data will display on the SAS screen.
SQL Functions support list
Functions | Supported |
---|---|
ABS | ✅ |
ARCOS | ✅ |
ARSIN | ✅ |
ATAN | ✅ |
CEIL | ✅ |
COALESCE | ✅ |
COMPRESS | ✅ |
COS | ✅ |
COUNT | ✅ |
EXP | ✅ |
FLOOR | ✅ |
INDEX | ✅ |
LENGTH | ✅ |
LENGTHN | ✅ |
LOG | ✅ |
LOWCASE | ✅ |
MAX | ✅ |
MIN | ✅ |
MOD | ✅ |
SIGN | ✅ |
SIN | ✅ |
SQRT | ✅ |
STD | ✅ |
SUBSTR | ✅ |
TAN | ✅ |
TRANWRD | ✅ |
TRIMN | ✅ |
UPCASE | ✅ |
VAR | ✅ |
ATAN2 | ✅ |
DATE | ✅ |
DATEPART | ✅ |
DATETIME | ✅ |
ROUND | ✅ |
TODAY | ✅ |
TRANSLATE | ✅ |
AVG | Partial Support 1. Unsupported interval type. |
SUM | Partial Support 1. Unsupported interval and money types. 2. bigint addition overflow. |
COT | ❌ |
LENGTHC | ❌ |
LOG 10 | ❌ |
LOG 2 | ❌ |
REPEAT | ❌ |
STRIP | ❌ |
TIME | ❌ |
TIMEPART | ❌ |