Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 37 ยท Projects and Practice

Project: Sales Reporting Pipeline

Combine PROC SORT, PROC SQL, macros, PROC MEANS and PROC REPORT into a monthly sales reporting pipeline, with a full SAS program to read and runnable SQL and Python stand-ins for each stage.

This project ties the chapter together. You will design a monthly sales report: clean the raw orders, join them to a customer table, summarize by region and product, and export a report. Each stage uses a tool from this chapter, and the whole thing is parameterized with a macro so that next month's run is a one-line change.

SAS cannot run on this site. The full program below is read-only and shows no output. To let you practice the logic, each stage has a stand-in: standard SQL in SQLite or small Python scripts. Treat them as models of the SAS steps, not as SAS.

The brief

Inputs:

  • raw.orders: order_id, customer_id, order_date (a SAS date), product, units, unit_price. It may contain duplicate order ids and rows with missing prices.
  • raw.customers: customer_id, name, region.

Output: a PDF with revenue by region and product for the chosen month, a total line, and a note listing how many rows were rejected.

flowchart LR
    A["raw.orders"] --> B["Clean: sort, nodupkey, filter"]
    B --> C["Join customers (PROC SQL)"]
    C --> D["Summarize (PROC MEANS)"]
    D --> E["Report (PROC REPORT, ODS PDF)"]
    B --> F["Rejects data set"]

Rejected rows are kept, never silently dropped.

Stage 1: clean

Three rules: keep one row per order_id, drop rows without a price, and keep only the requested month. We also keep what we threw away for auditing.

SAS
%macro clean_orders(year=, month=);
  %let start = %sysfunc(mdy(&month, 1, &year));
  %let end   = %sysfunc(intnx(month, &start, 0, end));

  proc sort data=raw.orders out=work.orders_s nodupkey dupout=work.dup_orders;
    by order_id;
  run;

  data work.orders_ok work.rejects;
    set work.orders_s;
    where &start <= order_date <= &end;
    if missing(unit_price) or units <= 0 then output work.rejects;
    else do;
      revenue = units * unit_price;
      output work.orders_ok;
    end;
  run;
%mend clean_orders;

Note how the dates are computed once as macro variables and then used inside where. intnx(month, &start, 0, end) returns the last day of the month.

Stand-in for stage 1

Python Stand-in: clean orders, keep the rejects

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The reject report is part of the deliverable. A pipeline that quietly drops rows loses trust the first time someone's total does not match.

Stage 2: join and summarize

SAS
proc sql;
  create table work.sales_detail as
    select o.order_id, o.product, o.revenue, c.name, coalesce(c.region, "Unknown") as region
      from work.orders_ok as o
      left join raw.customers as c
        on o.customer_id = c.customer_id;
quit;

proc means data=work.sales_detail noprint nway;
  class region product;
  var revenue;
  output out=work.summary sum=revenue n=orders;
run;

A left join keeps orders whose customer is missing from the customer table (they show up as region Unknown instead of vanishing). Compare row counts before and after the join: they must match.

Stage 2 stand-in in SQL

SQL Join, summarize and check the row count

Press Run (or Ctrl + Enter) to see the result here.

Customer 99 does not exist, and the order is kept under Unknown. The two counts match, which is the check you would write into the SAS log with %put or an assert-style step.

Stage 3: report

SAS
%macro monthly_report(year=, month=);
  %clean_orders(year=&year, month=&month)
  /* ... stage 2 steps here ... */

  proc sql noprint;
    select count(*) into :n_rej trimmed from work.rejects;
  quit;

  ods pdf file="C:\reports\sales_&year._%sysfunc(putn(&month, z2.)).pdf" style=journal;
  title "Sales for &year-%sysfunc(putn(&month, z2.))";
  footnote "Rejected rows: &n_rej";

  proc report data=work.summary;
    column region product revenue orders;
    define region  / group;
    define product / group;
    define revenue / analysis sum format=dollar12.2;
    define orders  / analysis sum "Orders";
    rbreak after / summarize;
  run;

  ods pdf close;
  title; footnote;
%mend monthly_report;

%monthly_report(year=2026, month=10)

Check each piece against what you learned: macro parameters (Macro Programs and Parameters), into : with trimmed (PROC SQL: Creating Tables and Macro Variables), ods pdf and cleared titles (ODS and Output Destinations), and rbreak (PROC REPORT and PROC TABULATE).

Hardening it

  • Log checks: after each stage, %put row counts and stop with %abort if an expected table is empty.
  • Idempotent: every run recreates work tables, so running it twice gives the same result.
  • Parameterized: the month and year are the only inputs.
  • Documented: a header comment with purpose, inputs, outputs and author.
  • Test data: run the macro on a tiny, hand-checked data set first, as in the stand-ins.

Extension ideas

  1. Add a compare to previous month column with a second call and a merge.
  2. Send the summary to Excel with one sheet per region.
  3. Highlight regions under target with call define in a compute block.
  4. Loop over the last six months with a %do loop and stack the results.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why keep a rejects data set instead of just dropping bad rows?

    Visible rejects make the pipeline trustworthy and easy to reconcile.
  2. 2. Why use a LEFT JOIN from orders to customers?

    An inner join would silently drop orders with no matching customer.
  3. 3. Which two numbers should match before and after the join?

    A join on a unique customer key should not change the order count; a difference means duplicate keys or lost rows.
  4. 4. What is the purpose of the %monthly_report macro parameters?

    Parameters turn a one-off program into a repeatable job.

Summary

  • A good SAS pipeline separates cleaning, joining, summarizing and reporting, and keeps its rejects.
  • Macros turn the dates and file names into parameters; into : carries counts into titles and footnotes.
  • Left joins and row-count checks prevent silent data loss.
  • PROC REPORT through ods pdf produces the finished deliverable.
  • The stand-ins model each stage so you can test the logic without SAS.

Next: Project: Clinical Data Validation