28 Ways to Be Missing in SAS

A SAS numeric missing is not a lone wolf: the ordinary missing (.) is one of 28 distinct missing values a numeric variable can hold. The other 27 are written with a single character - the letters A to Z, or an underscore (._).

They exist because "missing" is usually not the whole story. A survey question that was never reached, a reading that was illegible, a value the respondent refused to give - in a well-run process those are different facts, and a lone . throws the difference away. Special missings record why the value is missing.

They are numbers, not text

• Arithmetic on them yields missing - .A + 1 is .
• PROC MEANS, PROC SUMMARY and the other summarisation procedures exclude them, exactly as they exclude .
• They sort below every non-missing number, in the order ._, then ., then .A to .Z
• NMISS() and CMISS() count them as missing
• Converting one to text drops the period - CATS(.A) is the string A

One display detail is worth knowing: a regular missing prints as ., unless OPTIONS MISSING= changes that character - set it to blank and a regular missing renders as an empty cell. The option affects only the regular missing; ._ and .A-.Z always print as their own letter. So a blank cell in a SAS listing is still unambiguously a regular missing, and a lone letter is still a special one. Data Controller itself is unaffected either way - it sends a regular missing to the browser as null and a special missing as its letter.

In a SAS dataset they are written with a leading period (.A, .B ... ._). In Data Controller you type the letter or the underscore, with or without that period - .a and a are the same missing - and the letter is not case sensitive. Two letters, or a letter mixed with a number, are refused rather than guessed.

There is one cell where the letter cannot be typed at all. A numeric column that carries a date, datetime or time format is edited through a date picker rather than the numeric editor, and a picker accepts only a date.

Carrying them between the browser and SAS

The Data Controller frontend and the SAS backend exchange data as JSON, and JSON has no way to express a letter as a numeric value - A is a string. The conversion is handled in the open source SASjs Adapter:

• The adapter infers each column's SAS type from the values it is given. All numeric values mean a numeric column, all strings mean a character column, and a column holding a single character (a-z, _ or .) alongside numeric values is numeric, with the lone characters written to SAS as special missings.
• null becomes . or an empty string, according to the type derived for that column.
• Two cases cannot be inferred from the values alone: a numeric column containing only special missings looks like a single character column, and a character column containing only nulls looks numeric. For those, the adapter accepts an explicit format for the column - and Data Controller sends one automatically, because it already knows each column's SAS format from the metadata returned by the backend.
• A value that is neither a number nor a single valid character is refused rather than guessed - aaaa, or ! in a numeric column.
• A lone . is accepted as another way of typing the regular missing.

There is nothing to configure. Special missings are available by default, for numeric cells - a date, datetime or time formatted column aside, since those edit through a date picker.

Once in SAS they are ordinary values, so they are what the approval DIFF screen compares, and the DIFF's formatted / unformatted switch shows either the formatted representation or the raw value - useful for confirming exactly which missing was set.

What the Data Controller validation rules do with them

These are Data Controller's own rules, configured per column in the MPE_VALIDATIONS table and applied in the browser as you edit and submit.

• NOTNULL - rejects one. A special missing is a missing value, so it fails the rule, and a physical NOT NULL constraint on the target table rejects it as well. A primary key column is treated as NOT NULL whether or not a rule is configured for it
• HARDREGEX - checked against the pattern like any other value; unlike blanks and the plain ., special missings are not exempt, so a numeric column that carries them needs a pattern which allows for a single letter
• SOFTREGEX - the same check, but a failure is only a warning rather than a block, and it is ignored entirely if the column also has a HARDREGEX rule
• SOFTSELECT / HARDSELECT - both support them. The dropdown lists a special missing as a bare letter alongside the ordinary values, and a hard rule then accepts it like any other listed value - it still rejects a value that is not in the list
• ROUND - no effect. It only rounds values that are numbers, so a special missing is left as it was typed

The range rules compare in the order SAS itself uses, which is what lets a range be written in special missings. SAS puts every missing below every non-missing value, and orders the missing values among themselves: ._ is the lowest, then the regular missing, then .A through .Z. A range therefore means exactly what SAS would mean by it:

• MINVAL .A with MAXVAL .C accepts .B and rejects .D, and a blank - the regular missing - fails that floor because it sorts below .A
• a number sits above every missing, so it passes a floor of .A and fails a ceiling of .C
• against a numeric bound the ordering does the obvious thing: a missing sorts below every number, so it fails a MINVAL of 1 and passes a MAXVAL of 100. Use NOTNULL if the column has to be populated
• a rule value that is neither a number nor a special missing - a typo such as .. or AB - satisfies nothing, so the column fails until the rule is corrected

A formula is the one case where a special missing genuinely does not work:

• HARDFORMULA / SOFTFORMULA - a formula that reads a special-missing cell does not compute; the grid's spreadsheet engine returns #VALUE! for that row, where the plain . contributes 0

CASE (UPCASE / LOWCASE) is a character rule, so it does not apply: SAS hands the browser a special missing as an uppercase letter, so there is no case to enforce, and a CASE rule on a numeric column would reject the column's numbers rather than the missing.

Worth knowing about the regex rules: although the pattern is written in SAS PRX syntax, the check itself runs in the browser - SAS only parses the pattern (PRXPARSE) when the rule is saved - so the two engines can disagree on exotic patterns, and SAS pads a numeric-to-character conversion, so a pattern re-used in SAS needs strip() for an anchored match.

In short, a special missing counts as a value for the pattern and dropdown rules, and takes its own place in the order for the range rules: it sits below every number, so a numeric minimum rejects it and a numeric maximum accepts it, while a range written in special missings is decided among the missing values themselves.

See it in action

The recording below runs the whole cycle on one table: entering special missings, the rules that reject them, submitting the changes, approving them, and reviewing the DIFF - including a change from one special missing to another, and the formatted / unformatted switch on a date column.

It also shows the range rules doing what the section above describes. A special missing is refused by a numeric MINVAL and accepted by a numeric MAXVAL, and on a column carrying MINVAL .A with MAXVAL .C, .B is taken while .D is refused.