*Copyright @ www.mycsg.in;

Log Issues - Part 02: _ERROR_ Variable, Format-Not-Found, and BY Sort Order

Overview

SAS generates several categories of log messages: NOTE, WARNING, and ERROR.
Beyond messages printed to the log, SAS also maintains an automatic variable called _ERROR_ that records whether a data error occurred during the current iteration of the DATA step.
This lesson covers three common and important log issues: detecting data errors programmatically using _ERROR_, diagnosing format-not-found warnings, and understanding the BY-not-sorted error.
Being able to read and interpret these messages accurately is essential for debugging programs and producing reliable output.

The _ERROR_ Automatic Variable

_ERROR_ is an automatic variable that SAS creates in every DATA step.
It is initialised to 0 at the start of each iteration (each row read from the input dataset).
SAS sets _ERROR_ to 1 whenever a data error occurs during that iteration — for example, when a character value cannot be converted to a number, or when an invalid mathematical operation is attempted.
_ERROR_ is not written to the output dataset by default, but it is available for conditional logic during the DATA step.
You can use _ERROR_ to flag, count, or selectively output only rows that caused data errors, which is useful for quality checking.

The following example reads a small dataset that contains a mix of valid and invalid numeric values stored as character.
The INPUT function attempts to convert each character value to a number.
When conversion fails, SAS sets _ERROR_ to 1 and writes a NOTE to the log.
We use _ERROR_ to capture and output only the problem rows for inspection.

data work.source;
    input subject $ raw_value $;
    datalines;
001 42
002 .
003 abc
004 17
005 ??
;
run;
 
data work.converted work.errors;
    set work.source;
    numeric_value = input(raw_value, ?? best12.);
    if _error_ = 1 then do;
        output work.errors;
        _error_ = 0;
    end;
    else output work.converted;
run;

The ?? best12. informat suppresses the invalid data NOTE from the log — but _ERROR_ is still set to 1 internally when conversion fails, so the flag remains useful.
Using a single question mark ? best12. suppresses the NOTE but still sets _ERROR_; using ?? suppresses both the NOTE and the invalid data indicator in the log, but _ERROR_ is still set.
Check WORK.ERRORS — it should contain subjects 003 and 005 whose raw_value could not be converted.
Check WORK.CONVERTED — it should contain subjects 001, 002, and 004 where conversion succeeded (002 becomes a SAS missing numeric value, which is valid).
Resetting _ERROR_ to 0 after routing the row prevents SAS from printing an extra NOTE at the end of the step about _ERROR_ being set.

Using _ERROR_ to Count Data Problems

A practical pattern is to use RETAIN and a counter variable to accumulate the total number of data errors across all rows, then report it at the end of the DATA step using a PUT statement.
This lets you confirm at a glance whether any data quality issues were present without scanning the entire log.

data work.checked;
    retain error_count 0;
    set work.source end=last_row;
    numeric_value = input(raw_value, ?? best12.);
    if _error_ = 1 then do;
        error_count + 1;
        _error_ = 0;
    end;
    if last_row then put "NOTE: Total conversion errors detected: " error_count;
    drop error_count;
run;

RETAIN error_count 0 initialises the counter to zero and prevents it from resetting between rows.
The end=last_row dataset option sets the temporary variable last_row to 1 only on the final observation, allowing the summary PUT to execute exactly once.
Check the SAS log after running this step — you should see a NOTE line reporting the total count of conversion errors.

Format-Not-Found Warning

A format-not-found warning occurs when a variable references a format (via FORMAT or ATTRIB statement, or stored in a dataset attribute) that SAS cannot locate in the format search path.
The warning typically reads: WARNING: Format MYFMT was not found or could not be loaded.
SAS will still continue processing, but the variable will display its raw unformatted value wherever the missing format would have been applied.
The most common causes are: the format was created in a different SAS session, the FMTSEARCH= option does not include the library where the format lives, or the format name was misspelled.

The following example intentionally references a format called $STATUSFMT that does not exist, to show the warning message and demonstrate the FMTSEARCH= option as the fix.

*------------------------------------------------------------------------------;
* Create a format in a named library ;
*------------------------------------------------------------------------------;
proc format library=work;
    value $statusfmt
        'A' = 'Active'
        'D' = 'Discontinued'
        'C' = 'Completed';
run;
 
*------------------------------------------------------------------------------;
* Apply the format to a dataset ;
*------------------------------------------------------------------------------;
data work.subjects;
    input subject $ status $;
    format status $statusfmt.;
    datalines;
001 A
002 D
003 C
;
run;
 
*------------------------------------------------------------------------------;
* If WORK is not in the FMTSEARCH path, reading this dataset elsewhere ;
* may trigger format-not-found. Add WORK explicitly to resolve. ;
*------------------------------------------------------------------------------;
options fmtsearch=(work);
 
proc print data=work.subjects label;
run;

PROC FORMAT with LIBRARY=WORK stores the format in the WORK library for this session.
The FORMAT statement in the DATA step attaches $STATUSFMT to the status variable as a permanent attribute stored in the dataset header.
When another program or session reads WORK.SUBJECTS, it searches the format libraries in the order specified by FMTSEARCH=. If WORK is not listed, the format is not found and SAS issues a WARNING.
Adding OPTIONS FMTSEARCH=(WORK) or pointing to whatever library holds the format resolves the warning.
If you want the data to print without the format at all, use FORMAT status; (no format name) to detach the format for the current PROC step.

BY Statement - Data Not Sorted Error

When you use a BY statement in a DATA step or PROC step, SAS requires that the data are already sorted in the order of the BY variables.
If SAS encounters a row where the BY variable value is less than the previous row's value, it issues an ERROR: Data set WORK.xxx is not sorted in ascending sequence.
This is a hard error — processing stops at that point, and the output dataset may be incomplete.
The fix is always to run PROC SORT on the dataset before the step that uses the BY statement.

*------------------------------------------------------------------------------;
* Dataset that is NOT sorted - will cause a BY error ;
*------------------------------------------------------------------------------;
data work.unsorted;
    input subject $ visit;
    datalines;
002 1
001 1
003 1
001 2
;
run;
 
*------------------------------------------------------------------------------;
* This will error because BY requires sorted input ;
*------------------------------------------------------------------------------;
/*
data work.will_fail;
    set work.unsorted;
    by subject;
run;
*/
 
*------------------------------------------------------------------------------;
* Correct approach: sort first ;
*------------------------------------------------------------------------------;
proc sort data=work.unsorted out=work.sorted;
    by subject visit;
run;
 
data work.with_by;
    set work.sorted;
    by subject;
    if first.subject then visit_count = 0;
    visit_count + 1;
run;

The intentionally failing DATA step is commented out so the lesson runs cleanly — uncomment it to see the BY error in the log for yourself.
PROC SORT with OUT= creates a new sorted copy without modifying the original dataset, which is good practice when you want to preserve the original order.
After sorting, the BY statement works correctly and FIRST.SUBJECT is available for BY-group logic.
The visit_count variable increments within each subject group, confirming that the BY-group iteration is functioning as expected.
Always sort immediately before the step that needs the BY — relying on a sort from earlier in the program can lead to subtle bugs if the data is modified between steps.

Key Points

_ERROR_ is set to 1 by SAS whenever a data error occurs on the current row — use it to flag and route problem rows rather than relying solely on log scanning.
Reset _ERROR_ to 0 after handling the error to prevent extra log messages and keep the final _ERROR_ count meaningful.
Format-not-found warnings occur when the format search path (FMTSEARCH=) does not include the library where the format was created — add that library to resolve it.
BY-not-sorted is always a programming error — ensure the input dataset is sorted by the BY variables before any step that uses a BY statement.
Reading these messages carefully and locating the exact observation or format name mentioned in the error message is the fastest path to a fix.

*Copyright @ www.mycsg.in;

Log Issues - Part 02: _ERROR_ Variable, Format-Not-Found, and BY Sort Order

Overview

The _ERROR_ Automatic Variable

SAS Log

Dataset View

Using _ERROR_ to Count Data Problems

SAS Log

Format-Not-Found Warning

SAS Log

BY Statement - Data Not Sorted Error

SAS Log

Dataset View

Key Points