SAS

This article will introduce how to get data from the SAS connection to Canner Enterprise.

Overview

Support Scope

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 path
• postgres server: fill in the Host or IP address of Canner Enterprise
• port: fill in 7432
• user: Fill in the canner
• password : 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. test_58575 ). The SQL Name location is in the Workspace, click the Share button and switch to the Canner 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.

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	❌