Merge pull request 'docs(validations): document how each rule treats a special missing value' (#8) from docs/special-missing-rule-behaviour into main

Reviewed-on: #8
Reviewed-by: Allan <allan@4gl.io>

docs/dcc-validations.md

@@ -23,17 +23,17 @@ It is possible to configure a number of other rules by updating the MPE_VALIDATI
 
 ## Configurable Checks
 
-Check back frequently as we keep growing this list of checks.
+Check back frequently as we keep growing this list of checks.  For how each rule treats a special missing value (`.A`-`.Z`, `._`), see [Special Missing Values](#special-missing-values).
 
 |Rule Type|Example Value |Description|
 |---|---|---|
 |CASE|UPCASE|Will enforce the case of cell values.  Valid values: UPCASE, LOWCASE, PROPCASE|
-|NOTNULL|(defaultval)|Will prevent submission if null values are present.  Optional - provide a default value.|
-|MINVAL|1|Defines a minimum value for a numeric cell|
-|MAXVAL|1000000|Defines a maximum value for a numeric cell|
+|NOTNULL|(defaultval)|Will prevent submission if null values are present - a special missing counts as null.  Optional - provide a default value.|
+|MINVAL|1|Defines a minimum value for a numeric cell.  A special missing value sorts below every number, so it fails a numeric floor, and the floor itself may be a special missing - see [Special Missing Values](#special-missing-values).|
+|MAXVAL|1000000|Defines a maximum value for a numeric cell.  A special missing value sorts below every number, so it passes a numeric ceiling, and the ceiling itself may be a special missing - see [Special Missing Values](#special-missing-values).|
 |READONLY|(defaultval) |Renders the column read-only in the editor.  The defaultval is used when rows are added. |
 |HIDDEN|(defaultval) |Hides the column from the editor grid while still submitting its data. The defaultval is used when rows are added. |
-|ROUND|2 |Rounds numeric input on paste/edit. Positive digits round to number of decimal places (eg 2 rounds to 0.01) Negative digits round to the nearest ten/hundred etc.  Half-away-from-zero rounding is applied so `-0.5 → -1` and `2.5 → 3`. |
+|ROUND|2 |Rounds numeric input on paste/edit. Positive digits round to number of decimal places (eg 2 rounds to 0.01) Negative digits round to the nearest ten/hundred etc.  Half-away-from-zero rounding is applied so `-0.5 -> -1` and `2.5 -> 3`. |
 |NUMBER_FORMAT|`{"style":"currency","currency":"GBP"}` |Display-only [`Intl.NumberFormat` renderer](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Intl/NumberFormat).  RULE_VALUE is the JSON options object passed straight to `Intl.NumberFormat`.  Does not change the stored value. |
 |HARDFORMULA|`= PRICE * VOLUME`|The cell displays a value computed from a formula, using other columns in the same row.  The column is read-only - the user cannot override the result.  See [Formula Rules](#formula-rules) below.|
 |SOFTFORMULA|`= if( DC.ROW_STATUS != 'U', DC.USER_NAME, DC.ORIG_VALUE )`|Like HARDFORMULA, but the user can override the computed value and type their own.  See [Formula Rules](#formula-rules) below.|
@@ -45,6 +45,34 @@ Check back frequently as we keep growing this list of checks.
 |[SOFTSELECT_HOOK](/dynamic-cell-dropdown)|/physical/path/program.sas|A SAS service (STP or Viya Job) or a path to a SAS program on the filesystem.  User-provided values may (or may not) be in this list.  Cannot be used alongside a HARDSELECT_HOOK.|
 
 
+## Special Missing Values
+
+A SAS numeric variable has 28 missing values: the regular `.` and 27 special ones (`.A`-`.Z` and `._`).  Data Controller accepts them in a numeric cell as a letter - `a` to `z` - or an underscore, with or without a leading period (`a`, `.a`, `_`, `._`), and stores the matching special missing in SAS.  The period is optional, and a plain `.` stores the regular missing.
+
+A numeric column that carries a date, datetime or time format is the exception.  Those cells are edited through a date picker rather than the numeric editor, and the picker accepts only a date - so a special missing cannot be typed into one of them.
+
+The rules treat a special missing as a value for some checks and as a missing value for others - and the range rules compare it in the order SAS gives it, below every number but ordered among the other missing values:
+
+|Rule|What happens to a special missing|
+|---|---|
+|NOTNULL|Fails.  A special missing is a missing value, so a NOT NULL (or primary key) constraint rejects it, and a real SAS NOT NULL constraint on the target table rejects it too.|
+|MINVAL|Fails if the minimum is a number - a missing sorts below every number, so it is below any numeric floor.  A minimum that is itself a special missing is compared in the order the missing values have among themselves, and can accept one.|
+|MAXVAL|Passes - a missing sorts below every number, so it is below any numeric ceiling.  A ceiling that is itself a special missing is compared in the missing values' own order, and can reject one.|
+|CASE|Not applicable - it is a character rule.|
+|ROUND|Ignored - ROUND only rounds values that are numbers, so a special missing is left as typed.|
+|HARDREGEX / SOFTREGEX|Checked against the pattern like any other value.  Unlike blanks and the plain `.`, special missings are **not** exempt.  See [Regex Rules](#regex-rules).|
+|SOFTSELECT|Supports special missings - a soft dropdown never blocks a value.|
+|HARDSELECT|Supports special missings - the value is matched against the dropdown list like any other value.|
+|HARDFORMULA / SOFTFORMULA|The formula returns `#VALUE!` for that row instead of a number, and that is what gets submitted.|
+
+In short, a special missing is rejected by NOTNULL, by a primary key column, and by a MINVAL whose floor is a number; a formula returns `#VALUE!` for it.  It is fine in a column that carries a MAXVAL, a regex, a dropdown, or ROUND - and it can satisfy a MINVAL when the floor is expressed as a special missing.
+
+A range rule compares in the order SAS itself uses, so a range written in special missings means what SAS would mean by it.  That order puts every missing below every non-missing value, and orders the missing values as `._`, then the regular missing, then `.A` through `.Z`.  So `MINVAL .A` with `MAXVAL .C` accepts `.B` and rejects `.D`, and a blank - the regular missing - fails a floor of `.A` because it sorts below it.  A number sorts above every missing, so it passes a floor of `.A` and fails a ceiling of `.C`.
+
+A primary key column is treated as NOT NULL whether or not the target table carries a physical constraint, and whether or not MPE_VALIDATIONS has a NOTNULL rule for it - a key identifies the row, so a blank and a special missing are both rejected there.
+
+A range rule's own value is read as a number when it looks like one and as a missing value when it is a special missing.  A value that is neither - a typo such as `..` or `AB` - satisfies nothing, so every cell in the column fails until the rule is corrected.
+
 ## Formula Rules
 
 HARDFORMULA and SOFTFORMULA let you configure a column so that its value is automatically calculated from other columns in the same row - just like a spreadsheet formula.  When a user opens the editor, the formula is evaluated live and the result is shown in each cell.

docs/dci-deploysas9.md

@@ -31,12 +31,12 @@ If you don't have internet access from SAS, download `sas9.sas` from [here](http
 
 #### 2 - Deploy the Frontend
 
-The Data Controller frontend comes pre-built, and ready to deploy to the root of the SAS Web Server (mid-tier).
+The Data Controller frontend comes pre-built, and ready to deploy to the SAS Web Server (mid-tier).
 
 Deploy as follows:
 
 1.  Download the `frontend.zip` file from: [https://git.datacontroller.io/dc/dc/releases](https://git.datacontroller.io/dc/dc/releases)
-2. Unzip and place in the [htdocs folder of your SAS Web Server](https://sasjs.io/frontend-deployment/#sas9-deploy) - typically a subdirectory of: `!SASCONFIG/LevX/Web/WebServer/htdocs`.
+2. Create a folder for Data Controller in the [htdocs folder of your SAS Web Server](https://sasjs.io/frontend-deployment/#sas9-deploy) - typically a subdirectory of: `!SASCONFIG/LevX/Web/WebServer/htdocs` - and unzip the archive **into** that folder. The archive holds the frontend files at its root, so `index.html` sits directly inside your folder (alongside the `images` folder and the compiled asset files) - for example `htdocs/dc/index.html`.
 3. Open the `index.html` file and update the values in the `<sasjs>` tag as follows:
 
   * `appLoc` - same as per SAS code in the section above
@@ -47,7 +47,7 @@ Deploy as follows:
 
 The remaining properties are not relevant for a SAS 9 deployment and can be **safely ignored**.
 
-You can now open the app at `https://YOURWEBSERVER/unzippedfoldername`  (step 2 above) and follow the configuration steps (DC Physical Location and Admin Group) to complete deployment.
+You can now open the app at `https://YOURWEBSERVER/<yourfoldername>`  (the folder created in step 2 above) and follow the configuration steps (DC Physical Location and Admin Group) to complete deployment.
 
 #### 3 - Run the Configurator
 

docs/dci-troubleshooting.md

@@ -108,6 +108,34 @@ This can happen if you enter the wrong `serverName` when deploying the SAS progr
 
 The error may also be thrown due to an encoding issue - changing to a UTF-8 server has helped at least one customer.
 
+## Displayed timestamps are in UTC (or the wrong timezone)
+
+Data Controller records timestamps (such as the SUBMITTED column on the Submitted screen, and the audit history) using the SAS session clock, via the `datetime()` function. That function returns the time of the operating system, adjusted by the [TIMEZONE= system option](https://documentation.sas.com/doc/en/pgmsascdc/default/lesysoptsref/p15siqs0s00e50n1wuuvygzkr14r.htm) if it is set. The value is then displayed in the frontend exactly as stored, without conversion.
+
+On Viya, the SAS compute sessions that run Data Controller jobs are started inside Kubernetes containers whose clock is UTC by default, and which inherit no timezone from the host machine. If the TIMEZONE= option is not set for the compute context used by Data Controller, every timestamp DC records and displays is UTC. Other SAS products can appear unaffected because clients such as SAS Studio create their own compute sessions and pass the browser timezone / locale, whereas Data Controller submits jobs to its own shared compute context, which gets the raw container clock.
+
+To confirm the current state, run the following in a SAS session under the Data Controller compute context (or check the DC job log, where `_DEBUG` output shows the same values):
+
+```sas
+proc options option=timezone;
+run;
+%put &=SYSTIMEZONEIDENT &=SYSTIMEZONEOFFSET;
+```
+
+If TIMEZONE is blank (and SYSTIMEZONEOFFSET is 0), the session is on UTC. To fix it, ask your Viya administrator to set the timezone for the compute context used by Data Controller (in SAS Environment Manager, edit the context's Autoexec, or set the context's SAS options):
+
+```sas
+options timezone='Europe/Berlin';
+```
+
+Then recycle any existing compute sessions - hot sessions keep the old setting until they terminate.
+
+!!! note
+    Use a region/area time zone ID such as `Europe/Berlin` rather than a fixed offset such as `GMT+2`. Fixed offsets do not follow daylight saving time, so the display would be 1 hour off in winter (CET = UTC+1).
+
+!!! note
+    Changing the timezone affects new timestamps only. Previously recorded submissions keep the UTC values that were stored when they were created.
+
 ## Determining Application Version
 
 The app version is bundled into the frontend during the release, and is visible by clicking your username in the top right.

docs/dcu-tableviewer.md

@@ -16,6 +16,18 @@ Row and Column level security can also be applied in VIEW mode, as can additiona
 
 A single search box can be used to make a full table search on any character or numeric value, using this [macro](https://core.sasjs.io/mp__searchdata_8sas.html).
 
+The search covers **every** column of the table at once, so you do not need to know which column holds the value you are looking for:
+
+- **Character search** (the default) is a case sensitive CONTAINS match against every character column, so `Smith` finds `Smithson` and `smith` finds `Goldsmith`, but `smith` will not find `Smithson`.
+- **Numeric search** (tick the Numeric box) is an exact match against every numeric column, so `42` will not find `420` or `4210`.
+- If a filter is applied, the search runs within the filtered rows rather than the whole table.
+- The grid is capped by the [`DC_MAXOBS_WEBVIEW`](/dcc-options/#dc_maxobs_webview) option (500 rows by default).
+- For a search, the cap applies to the row count as well: a search that matches more rows than the cap returns 500 of them, and the count shown next to the table name is the number of rows returned, not the total number of matches.
+- Without a search, the count shown next to the table name is the full number of rows in the filtered view, so it can be higher than the number of rows displayed - a table of 1,200 rows reports `1,200 rows` above a grid holding the first 500.
+- A search that matches nothing shows a "No data found with given conditions" panel rather than an empty grid.
+
+![Full table search](img/full-table-search.png)
+
 <iframe width="560" height="315" src="https://www.youtube.com/embed/i27w-xq85WQ" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture" allowfullscreen></iframe>
 
 ## Options