🔌 Tutorial 11: SAS Connectivity to Microsoft Fabric¶

/* SAS LIBNAME for Microsoft Fabric Lakehouse */
LIBNAME fabric ODBC
    DSN="Fabric_Casino_Lakehouse"
    SCHEMA="dbo"
    READBUFF=10000
    INSERTBUFF=10000;

/* Test connection */
PROC CONTENTS DATA=fabric._all_ NODS;
RUN;

/* DSN-less connection to Fabric Warehouse */
LIBNAME fabric_wh ODBC
    NOPROMPT="Driver={ODBC Driver 18 for SQL Server};
              Server=xyz67890.datawarehouse.fabric.microsoft.com;
              Database=wh_casino_analytics;
              Authentication=ActiveDirectoryInteractive;
              Encrypt=yes;
              TrustServerCertificate=no;"
    SCHEMA="casino"
    READBUFF=10000;

/* List available tables */
PROC SQL;
    SELECT *
    FROM dictionary.tables
    WHERE libname = 'FABRIC_WH';
QUIT;

/* Environment variables for security (set before SAS starts) */
/* SET SAS_FABRIC_CLIENT_ID=your-client-id */
/* SET SAS_FABRIC_SECRET=your-client-secret */

%LET client_id = %SYSGET(SAS_FABRIC_CLIENT_ID);
%LET client_secret = %SYSGET(SAS_FABRIC_SECRET);

LIBNAME fabric ODBC
    NOPROMPT="Driver={ODBC Driver 18 for SQL Server};
              Server=abc12345.datawarehouse.fabric.microsoft.com;
              Database=lh_casino_poc;
              Authentication=ActiveDirectoryServicePrincipal;
              UID=&client_id.;
              PWD=&client_secret.;
              Encrypt=yes;"
    SCHEMA="dbo";

/* Optimized LIBNAME for large data operations */
LIBNAME fabric ODBC
    DSN="Fabric_Casino_Lakehouse"
    SCHEMA="dbo"
    READBUFF=50000
    INSERTBUFF=10000
    DBCOMMIT=0
    DIRECT_SQL=ALLOW
    PRESERVE_COL_NAMES=YES
    PRESERVE_TAB_NAMES=YES;

/* Read slot_transactions from Fabric Lakehouse */
LIBNAME fabric ODBC DSN="Fabric_Casino_Lakehouse" SCHEMA="dbo";

/* Simple read - all data */
DATA work.slot_transactions;
    SET fabric.slot_transactions;
RUN;

PROC PRINT DATA=work.slot_transactions (OBS=10);
RUN;

/* Read with filter - uses pass-through when possible */
DATA work.recent_transactions;
    SET fabric.slot_transactions (WHERE=(transaction_date >= '2024-01-01'));
RUN;

/* Verify SAS generated appropriate SQL */
OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;
DATA work.filtered;
    SET fabric.slot_transactions (WHERE=(coin_in > 100));
RUN;
OPTIONS SASTRACE=OFF;

/* PROC SQL with pass-through optimization */
PROC SQL;
    CREATE TABLE work.player_summary AS
    SELECT
        player_id,
        COUNT(*) AS session_count,
        SUM(coin_in) AS total_coin_in,
        AVG(session_duration) AS avg_duration
    FROM fabric.player_sessions
    WHERE session_date >= '2024-01-01'
    GROUP BY player_id
    HAVING SUM(coin_in) > 1000
    ORDER BY total_coin_in DESC;
QUIT;

/* Explicit pass-through SQL - executes entirely in Fabric */
PROC SQL;
    CONNECT USING fabric;

    CREATE TABLE work.complex_analysis AS
    SELECT * FROM CONNECTION TO fabric (
        WITH player_metrics AS (
            SELECT
                player_id,
                CAST(transaction_date AS DATE) AS play_date,
                SUM(coin_in) AS daily_coin_in,
                SUM(coin_out) AS daily_coin_out,
                COUNT(*) AS spin_count
            FROM casino.slot_transactions
            WHERE transaction_date >= DATEADD(DAY, -30, GETDATE())
            GROUP BY player_id, CAST(transaction_date AS DATE)
        ),
        player_ranked AS (
            SELECT *,
                ROW_NUMBER() OVER (
                    PARTITION BY player_id
                    ORDER BY daily_coin_in DESC
                ) AS rank_in_player
            FROM player_metrics
        )
        SELECT
            player_id,
            play_date,
            daily_coin_in,
            daily_coin_out,
            spin_count
        FROM player_ranked
        WHERE rank_in_player <= 3
    );

    DISCONNECT FROM fabric;
QUIT;

/* Connect to Fabric Warehouse (not Lakehouse SQL endpoint) */
LIBNAME fabric_wh ODBC
    NOPROMPT="Driver={ODBC Driver 18 for SQL Server};
              Server=xyz67890.datawarehouse.fabric.microsoft.com;
              Database=wh_casino_analytics;
              Authentication=ActiveDirectoryInteractive;
              Encrypt=yes;"
    SCHEMA="sas_output"
    BULKLOAD=YES
    INSERTBUFF=10000;

/* Create sample SAS dataset */
DATA work.player_scores;
    INPUT player_id $ score segment $;
    DATALINES;
P001 85 HIGH
P002 72 MEDIUM
P003 45 LOW
P004 91 HIGH
P005 68 MEDIUM
;
RUN;

/* Write to Fabric Warehouse */
DATA fabric_wh.player_scores;
    SET work.player_scores;
RUN;

/* Verify write */
PROC SQL;
    SELECT COUNT(*) AS row_count FROM fabric_wh.player_scores;
QUIT;

/* Efficient bulk append for large datasets */
PROC APPEND
    BASE=fabric_wh.transaction_history
    DATA=work.new_transactions
    FORCE;
RUN;

/* Create table with specific data types */
PROC SQL;
    CONNECT USING fabric_wh;

    EXECUTE (
        CREATE TABLE sas_output.player_analytics (
            player_id VARCHAR(50) NOT NULL,
            analysis_date DATE NOT NULL,
            churn_score DECIMAL(5,4),
            ltv_prediction DECIMAL(12,2),
            segment VARCHAR(20),
            model_version VARCHAR(10),
            created_at DATETIME2 DEFAULT GETDATE()
        )
    ) BY fabric_wh;

    DISCONNECT FROM fabric_wh;
QUIT;

/* Insert data */
DATA fabric_wh.player_analytics;
    SET work.model_output;
RUN;

/* Export to CSV for OneLake ingestion */
PROC EXPORT DATA=work.player_scores
    OUTFILE="/mnt/onelake/workspace/lakehouse/Files/sas_output/player_scores.csv"
    DBMS=CSV
    REPLACE;
RUN;

/* Note: Requires OneLake mounted or accessible via file path */
/* After export, use Fabric notebook to load into Delta table */

/* OLE DB LIBNAME for Microsoft Fabric */
LIBNAME fabric OLEDB
    PROVIDER="MSOLEDBSQL"
    PROPERTIES=(
        "Data Source"="abc12345.datawarehouse.fabric.microsoft.com"
        "Initial Catalog"="lh_casino_poc"
        "Authentication"="ActiveDirectoryInteractive"
        "Encrypt"="yes"
    )
    SCHEMA="dbo";

/* Test connection */
PROC SQL;
    SELECT * FROM fabric.slot_transactions (OBS=5);
QUIT;

/* Daily SAS job to score players and write back to Fabric */

/* Step 1: Connect to Fabric */
LIBNAME fabric ODBC DSN="Fabric_Casino_Lakehouse" SCHEMA="gold";
LIBNAME output ODBC DSN="Fabric_Casino_Warehouse" SCHEMA="sas_output"
    BULKLOAD=YES;

/* Step 2: Read player data from Gold layer */
PROC SQL;
    CREATE TABLE work.player_features AS
    SELECT *
    FROM fabric.gold_player_360
    WHERE last_activity_date >= DATEADD(DAY, -30, GETDATE());
QUIT;

/* Step 3: Apply churn prediction model */
PROC LOGISTIC INMODEL=saslib.churn_model;
    SCORE DATA=work.player_features
          OUT=work.player_scores;
RUN;

/* Step 4: Prepare output */
DATA work.predictions;
    SET work.player_scores;
    prediction_date = TODAY();
    churn_probability = P_1;
    churn_segment = IFC(P_1 > 0.7, 'HIGH_RISK',
                    IFC(P_1 > 0.4, 'MEDIUM_RISK', 'LOW_RISK'));
    KEEP player_id prediction_date churn_probability churn_segment;
RUN;

/* Step 5: Write predictions to Fabric Warehouse */
PROC SQL;
    DELETE FROM output.player_churn_predictions
    WHERE prediction_date = TODAY();
QUIT;

DATA output.player_churn_predictions;
    SET work.predictions;
RUN;

/* Step 6: Log results */
%PUT NOTE: Processed %NOBS(work.predictions) players;
%PUT NOTE: Predictions written to Fabric Warehouse;

/* Cleanup */
LIBNAME fabric CLEAR;
LIBNAME output CLEAR;

/* Macro for on-demand player scoring */
%MACRO score_player(player_id);
    LIBNAME fabric ODBC DSN="Fabric_Casino_Lakehouse" SCHEMA="gold";

    PROC SQL NOPRINT;
        CREATE TABLE work.player_&player_id. AS
        SELECT *
        FROM fabric.gold_player_360
        WHERE player_id = "&player_id.";
    QUIT;

    /* Apply model */
    PROC LOGISTIC INMODEL=saslib.churn_model;
        SCORE DATA=work.player_&player_id.
              OUT=work.score_&player_id.;
    RUN;

    /* Return score */
    PROC SQL NOPRINT;
        SELECT P_1 INTO :churn_score
        FROM work.score_&player_id.;
    QUIT;

    %PUT Player &player_id. churn score: &churn_score.;

    LIBNAME fabric CLEAR;
%MEND;

/* Usage */
%score_player(P12345);

/* Generate CTR/SAR reports using Fabric data */
LIBNAME fabric ODBC DSN="Fabric_Casino_Lakehouse" SCHEMA="gold";

/* CTR: Transactions over $10,000 */
PROC SQL;
    CREATE TABLE work.ctr_report AS
    SELECT
        transaction_id,
        player_id,
        transaction_timestamp,
        amount,
        transaction_type,
        'CTR' AS report_type
    FROM fabric.gold_cage_operations
    WHERE amount >= 10000
      AND transaction_date = TODAY() - 1;
QUIT;

/* SAR: Suspicious patterns ($8K-$9.9K range) */
PROC SQL;
    CREATE TABLE work.sar_candidates AS
    SELECT
        player_id,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_amount,
        MIN(transaction_timestamp) AS first_txn,
        MAX(transaction_timestamp) AS last_txn
    FROM fabric.gold_cage_operations
    WHERE amount BETWEEN 8000 AND 9999
      AND transaction_date >= TODAY() - 7
    GROUP BY player_id
    HAVING COUNT(*) >= 3;  /* Multiple transactions in range */
QUIT;

/* Export reports */
ODS PDF FILE="/reports/compliance/daily_ctr_%sysfunc(today(), yymmddn8.).pdf";
PROC PRINT DATA=work.ctr_report;
    TITLE "Currency Transaction Report - %sysfunc(today(), date9.)";
RUN;
ODS PDF CLOSE;

/* Enable detailed ODBC tracing */
OPTIONS SASTRACE=',,,d'
        SASTRACELOC=SASLOG
        NOSTSUFFIX
        MSGLEVEL=I;

/* Attempt connection with verbose logging */
LIBNAME debug ODBC
    NOPROMPT="Driver={ODBC Driver 18 for SQL Server};
              Server=abc12345.datawarehouse.fabric.microsoft.com;
              Database=lh_casino_poc;
              Authentication=ActiveDirectoryInteractive;
              Encrypt=yes;"
    SCHEMA="dbo";

/* Check log for detailed error messages */

/* Disable tracing */
OPTIONS SASTRACE=OFF;
LIBNAME debug CLEAR;

/* Debug service principal authentication */
%LET client_id = %SYSGET(SAS_FABRIC_CLIENT_ID);
%LET client_secret = %SYSGET(SAS_FABRIC_SECRET);

/* Check if variables are set */
%PUT Client ID length: %LENGTH(&client_id.);
%PUT Secret length: %LENGTH(&client_secret.);

%IF %LENGTH(&client_id.) = 0 %THEN %DO;
    %PUT ERROR: SAS_FABRIC_CLIENT_ID environment variable not set;
%END;