# Move SAS reporting workflows to DRE | DRE

> Keep your database. Inspect a synthetic overdue-loan migration with Oracle and SQL Server SQL, checked Excel outputs and an unexecuted SAS reference.

[← All guides](https://getdre.com/guides/)

# Move your SAS reporting workflows to DRE. Keep your database.

For recurring database-to-Excel reporting: move preparation and business rules into SQL in your existing database. Keep the report definition in git. Generate a simple workbook or fill a workbook designed in Excel.

**Released for DRE 0.10.0, JDBC 1.0.0 and XLSX 1.1.3.** Install the CLI, configure a compatible Java runtime and vendor driver, then run the same project on Oracle or SQL Server.

[Download the complete example](https://getdre.com/media/downloads/overdue-loans/overdue-loans.tar.gz)[Setup and requirements](#setup)

## A monthly overdue-loan pack, end to end

Wholly synthetic customers, loans, repayments and branches. January’s independently specified result is **8 detail accounts and AUD 3,480.01**. Both outputs contain Summary, Detail and Exceptions. The template also retains Notes.

See the reporting program beside DRE’s compact YAML definition, then watch the real Oracle run, keyed result checks and both workbooks opened in Excel. The business logic lives in database SQL; YAML declares the sheets, formatting and outputs.

SAS source to DRE SQL and YAML: a real Oracle run, keyed checks and two Excel workbook approaches. Synthetic data.

SAS source to DRE SQL and YAML: a real Oracle run, keyed checks and two Excel workbook approaches. Synthetic data.

[Read the recording captions](https://getdre.com/media/sas_overdue/captions.vtt)

## One set of business rules. Two workbook approaches.

### Generate the workbook

Declare tabs, column widths, number formats and banded rows in YAML. Keep money numeric and identifiers as text, including leading zeros and long account IDs.

[Download the simple workbook](https://getdre.com/media/downloads/overdue-loans/simple-2026-01-31.xlsx)

### Fill an Excel-authored template

Keep the authored layout, frozen panes, filters, wrapping and conditional highlighting. Bind query results into expanding rows. The illustrative “2x (demo)” column is not a banking measure.

[Download the template workbook](https://getdre.com/media/downloads/overdue-loans/presentation-2026-01-31.xlsx)

SQL balances are authoritative. Template formulas recalculate in Excel; saved formula caches are not evidence of calculated results. Separate sheets and SQL groups are practical alternatives to exact SAS group-break layouts.

## Inspect the migration

1.  **Start with the specification and inputs.** The archive includes REPORT-SPEC.md, small CSV inputs and literal expected records. Boundary dates, nulls, duplicate versions, rounding and text identifiers have explicit rules.
2.  **Review the unexecuted reference.sas.** It illustrates Base SAS, PROC SQL and ODS EXCEL. No SAS runtime was available. DRE was validated against independent business expectations, not established SAS output parity.
3.  **Read the database-specific SQL.** Oracle and SQL Server each seed private session tables, resolve duplicate versions before joining, apply month-end rules and query Summary, Detail and Exceptions.
4.  **Run and reconcile.** The checker compares keyed fields, amounts, classifications, nulls, workbook cell types and totals. Existing SAS CSV/XLSX outputs are optional additional comparison inputs when their date, parameters and inputs match.

Oracle: reports/overdue/summary.sql

```sql
select branch_name || ' / ' || product || ' / ' || bucket as "branch_product_bucket",
       cast(count(*) as number(10,0)) as "accounts",
       cast(sum(balance) as number(18,2)) as "balance"
from ora$ptt_prepared
where exception is null and balance > 0
group by branch_name, product, bucket
order by branch_name, product, bucket;
```

[Browse the reviewed source and full business specification](https://github.com/get-dre/dre/tree/97b06a4ed5b204f60c0f49bc3fe4d5220bcd4609/examples/overdue-loans)

## Setup: your Java, your driver, your database

**Install DRE 0.10.0 or newer.** Follow the [installation guide](https://getdre.com/docs/install/), then use the released [JDBC guide](https://github.com/get-dre/dre/tree/97b06a4ed5b204f60c0f49bc3fe4d5220bcd4609/docs/plugin-jdbc.md) for connection details. The download README contains complete connection examples for both databases.

Install the released CLI

```bash
python3 -m pip install dre-cli==0.10.0
dre --version
```

-   **Java 17 or newer:** install and maintain a compatible runtime yourself. DRE selects JAVA\_HOME/bin/java, otherwise java on PATH. The JDBC plugin does not bundle, download, install, patch or update Java.
-   **Vendor driver JAR:** supply Oracle Thin or Microsoft SQL Server’s JDBC driver yourself. Neither the download nor the plugin redistributes or downloads it. Vendor Java requirements still apply.
-   **DRE, JDBC and XLSX:** use DRE 0.10.0 or newer, JDBC 1.0.0 or newer and XLSX 1.1.3 or newer. Earlier template versions can change text identifiers or expansion ranges. Python 3.8+ prepares fixtures and checks results.
-   **A disposable test account:** Oracle needs CREATE SESSION and CREATE TABLE; SQL Server needs connection access and local temporary-table creation. Review SQL before execution. Database permissions are the security boundary; a JDBC read-only hint is not one.

profiles.yml — Oracle example

```yaml
connections:
  reporting:
    targets:
      dev:
        type: jdbc
        url: "{{ env_var('DRE_TEST_JDBC_URL') }}"
        driver_jars: "{{ env_var('DRE_TEST_JDBC_JAR') }}"
        properties:
          user: "{{ env_var('DRE_TEST_JDBC_USER') }}"
          password: "{{ env_var('DRE_TEST_JDBC_PASSWORD') }}"
          oracle.jdbc.fanEnabled: false
```

Use an environment secret store for passwords. Specify each setting once, in the URL or properties: duplicate-setting precedence belongs to the driver. Keep SQL Server certificate validation enabled in production; the README’s self-signed test settings apply only to disposable local fixtures.

After installing DRE and setting connection variables

```bash
tar -xzf overdue-loans.tar.gz
cd overdue-loans
python3 prepare.py oracle
cd oracle
dre deps
dre validate
dre run overdue
cd ..
python3 check.py oracle --verify oracle
```

For SQL Server, substitute `sqlserver` for `oracle`. The queries prepare and classify data in the source database on one report session. Python only creates literal fixture SQL and verifies outputs. Session tables disappear on disconnect.

Rerun February with `dre run overdue --var report_date=2026-02-28`: expected 9 accounts and AUD 4,375.01. Your scheduler can invoke the same command with its secret environment. Check the exit status and run record: old dated files can remain after a failed run.

## What has been tested

Database

Pinned environment exercised in CI

Oracle Free

23.26.3.0-lite, Oracle JDBC 23.26.3.0.0, Java 17

SQL Server

2022 CU27 (16.0.4295.3), x86-64 Linux, Microsoft JDBC 13.6.1.jre11, Java 17

Both downloadable variants passed real database-to-workbook checks. The recording uses Oracle on Apple Silicon. These fixtures establish tested versions; they do not certify every historical version. Other databases may work through compatible user-supplied JDBC drivers and need their own checks.

## Get help porting a report

The released [report-porting skill](https://github.com/get-dre/dre/tree/97b06a4ed5b204f60c0f49bc3fe4d5220bcd4609/skills/dre-port/SKILL.md) inventories source programs, includes, macros, dependencies and optional reference outputs. It can use authorized live schema discovery or supplied DDL, draft database-specific SQL and a DRE report, then guide business-rule checks. Read [how DRE agent skills work](https://getdre.com/agents/) or install skills 5.1.0 from the current channel.

Start with SQL selections, joins and aggregates, simple DATA-step filters and derived columns, deterministic deduplication, scalar parameters and tabular reports. Review generated SQL before running it, especially preparation statements.

Stateful DATA steps, SAS match-merges, complex macro execution, local datasets, unavailable dependencies and specialist procedures need explicit handling. The skill flags unresolved semantics rather than treating plausible SQL as a completed migration.

DRE does not execute SAS or replace statistical modelling, risk engines or packaged banking applications. Native charts, PivotTables, slicers, macros and external connections are not promised preserved, refreshed or created. Open any changed template in your intended Excel version before adoption.

## Download and verify

The project includes both database variants, the template, synthetic inputs, expected results, unexecuted SAS reference, preparation/checking tools and GPL licence. It contains no Java runtime, vendor drivers or credentials.

[Download project (.tar.gz)](https://getdre.com/media/downloads/overdue-loans/overdue-loans.tar.gz)[SHA-256 checksum](https://getdre.com/media/downloads/overdue-loans/overdue-loans.tar.gz.sha256)

Verify the download before extracting

```bash
shasum -a 256 -c overdue-loans.tar.gz.sha256
```