Skip to main content
Version: v2

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.

1_pg_sas_connect_pg

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. wire_protocol_test_84077 ). The SQL Name location is in the Workspace, click the Share button and switch to the PostgreSQL Wire Protocol page. 1_sas

When the connection is completed, we can see the following connection success message in the log.

2_pg_sas_connect_successfully

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.

3_pg_sas_apply_proc_sql

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.

4_pg_sas_view_result

SQL Functions support list

FunctionsSupported
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
AVGPartial Support
1. Unsupported interval type.
SUMPartial Support
1. Unsupported interval and money types.
2. bigint addition overflow.
COT
LENGTHC
LOG 10
LOG 2
REPEAT
STRIP
TIME
TIMEPART