*Copyright @ www.mycsg.in;

COMPUTE blocks in PROC REPORT - derived columns and conditional formatting

What is a COMPUTE block

• Previous PROC REPORT lessons covered basic reports, grouping, BREAK, RBREAK, and a basic COMPUTE AFTER label
• A COMPUTE block is a section of code inside PROC REPORT that calculates new values or applies conditional logic row by row as the report is built
• COMPUTE blocks can create entirely new derived columns, modify existing column values, and apply conditional highlighting or text changes
• They are written between `compute column_name;` and `endcomp;` and use a limited subset of DATA step assignment and IF-THEN logic
• COMPUTE blocks are the primary mechanism for custom calculations inside PROC REPORT without preprocessing the data separately

Create sample data

• We create a sales performance dataset with actual and target figures per department and region
• This data will be used to demonstrate a derived ratio column and conditional text based on whether the target was met

data sales; input dept $ region $ actual target; datalines; Marketing East 1200 1000 Marketing West 900 1100 Marketing North 1400 1200 Sales East 2000 1800 Sales West 1600 2000 Sales North 2200 1900 Support East 500 450 Support West 420 500 Support North 550 500 ; run; Copy Code View Log

• Confirm that `sales` has 9 rows — three regions per department
• Some actual values exceed their target and some fall short — this asymmetry will make the conditional logic meaningful

View Data

Creating a derived column with a COMPUTE block

Define a COMPUTED column and calculate a percentage

• To create a derived column in PROC REPORT, you first declare the column with `define colname / computed`
• A COMPUTE block then calculates the value for each row using other column names
• Inside a COMPUTE block, columns with a statistic role such as `sum` must be referenced with the statistic as a suffix — for example `actual.sum` and `target.sum`, not just `actual` or `target`
• The derived column exists only in the report — no new variable is added to the input dataset

proc report data=sales nowd; column dept region actual target pct_target; define dept / group 'Department'; define region / group 'Region'; define actual / sum 'Actual Sales'; define target / sum 'Target'; define pct_target / computed format=percent8.1 'Pct of Target'; compute pct_target; if target.sum ne 0 then pct_target = actual.sum / target.sum; else pct_target = .; endcomp; run; Copy Code View Log

• The `pct_target` column shows each row's actual as a percentage of its target
• Note that `actual.sum` and `target.sum` inside the COMPUTE block refer to the aggregated column values as computed by the report — the `.sum` suffix is required because these columns are defined with the `sum` statistic role
• When the report includes grouping and summary rows, the COMPUTE block runs for every row including group totals — the percentage at the group total row uses the summed actual and summed target values

Conditional logic inside a COMPUTE block

Flag rows based on whether actual met the target

• A COMPUTE block can use `IF-THEN-ELSE` to assign different text or values based on conditions
• One common pattern is creating a status label that describes whether the target was met
• The derived column must be declared with the correct type — for character results, add `character length=n` on the COMPUTE statement itself, not the DEFINE statement

proc report data=sales nowd; column dept region actual target status; define dept / group 'Department'; define region / group 'Region'; define actual / sum 'Actual Sales'; define target / sum 'Target'; define status / computed 'Status' ; compute status/character length=15; if actual.sum ge target.sum then status='Met Target'; else status='Below Target'; endcomp; run; Copy Code View Log

• Each row now shows either `Met Target` or `Below Target` based on the comparison of actual and target for that row
• Verify that Marketing West shows `Below Target` because its actual (900) is below its target (1100)
• The `character length=15` is specified on the COMPUTE statement (`compute status / character length=15;`) — the DEFINE statement only needs `/ computed` and the column label; placing the length specification on the COMPUTE statement is what tells SAS the column is character and how wide to make it

Combine derived percentage and conditional status in one report

• Multiple COMPUTE blocks can appear in the same PROC REPORT step
• Each block is independent and applies to its own defined column

proc report data=sales nowd; column dept region actual target pct_target status; define dept / group 'Department'; define region / group 'Region'; define actual / sum 'Actual Sales'; define target / sum 'Target'; define pct_target / computed format=percent8.1 'Pct of Target'; define status / computed 'Status' ; compute pct_target; if target.sum ne 0 then pct_target = actual.sum / target.sum; else pct_target = .; endcomp; compute status/character length=15; if actual.sum ge target.sum then status='Met Target'; else status='Below Target'; endcomp; run; Copy Code View Log

• The report now shows four data columns: actual, target, percentage, and status — all derived or computed in PROC REPORT without modifying the input dataset
• Review every row and confirm that the status aligns correctly with whether actual is above or below target

Using COMPUTE with BREAK and RBREAK

Add a derived column that also appears on group total rows

• When BREAK or RBREAK is used, the COMPUTE block runs for the summary line as well, using the aggregated column values
• The percentage at the department total row will correctly use the summed actual and summed target, giving a meaningful overall percentage

proc report data=sales nowd; column dept region actual target pct_target; define dept / group 'Department'; define region / group 'Region'; define actual / sum 'Actual Sales'; define target / sum 'Target'; define pct_target / computed format=percent8.1 'Pct of Target'; compute pct_target; if target.sum ne 0 then pct_target = actual.sum / target.sum; else pct_target = .; endcomp; break after dept / summarize; rbreak after / summarize; run; Copy Code View Log

• The department-level subtotal rows and the grand total row each show a percentage computed from the summed actual and target values
• This means the subtotal percentage is not an average of the individual row percentages but rather the correct aggregate ratio — the behaviour most reports require

Key points to remember

• Declare a derived column with `define colname / computed` in PROC REPORT — this tells the procedure to expect a COMPUTE block that supplies the column's values
• For character computed columns, add `character length=n` to the COMPUTE statement (`compute colname / character length=n;`) — the DEFINE statement only needs `/ computed`
• Inside a COMPUTE block, columns with a statistic role must be referenced with the statistic suffix — for example `actual.sum` and `target.sum` for columns defined as `sum`; using the bare name without the suffix will not reference the correct value
• COMPUTE blocks run for every output row including BREAK and RBREAK summary lines — percentages and other derived values will use summed values at those rows
• Multiple COMPUTE blocks can coexist in a single PROC REPORT step — one block per computed column
• COMPUTE block logic is limited to assignment statements and IF-THEN-ELSE — complex DATA step operations such as RETAIN are not supported