Extraction Queries for SAS Base

Alation Cloud Service Applies to Alation Cloud Service instances of Alation

Customer Managed Applies to customer-managed instances of Alation

The default extraction queries for the SAS Base data source are provided below. You can customize them to better suit your catalog needs. Custom queries can be specified on the Metadata Extraction tab of the data source Settings page. You can customize all or some of the queries.

Schema

Ensure your query has a column labelled as SCHEMA in the SELECT list.

SELECT UNIQUE LIBNAME AS SCHEMA
FROM DICTIONARY.MEMBERS
WHERE LIBNAME NOT IN ('''')
    AND LIBNAME NOT IN ('information_schema' , 'SASHELP' , 'SASUSER' , 'WORK' , 'USER');

Table

Ensure your query has columns labelled as SCHEMA, TABLE_NAME, TABLE_TYPE, and REMARKS in the SELECT list.

SELECT LIBNAME AS SCHEMA,
    MEMNAME AS TABLE_NAME,
    MEMTYPE AS TABLE_TYPE,
    MEMLABEL AS REMARKS
FROM DICTIONARY.TABLES
WHERE MEMTYPE = 'DATA'
    AND LIBNAME  NOT IN ('''')
    AND LIBNAME NOT IN ('information_schema' , 'SASHELP' , 'SASUSER' , 'WORK' , 'USER');

View

Ensure your query has columns labelled as SCHEMA, VIEW_NAME, VIEW_TYPE, VIEW_CREATE_STATEMENT, and REMARKS in the SELECT list.

SELECT LIBNAME AS SCHEMA,
    MEMNAME AS VIEW_NAME,
    MEMTYPE AS VIEW_TYPE,
    '' AS VIEW_CREATE_STATEMENT,
    MEMLABEL AS REMARKS
FROM DICTIONARY.TABLES
WHERE MEMTYPE = 'VIEW'
    AND LIBNAME NOT IN ('''')
    AND LIBNAME NOT IN ('information_schema' , 'SASHELP' , 'SASUSER' , 'WORK' , 'USER');

Column

Ensure your query has columns labelled as SCHEMA, TABLE_NAME, COLUMN_NAME, REMARKS, ORDINAL_POSITION, IS_NULLABLE, DATA_TYPE, TYPE_NAME, and COLUMN_DEFAULT in the SELECT list.

SELECT LIBNAME AS SCHEMA,
    MEMNAME AS TABLE_NAME,
    NAME AS COLUMN_NAME,
    FORMAT AS COLUMN_FORMAT,
    LABEL AS REMARKS,
    LENGTH AS CHAR_OCTET_LENGTH,
    VARNUM AS ORDINAL_POSITION,
    NOTNULL AS IS_NULLABLE,
    TYPE AS DATA_TYPE,
    CASE WHEN TYPE = 'char' THEN 'char('||strip(put(LENGTH, 5.))||')'
        ELSE TYPE
    END AS TYPE_NAME,
    '' AS COLUMN_DEFAULT
FROM DICTIONARY.COLUMNS
WHERE LIBNAME NOT IN ('''')
    AND LIBNAME NOT IN ('information_schema' , 'SASHELP' , 'SASUSER' , 'WORK' , 'USER');

Primary Keys

Ensure your query has columns labelled as SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION, and PK_NAME in the SELECT list.

SELECT LIBNAME AS SCHEMA,
    MEMNAME AS TABLE_NAME,
    NAME AS COLUMN_NAME,
    INDXPOS AS ORDINAL_POSITION,
    INDXNAME AS PK_NAME
FROM DICTIONARY.INDEXES
WHERE UNIQUE = 'yes'
    AND LIBNAME NOT IN ('''')
    AND LIBNAME NOT IN ('information_schema' , 'SASHELP' , 'SASUSER' , 'WORK' , 'USER');

Index

Ensure your query has columns labelled as SCHEMA, TABLE_NAME, INDEX_NAME, INDEX_TYPE, ORDINAL_POSITION, and COLUMN_NAME in the SELECT list.

SELECT LIBNAME AS SCHEMA,
    MEMNAME AS TABLE_NAME,
    INDXNAME AS INDEX_NAME,
    IDXUSAGE AS INDEX_TYPE,
    INDXPOS AS ORDINAL_POSITION,
    NAME AS COLUMN_NAME
FROM DICTIONARY.INDEXES
WHERE UNIQUE = 'no'
    AND LIBNAME  NOT IN ('''')
    AND LIBNAME  NOT IN ('information_schema' , 'SASHELP' , 'SASUSER' , 'WORK' , 'USER');