RIF Data Loading

1. Overview
2. RIF Capabilities
3. Limitations
   1. Data Loader Tool
4. RIF Data Loading Prerequisites
   1. Postgres
   2. SQL Server
      1. BCP Access Permissions
      2. BCP Format Issues
   3. Data Structure
      1. Numerator
      2. Denominator
      3. Covariates
      4. Administrative Geography
         1. Geography
         2. Geolevels
         3. Lookup tables
         4. Tile tables
         5. Adjacency tables
         6. Hierarchy tables
         7. Geometry tables
         8. Shapefile, shapefile tables
         9. Centroids tables
      5. Health Themes
5. Load Processing
   1. Generate Series
   2. Administrative Geography
   3. Postgres
   4. SQL Server
   5. Numerator
   6. Pre Processing
   7. Load Processing
   8. Denominator
   9. Pre Processing
   10. Load Processing
   11. Covariates
   12. Pre Processing
   13. Load Processing
6. Information Governance
7. Flexible Configuration Support
   1. Age Groups
   2. ICD field Name
   3. Automatic Numerator Denominator Pairs
8. Quality Control
   1. Extract Warnings
   2. Numerator Denominator Pair Errors
   3. Check that the RIF can deduce the outcome columns for the numerator tables
9. Remote Data Links
   1. SQL Server to Oracle
   2. Postgres to Oracle
      1. Setting up
      2. Diagnostics
      3. HES Example

Overview

This document details the manual process for the loading of data into the RIF. The RIF requires the following types of data:

• Numerator data. This may be in individual record form or aggregated to a suitable administrative geography. Individual records are assumed to have been de-identified into a suitable pseudonymous form; all this is not a requirement.
• Denominator data. This is in aggregate form.
• Covariate data. This is again in aggregate form and has been quantilised
• Administrative geography. This is created by the Tile-Maker tool; this document details the loading of the administrative geography data and the geo-coding requirements of the RIF. See the tile-maker manual for how to create RIF administrative geographies.

RIF data loading occurs in two distinct phases;

• Load pre processing. The end product are CSV files suitable for use either in a RIF load script or for use by the RIF data loader tool. An example is provided using the US SEER Cancer Registry data with example scripts for both Postgres and SQL Server.
• RIF loading processing. The end result here is a fully configured RIF with the data loaded. Typically users need to be granted access to the dataset to be able to use the data. Data without access is not visible to users in the RIF. There are two routes to carry out this this processing:
  • Via the RIF data loader tool. This is the automated process for loading data into the RIF. The data loader tool carries out additional load pre-processing and then generates data and scripts for both SQL Server and Postgres. The current data loader documentation is not in this document; but in: RIF Data Loader Manual. The data loader tools is still in development and is not expected to be complete until 2019.
  • Manually via a user created script. Again an example is provided using the US SEER Cancer Registry data with example scripts for both Postgres and SQL Server.

RIF Capabilities

The RIF supports:

• Multiple geographies, numerators, covariates
• Complex condition logic
• User customizable age groups

In addition to the limitations detailed below which will be removed in future releases the following capabilities may potentially be added in future releases:

• Multiple investigations
• Temporal analysis (run by year/groups of years)
• Covariates without years
• Study age bands, e.g. 0-19,20-29,60-74,75+

Limitations

Limitations in the current RIF:

• Health and geographic data must be stored in the rif_data schema. SQL Server does not have the concepts of either a search path or synonyms so the schema name has been hard coded. Postgres can use the schema search path however the middleware has not been tested to use other schemas for the data;
• No support for covariates embedded in numerator or denominator data. Data must be extracted into a separate covariate table. Covariates must be merged into a single table and disaggregated (if required) by year. It is not planned to remove these restrictions which were in the previous RIF;
• Covariates must be quantilised. Support for continuous variable covariates (i.e. on the fly quantilisation) may be added in future releases.
• Denominator data is always used in indirect standardisation. There is no support currently in the RIF for direct standardisation using standard populations. This was supported in previous versions of the RIF and will be put back if required;
• No support for ad-hoc SQL. This functionality will be partially re-implemented in future releases using user specified conditions (pre defined groups). The feature was removed as it cannot be implemented in a secure manner (i.e. it permits SQL injection attacks);

• Numerators are currently limited to ICD 9 and 10 coding only. Support will be added for ICD 11 (subject to the release of the 11th Edition in June 2018)

  In the longer term it is expected that support will be added for:

  • ICD oncology O(ICD-O-1)
  • UK HES oper and A+E codes
  • User specified conditions (pre defined groups), e.g. Low birthweight, complex groups of ICD codes, the all record condition (the 1=1 ad-hoc SQL filter in the previous RIF)
• Single ICD field name. Unlike the old RIF the field name is configurable;
• The RIF currently lacks complex support for Information Governance beyond having strong role based permissions.
• No support for population weighted centroids.

It is planned to remove the following restrictions progressively in future releases:

• One investigation only;
• Single ICD field names. Multiple field name support is in the database with partial support in the database;
• Covariate study_geolevel_name must be the same as rif40_studies.study_geolevel_name, i.e. covariate geolevel cannot be of lower resolution than study geolevel;
• Disease mapping only. Risk analysis will be added during summer 2018;
• AGE_SEX_GROUP column only (i.e. no separate AGE_GROUP/AGE/SEX columns);
• Support for more than current and previous version of a table outcome (e.g. ICD). This would allow ICD 9, 10 and 11 (or 8, 9 and 10) to be supported all at the same time together the the start and end year for each version in a numerator table. Currently the RIF applies the same ICD filter to all years. This approach may cause problems if there are coding incompatibilities between the version (i.e. the same code means something different in two or more version).
• Population weighted centroids; also add the ability to import population weighted or spatially processed (centroid pulled to within the centroid boundary) centroids into an administrative geography.

Like the old RIF there are field naming restrictions:

• AGE_SEX_GROUP. Use of AGE and SEX is not permitted; the column must be called AGE_SEX_GROUP;
• YEAR

THe ICD field name is now configurable. The default is ICD_SAHSU_01.

Data Loader Tool

The data loader tool in its current prototype form is severely limited in its ability to transform data by:

• Aggregation of data. The data loader tool requires aggregate data
• Re-gecoding data
• Performing complex cleaning and remapping of data (e.g. choosing between provisional and final causes of death)

The following are known issues with the RIF data loader.

• The properties file RIFDataLoaderToolStartupProperties.properties used is the hard coded one at compile time. Needs to be set to read this file from the current directory. This means the source has to be edited to change the database type and connection settings. The current default is localhost and Postgres.
• Numeric total fields to be supported
• Numerators without a total field needs to be supported; exception is not handled correctly. Data loader tool only supports aggregate data.

It is envisaged that the data loader will become a browser based tool like the RIF of the RIF tool chain.

The current data loader documentation is in the RIF Data Loader Manual

RIF Data Loading Prerequisites

The following steps need to be carried out to load process the data:

• Separation of data into numerator, denominator and covariates.
• Collation of covariates into a single covariate source
• Geo-coding of data by joining to the administrative dataset.

The following are restrictions on the naming of columns:

• The names of the geolevels must match exactly and all geolevels must must be present in numerator and denominator data. For covariates geolevel names the name must match the geolevel the covariate is for.
• All names must be in lower case, up to 30 characters long, start with a lower case letter, and only contain lower case letters, digits (0-9) and underscore. This is a common database restriction. The 30 character limit comes from the earlier Oracle versions of the RIF.

Postgres

Postgres uses the \copy command to load and unload data. \copy cannot handle fixed length data; this is loaded as a fixed length string and parsed using SQL.

The SEER data load requires USA load phase data pg_USQ_2014.sql to be loaded as a RIF user (not rif40) and the production data (rif_pg_usa_2014.sql) needs to be loaded into the rif40 account in the rif_data schema.

SQL Server

BCP Access Permissions

SQL Server needs access permission granted to the directories used to BULK INSERT files, the files are not copied from the client to the server as in the Postgres psql ```\copy` command and the Oracle sqlldr command. This also implies that a full file path is required and the file name must be accessible on the SQL Server server; it does NOT have to be accessible on the client.

SQL Server needs access to these directories. The simplest way is to allow read/execute access to the local users group (e.g. PH-LAPTOP\Users or USERS depending on your Windows version).

DO NOT TRY TO RUN BULK INSERT FROM NETWORK DRIVES or CLOUD DRIVES (e.g. Google Drive). Use a local directory which SQL Server has access to; e.g. somewhere on the C: drive. Note that SQL Server BULK LOAD behaves deterrently if you logon using Windows authentication (where it will use your credentials to access the files) to using a username and password (where it will use the Server’s credentials to access the file).

BULK INSERT rif_data.lookup_sahsu_grd_level1
FROM 'C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\..\GeospatialData\tileMaker/mssql_lookup_sahsu_grd_level1.csv'     -- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
WITH
(
        FORMATFILE = 'C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\..\GeospatialData\tileMaker/mssql_lookup_sahsu_grd_level1.fmt',            -- Use a format file
        TABLOCK                                 -- Table lock
);

Msg 4861, Level 16, State 1, Server PH-LAPTOP\SQLEXPRESS, Line 7
Cannot bulk load because the file "C:\Users\Peter\Documents\GitHub\rapidInquiryFacility\rifDatabase\SQLserver\installation\..\..\GeospatialData\tileMaker/mssql_lookup_sahsu_grd_level1.csv" could not be opened. Operating system error code 5(Access is denied.).

BCP Format Issues

BULK LOAD and BCP cannot handle:

• Text fields with a mixture of quote enclosing and no quote enclosing;
• Exponential notation e.g. 3.12E-5
• A database of SQLDECIMAL with no PRECISION and SCALE specified will convert the input to an INTEGER transparently! Make sure to use:

   <COLUMN SOURCE="4" NAME="population" xsi:type="SQLDECIMAL" PRECISION="7" SCALE="2" />```
  

Data Structure

All RIF data tables are located in the rif_data schema. They must be located in rif_data because SQL Server does not have the concept of a schema search path; the search paths have been hard coded. Tables may be located in any tablespace and you may use views which again must be located in the rif_data schema.

The RIF also support views for numerator and denominator tables. This allows for considerable flexibility in configuration as:

• Tables can be UNIONed together;
• Views can aggregate and join data;
• Field can be renamed and data types coerced;
• Tables can be located in different schemas;
• Impose Information Governance restrictions

All table and column names must be a valid [Oracle] database name - 30 characters, uppercase, A-Z, 0-9 and underscore (_) and start with a letter. In some cases the length is reduced to 20 characters so that derived names of indexes, primary and foreign keys are under the 30 character limit.

The middleware translates these names into the correct format for the database software port. Names must NOT have schemas appended.

Numerator

The example numerator table is num_sahsuland_cancer:

The num_sahsuland_cancer table is aggregated to the highest geographic resolution. The table can disaggregated to individual level data (e.g. SAHSU cancer incidence data). Numerator table must have the following fields:

• YEAR. This must be an integer;
• AGE_SEX_GROUP. This contains an age sex group field name, e.g. M5_9. See: Age groups;
• <outcome groups field name> e.g. icd for the OUTCOME_GROUP_NAME SAHSULAND_ICD. See: ICD field name;
• <one geolevel field name for each geography geolevel> e.g. sahsu_grd_level1. See: Administrative geography;
• <total field name> e.g. total. If this is null, the <outcome groups field name> e.g. icd is COUNTed (i.e. the table is disaggregated). If not null, the <total field name> is summed.

To add a numerator table to the RIF if must be added to:

• rif40_tables:

  column_name	description	data_type
  theme	Health Study theme. Link to RIF40_HEALTH_STUDY_THEMES. See: Health theme;	varchar(30)
  table_name	RIF table name. Normally the schema owner will not be able to see the health data tables, so no error is raised if the table cannot be resolved to an acceisble object. The schema owner must have access to automatic indirect standardisation denominators.	varchar(30)
  description	Description	varchar(250)
  year_start	Year table starts	smallint
  year_stop	Year table stops	smallint
  total_field	Total field (when used aggregated tables)	varchar(30)
  isindirectdenominator	Is table a denominator to be used in indirect standardisation (0/1). Must ALWAYS be 1 for denominators.	smallint
  isdirectdenominator	Is table a denominator to be used in direct standardisation (0/1). E.g. POP_WORLD, POP_EUROPE. Must ALWAYS be 0.	smallint
  isnumerator	Is table a numerator (0/1)	smallint
  automatic	Able to be used in automatic RIF40_NUM_DENOM (0/1, default 0). Cannot be applied to a direct standardisation denominator. Restricted to 1 denominator per geography to prevent the automatic RIF40_NUM_DENOM having >1 pair per numerator. This restriction is actually enforced in RIF40_NUM_DENOM because of the "ORA-04091: table RIF40.RIF40_TABLES is mutating, trigger/function may not see it" error. A user specific T_RIF40_NUM_DENOM is supplied for other combinations. The default is 0 because of the restrictions	smallint
  sex_field_name	Name of SEX field. No default. AGE_GROUP_FIELD_NAME must be set, AGE_SEX_GROUP_FIELD_NAME must not be set. Not currently used.	varchar(30)
  age_group_field_name	Name of AGE_GROUP field. No default. SEX_FIELD_NAME must be set, AGE_SEX_GROUP_FIELD_NAME must not be set. Not currently used.	varchar(30)
  age_sex_group_field_name	Name of AGE_SEX_GROUP field. Default: AGE_SEX_GROUP; AGE_GROUP_FIELD_NAME and SEX_FIELD_NAME must not be set.	varchar(30)
  age_group_id	Type of RIF age group in use. Link to RIF40_AGE_GROUP_NAMES. No default. See: Age groups;	smallint
  validation_date	Date table contents were validated OK. Availabel for use by quality control programs	timestamp without time zone

  Example:

  theme	table_name	description	year_start	year_stop	total_field	isindirectdenominator	isdirectdenominator	isnumerator	automatic	age_sex_group_field_name	age_group_id
  cancers	NUM_SAHSULAND_CANCER	cancer numerator	1989	2016	TOTAL	0	0	1	1	AGE_SEX_GROUP	1

  So, for the example numerator table num_sahsuland_cancer:

  • The theme is a reference to RIF40_HEALTH_STUDY_THEMES.THEME. See: Health theme;
  • The table name MUST be in upper case. Do not add the schema owner;
  • Year stop and start refer to the first and last years of numerator data;
  • The total field, if not null, MUST be in upper case;
  • isindirectdenominator, isdirectdenominator are “0”; isnumerator is “1”;
  • automatic is normally “1” unless you have >1 denominator for the geography when it is “0”. See: Automatic numerator denominator pairs;
  • age_sex_group_field_name MUST be AGE_SEX_GROUP;
  • The age_group_id refers to rif40.rif40_age_group_names. The standard 21 age groups used by SAHSU is “1”. See: Age groups;

• *rif40.rif40_table_outcomes**: see: ICD field name;

Denominator

The example denominator table is pop_sahsuand_pop:

The pop_sahsuand_pop table is aggregated to the highest geographic resolution. The table CANNOT disaggregated to individual level data or rotated with AGE_SEX_GROUP as multiple columns. Denominator table must have the following fields:

• YEAR. This must be an integer;
• AGE_SEX_GROUP. This contains an age sex group field name, e.g. M5_9. See: Age groups;
• <one geolevel field name for each geography geolevel> e.g. sahsu_grd_level1. See: Administrative geography;
• <total field name> e.g. total. This field is summed.

To add a denominator table to the RIF if must be added to:

• rif40_tables:

So, for the example denominator table pop_sahsuand_pop:

• The theme is a reference to RIF40_HEALTH_STUDY_THEMES.THEME. See: Health theme;
• The table name MUST be in upper case. Do not add the schema owner;
• Year stop and start refer to the first and last years of denominator data;
• The total field, if not null, MUST be in upper case;
• isindirectdenominator is “1”, isdirectdenominator and isnumerator are “0”;
• automatic is normally “1” unless you have >1 denominator for the geography when it is “0”. See: Automatic numerator denominator pairs;
• age_sex_group_field_name MUST be AGE_SEX_GROUP;
• The age_group_id refers to rif40.rif40_age_group_names. The standard 21 age groups used by SAHSU is “1”. See: Age groups;

Covariates

The covariate tables with one table per geolevel for additional covariates within a geography (e.g. social exclusion scores). The table is defined in rif40_geolevels.covariate_table.

Covariate tables must contain the following fields (as in the covar_sahsuland_covariates3 example below):

• Year this should cover the range of the associated denominator and must be an integer;
• <geolevel field name> e.g. sahsu_grd_level4. See: Administrative geography;
• One or more <covariate name> fields e.g. ses. These can be either an integer or a numeric. Currently only quantilised fields are supported; continuous variables are not supported.

Unlike other administrative geography tables; RIF managers MUST create these tables for themselves. A full range of all possible values MUST be provided, even if the covariate data is null. So in the example below

As an example the covariate table covar_sahsuland_covariates3 contains (with rows truncated):

Administrative Geography

RIF administrative geography has nine components:

• Geography: name of the administrative geography;
• Geolevels: a hierarchy of administrative areas that define a geography where each higher resolution contains one or more areas that fit exactly within the lower resolution;
• Lookup tables: for the names of the administrative area id codes within a geolevel;
• Tile tables: the WGS84 topoJSON and geoJSON tiles for a geography used by the RIF front end and extract utilities to display administrative geography geolevels;
• Adjacency tables: a list of adjacent areas for each geolevel and area id in a geography used in BAtesian smoothing;
• Hierarchy tables: one table per administrative geography; contains a hierarchy of higher resolution of one or more areas that fit exactly within the lower resolution

The following components are used by the administrative geography preprocessing (tile maker):

• Geometry tables: geometric data for a geography to allow the database to perform spatial queries with the administrative geometry;
• Shapefile, shapefile tables: Storage for the original shapefile data and name used as part of the administrative geography preprocessing (tile maker);

The final component is in the TODO list for future additions:

• Centroids tables: tables of centroids tables. Used to import population weighted or spatially processed (centroid pulled to within the centroid boundary) centroids into an administrative geography;

All components are created by the administrative geography preprocessing (tile maker) and loaded by a SQL Server sqlcmd or Postgres psql script.

Geography

The table rif40.rif40_geographies is used to define one or more administrative geographies

• Currently partitioning is not enabled on SQL Server due to licensing restrictions and on Postgres use is limited to the geometry table. Postgres partitioning uses the version 9 and before inheritance method. Partitioning is being progressively implemented in Postgres 10;
• postal_population_table and postal_point_column are for using postcodes or ZIP codes as the centre of a risk analysis circle and are not currently used.

The supplied SAHUSLAND test database is set up as follows:

The example SEER test database is set up as follows:

Geolevels

The table and view rif40.t_rif40_geolevels/rif40.rif40_geolevels is used to define a hierarchy of administrative areas that define a geography where each higher resolution contains one or more areas that fit exactly within the lower resolution. Users with the rif_student role cannot see the restricted geolevels.

• centroids_table, centroids_area_id_column, centroidxcoordinate_column and centroidycoordinate_column are used to import population weighted or spatially processed (centroid pulled to within the centroid boundary) centroids into an administrative geography. This is not currently supported;
• avg_npoints_geom, avg_npoints_opt, file_geojson_len, leg_geom and leg_opt are used as part of the administrative geography preprocessing (tile maker);

• Normally resolution, comparea and listing are all set to “1”. Setting to “0” restricts the RIF front end as follows:

  • resolution: cannot use the map for selection at this resolution for either study or comparison area;
  • comparea:cannot be to be used as a comparison area;
  • listing: cannot be in a list of areas when setting up a disease map study or comparison area.

The supplied SAHUSLAND test database is set up as follows:

The field geolevel_id is used to order the geolevels by resolution. The highest geolevel_id is the highest resolution (i.e. has the most areas).

The example SEER test database is set up as follows:

Lookup tables

Lookup tables contain the names of the administrative area id codes within a geolevel and must have:

• The area id column relevant to the geolevel. It must be named the same as the &lt’geolevel_name>;
• A unique gid field;
• A descriptive field defined in rif40_geolevels.lookup_desc_column.
• A geographic centroid in GEoJSON format, SGS84 geometry;

Lookup table lookup_sahsu_grd_level2;

As an example the lookup table lookup_sahsu_grd_level2 contains:

And for USA states:

Tile tables

The tile tables and views contain WGS84 topoJSON and geoJSON tiles for a geography used by the RIF front end and extract utilities to display administrative geography geolevels The view is defined in rif40_geolevels.tiletable and adds valid empty tiles for the whole planet. The view uses the table which only contains tiles with data. The view is efficient.

Tiles tables must contain the following fields (t_tiles_sahsuland example):

Example view SQL for SAHSULAND:

CREATE OR REPLACE VIEW rif_data.tiles_sahsuland
AS
WITH a AS (
         SELECT t_rif40_geolevels.geography,
                MAX(t_rif40_geolevels.geolevel_id) AS max_geolevel_id
           FROM t_rif40_geolevels
          WHERE t_rif40_geolevels.geography::text = 'SAHSULAND'::text
          GROUP BY t_rif40_geolevels.geography
), b AS (
         SELECT a.geography,
                generate_series(1, a.max_geolevel_id::integer, 1) AS geolevel_id
           FROM a
), c AS (
         SELECT b2.geolevel_name,
                b.geolevel_id,
                b.geography,
                b2.areaid_count
           FROM b,
                t_rif40_geolevels b2
          WHERE b.geolevel_id = b2.geolevel_id AND b.geography::text = b2.geography::text
), d AS (
         SELECT generate_series(0, 11, 1) AS zoomlevel
), ex AS (
         SELECT d.zoomlevel,
                generate_series(0,
						power(2::double precision, d.zoomlevel::double precision)::integer - 1,
						1) AS xy_series
           FROM d
), ey AS (
         SELECT c.geolevel_name,
                c.areaid_count,
                c.geolevel_id,
                c.geography,
                ex.zoomlevel,
                ex.xy_series
           FROM c,
                ex
)
SELECT z.geography,
       z.geolevel_id,
       z.geolevel_name,
       CASE
            WHEN h1.tile_id IS NULL AND h2.tile_id IS NULL THEN 1
            ELSE 0
       END AS no_area_ids,
       COALESCE(h1.tile_id,
			((((((((z.geolevel_id::character varying::text ||
				'_'::text) ||
				z.geolevel_name::text) ||
				'_'::text) ||
				z.zoomlevel::character varying::text) ||
				'_'::text) ||
				z.x::character varying::text) ||
				'_'::text) ||
				z.y::character varying::text)::character varying) AS tile_id,
       z.x,
       z.y,
       z.zoomlevel,
       COALESCE(h1.optimised_topojson,
			h2.optimised_topojson,
			'{"type": "FeatureCollection","features":[]}'::json) AS optimised_topojson
  FROM (
     SELECT ey.geolevel_name,
            ey.areaid_count,
            ey.geolevel_id,
            ey.geography,
            ex.zoomlevel,
            ex.xy_series AS x,
            ey.xy_series AS y
       FROM ey,
            ex
      WHERE ex.zoomlevel = ey.zoomlevel) z
     LEFT JOIN t_tiles_sahsuland h1 ON
			z.areaid_count > 1 AND
			z.zoomlevel = h1.zoomlevel AND
			z.x = h1.x AND z.y = h1.y AND
			z.geolevel_id = h1.geolevel_id
     LEFT JOIN t_tiles_sahsuland h2 ON
			z.areaid_count = 1 AND
			h2.zoomlevel = 0 AND
			h2.x = 0 AND
			h2.y = 0 AND
			h2.geolevel_id = 1;

Adjacency tables

The adjacency tables are a list of adjacent areas for each geolevel and area id in a geography used in BAtesian smoothing; The table is defined in rif40_geolevels.adjacencytable.

Adjacency tables must contain the following fields (adjacency_sahsuland example):

As an example the adjacency table adjacency_sahsuland contains (with rows truncated):

Hierarchy tables

Hierarchy tables have one table per administrative geography; contains a hierarchy of higher resolution of one or more areas that fit exactly within the lower resolution The table is defined in rif40_geographies.hierarchytable*.

Hierarchy tables must contain the following fields (as in the hierarchy_sahsuland example below):

• Area id columns relevant to each geolevel in the geography. They must be named the same as the &lt’geolevel_name>;

As an example the hierarchy table hierarchy_sahsuland contains (with rows truncated):

Geometry tables

The geometry tables contains geometric data for a geography to allow the database to perform spatial queries with the administrative geometry The table is defined in rif40_geolevels.geometrytable.

Geometry tables must contain the following fields (geometry_sahsuland example):

Geometry tables are partitioned on Postgres ports.

Shapefile, shapefile tables

The Shapefile, shapefile tables contain the names of the original shapefile and the data in geoJSON format. They are used as part of the administrative geography preprocessing (tile maker) The shapefile is defined in rif40_geolevels.shapefile. The table is defined in rif40_geolevels.shapefile_table.

Centroids tables

Centroids tables are used to import population weighted or spatially processed (centroid pulled to within the centroid boundary) centroids into an administrative geography. Centroids tables are not currently supported. The table is defined in rif40_geolevels.centroids_table. This is in the TODO list for future additions

Health Themes

The table rif40.rif40_health_study_themes is used to group numerators logically so the user is not presented with a long list of numerator/denominator pairs. Purpose could include:

• Management of datasets with annual reloads (e.g. split cancer in current cancer and archived cancer) so the user is not confused by large numbers of similarly named tables
• Management of datasets with large numbers of component parts (e.g. HES) and differing views onto the data
• Project specific datasets, e.g. incinerators, CO (Carbon monoxide poisoning)

An example theme is provided:

Load Processing

Generally load processing requires three steps:

• Create and load the Administrative geography. Always load the administrative geography first. This is produced by the Tile maker. See Example of Post Front End Processing
• Pre-process the data from flat files, process and unloaded back into flat files that can be loaded into either Postgres or SQL Server. Typically this is done as a normal user on any database. Do not use the schema rif40 or administrative accounts (postgres or administrator):
  • Create numerator/denominator/covariate load tables to load data from CSV or Text files. Fixed length (mainframe) record files have to be loaded as a single string and then chopped converted into the relevant fields in stage four;
  • Load numerator/denominator/covariate tables from CSV or Text files. SQL Server records will need a format specification Using a Format File to Bulk Import Data;
  • Check all numerator/denominator/covariate table data has been loaded;
  • Convert numerator/denominator/covariate fixed length string into new numerator/denominator/covariate load table with the correct columns and datatypes;
  • Create numerator/denominator/covariate table from load table. For denominator and covariate tables additional rows may need to be added to cope with holes in the data; e.g. re-use a later year of population or covariate data to replace missing earlier years. RIF covariates require annual data, if you do not have annual data you can use a view and the generate_series function to add the years; see: 3.0.1 Generate series;
  • Add constraints to numerator/denominator/covariate table;
  • Add indexes to numerator/denominator/covariate table;
  • Comment numerator/denominator/covariate table and columns;
  • Unload numerator/denominator/covariate table for load processing.

  With the covariates data there are normally two phases:

  • Loading and cleaning the input data;
  • Creating production covariate tables for unloading and then subsequent use in the production load process.
• Load the processed data as a schema owner (e.g. rif40)into a target database:
  • Remove setup data, views and tables;
  • Create table;
  • Create views;
  • Load CSV data into table;
  • Check all table data has been loaded;
  • Add indexes to table;
  • Comment table and columns;
  • Grant grant access on tables and views to an appropriate role;
  • Setup numerator and denominator tables and views.
    • Numerator and denominator tables are setup in rif40.rif40_tables;
    • The numerator ICD field needs to be defined in: rif40.rif40_outcome_groups and rif40.rif40_outcomes, see: ICD field name;
    • Covariate table names are predefined, see: Covariates; The RIF load scripts only require the administrative geography; and not the tilemaker pre-processing tables.

The following scripts are used in the examples:

To install, change to the <SEER Data directory, e.g. C:\Users\phamb\OneDrive\April 2018 deliverable for SAHSU\SEER Data\ >;

The SEER pre-processing script pg_load_seer_covariates.sql has a dependency on the cb_2014_us_nation_5m, cb_2014_us_state_500k and cb_2014_us_county_500l that are part of the tilemaker pre-processing. The FIPS code is required to make the join and this field is not in the standard lookup tables. For this reason it is necessary to build the covariates table on sahsuland_dev. In the longer term the FIPS codes should be added to the lookup tables.

SQL Server pre-processing scripts have not been created due to the lack of an unload facility in sqlcmd. There are two basic options to resolve this problem without recourse to C# or the many GUI wizards available:

• Use bcp
• Use sqlcmd SQL SERVER – Export Data AS CSV from Database Using SQLCMD

Other useful references:

• Bulk Import and Export of Data (SQL Server)
• 8 Ways to Export SQL Results To a Text File

To run a script:

• Postgres: psql -U rif40 -d <database name> -w -e -f <script name> Flags:
  • -U rif40: connect as user rif40
  • -d <database name>: connect to database <database name>
  • -w: never issue a password prompt. If the server requires password authentication and a password is not available by other means such as a .pgpass file, the connection attempt will fail;
  • -e: copy all SQL commands sent to the server to standard output as well;
  • -f <script name>: run SQL script <script name>

  For information on Postgres passwords

  e.g.

  psql -U rif40 -d sahsuland -w -e -f pg_rif40_load_seer.sql
  

  An an example see: Postgres data load;

• SQL Server: sqlcmd -U rif40 -P <password> -d <database name> -b -m-1 -e -r1 -i <script name> -v pwd="%cd%" Flags:
  • -U rif40: connect as user rif40;
  • -P <password>: the <password> for user rif40;
  • -d <database name>: connect to database <database name>;
  • -b: terminate batch job if there is an error;
  • -m-1: all messages including informational messages, are sent to stdout;
  • -e: echo input;
  • -r1: redirects the error message output to stderr;
  • -i <script name>: run SQL script <script name>;
  • -v pwd="%cd%": set script variable pwd to %cd% (current working directory). So bulk load can find the CSV files.

  By default on SQL server therif40 password is set to random characters, to change the SQL server rif40 password:

  ALTER LOGIN rif40 WITH PASSWORD = 'XXXXXXXX';
  GO
  

  e.g.

  sqlcmd -U rif40 -P rif40 -d sahsuland -b -m-1 -e -r1 -i ms_rif40_load_seer.sql -v pwd="%cd%"
  

  The production scripts rif_pg_usa_2014.sql and rif_mssql_usa_2014.sql require a role called SEER_USER. This needs to be created and then granted to each database user:

  psql -U postgres -d postgres
  CREATE ROLE seer_user;
  GRANT seer_user TO peter;
  

  sqlcmd -E
  USE sahsuland;
  IF DATABASE_PRINCIPAL_ID('seer_user') IS NULL
  	CREATE ROLE [seer_user];
  SELECT name, type_desc FROM sys.database_principals WHERE name LIKE '%seer_user%';
  ALTER ROLE [seer_user] ADD MEMBER [peter];
  GO
  

Once all the data has been loaded, check the table appears in a user (not rif40) rif40_num_denom

sahsuland=> select * from rif40_num_denom;
 geography |   numerator_table    |             numerator_description             |         theme_description         | denominator_table |                                 denominator_description                                  | automatic
-----------+----------------------+-----------------------------------------------+-----------------------------------+-------------------+------------------------------------------------------------------------------------------+-----------
 SAHSULAND | NUM_SAHSULAND_CANCER | cancer numerator                              | covering various types of cancers | POP_SAHSULAND_POP | population health file                                                                   |         1
 USA_2014  | SEER_CANCER          | SEER Cancer data 1973-2013. 9 States in total | covering various types of cancers | SEER_POPULATION   | SEER Population 1972-2013. Georgia starts in 1975, Washington in 1974. 9 States in total |         1
(2 rows)

If your data does not appear; see Numerator Denominator Pair Errors

Generate Series

The generate_series function is used in the RIF rif_data.tiles_sahsuland VIEW to add back tiles for which there is no data (i.e. outside of the extent of the geography so the tiles cover the entire world. It is also very useful for converting non annual covariates to the annual form required by the RIF; e.g:

SELECT generate_series(2011, 2018);
 generate_series
-----------------
            2011
            2012
            2013
            2014
            2015
            2016
            2017
            2018
(8 rows)

Using rif_data.covar_sahsuland_covariates3 year 2000 data only (i.e pretend it is not annualised!)

WITH a AS (
	SELECT generate_series(2011, 2018) AS year
)
SELECT a.year, b.sahsu_grd_level3, b.ses, b.ethnicity
  FROM a, covar_sahsuland_covariates3 b
 WHERE b.year = 2000 /* USe only one year! */
 ORDER BY 1, 2 LIMIT 20;
 year | sahsu_grd_level3 | ses | ethnicity
------+------------------+-----+-----------
 2011 | 01.001.000100    |   4 |         1
 2011 | 01.001.000200    |   5 |         1
 2011 | 01.001.000300    |   5 |         3
 2011 | 01.002.000300    |   2 |         2
 2011 | 01.002.000400    |   5 |         3
 2011 | 01.002.000500    |   5 |         3
 2011 | 01.002.000600    |   4 |         3
 2011 | 01.002.000700    |   5 |         3
 2011 | 01.002.000800    |   4 |         3
 2011 | 01.002.000900    |   1 |         2
 2011 | 01.002.001000    |   2 |         1
 2011 | 01.002.001100    |   3 |         3
 2011 | 01.002.001200    |   1 |         2
 2011 | 01.002.001300    |   2 |         3
 2011 | 01.002.001400    |   2 |         3
 2011 | 01.002.001500    |   1 |         2
 2011 | 01.002.001600    |   2 |         3
 2011 | 01.002.001700    |   1 |         1
 2011 | 01.002.001800    |   2 |         3
 2011 | 01.002.001900    |   1 |         1
(20 rows)

See Postgres set returning functions

The generate_series function is NOT part of SQL Server; however the following code provides this functionality. It is installed as a table valued function in the rif40 schema.

/*
 * SQL statement name: 	generate_series.sql
 * Type:				MS SQL Server SQL statement
 * Parameters:			None
 * Description:			Generate a series of values, from start to stop with a step size of step
 *						Original by: Simon Greener, Independent GeoSpatial Solutions Architect
 *						http://www.spatialdbadvisor.com/sql_server_blog/86/generate_series-for-sql-server-2008
 */
CREATE FUNCTION generate_series ( @p_start INT, @p_end INT, @p_step INT=1 )
RETURNS @Integers TABLE ( [IntValue] INT )
AS
BEGIN
    DECLARE
      @v_i                 INT,
      @v_step              INT,
      @v_terminating_value INT;
    BEGIN
      SET @v_i = CASE WHEN @p_start IS NULL THEN 1 ELSE @p_start END;
      SET @v_step  = CASE WHEN @p_step IS NULL OR @p_step = 0 THEN 1 ELSE @p_step END;
      SET @v_terminating_value =  @p_start + CONVERT(INT,ABS(@p_start-@p_end) / ABS(@v_step) ) * @v_step;
      -- Check for impossible combinations
      IF NOT ( ( @p_start > @p_end AND SIGN(@p_step) = 1 )
               OR
               ( @p_start < @p_end AND SIGN(@p_step) = -1 ))
      BEGIN
        -- Generate values
        WHILE ( 1 = 1 )
        BEGIN
           INSERT INTO @Integers ( [IntValue] ) VALUES ( @v_i )
           IF ( @v_i = @v_terminating_value )
              BREAK
           SET @v_i = @v_i + @v_step;
        END;
      END;
    END;
    RETURN
END;

Administrative Geography

The SAHSULAND example geography is supplied as part of the RIF. To load the SEER test dataset you first need to load the USA County level administrative geography. The scripts and the data are creared by the tile-maker program:

To install, change to the <tile maker directory, e.g. C:\Users\phamb\OneDrive\April 2018 deliverable for SAHSU\SEER Data\Tile maker USA>

• Make sure nobody is logged onto the RIF;
• cd C:\Users\phamb\OneDrive\April 2018 deliverable for SAHSU\SEER Data\Tile maker USA;
• Run rif_pg_usa_2014.sql or rif_mssql_usa_2014.sql;.

The scripts rif_pg_usa_2014.sql or rif_mssql_usa_2014.sql load processed geometry and tiles tables into production database:

a) integrate with RIF40 control tables, e.g:

• geography_usa_2014
• geolevels_usa_2014

b) Processed geometry data (partitioned in PostGres), e.g:

• geometry_usa_2014

c) Hierarchy table, e.g:

• hierarchy_usa_2014

d) Lookup tables, e.g:

• lookup_cb_2014_us_county_500k
• lookup_cb_2014_us_nation_5m
• lookup_cb_2014_us_state_500k

e) Tiles table and view

• t_tiles_usa_2014
• tiles_usa_2014

Postgres

Run: psql -U rif40 -w -e -f rif_pg_USA_2014.sql

SQL Server

Run: sqlcmd -U rif40 -d <database name> -b -m-1 -e -r1 -i rif_mssql_USA_2014.sql -v pwd="%cd%"

Numerator

Pre Processing

• Create numerator load tables to load data from CSV or Text files. Fixed length (mainframe) record files have to be loaded as a single string and then chopped converted into the relevant fields in stage four:
  • Postgres:

    CREATE TABLE seer9_yr1973_2013_fixed_length (
        record_value VARCHAR(358)
    );
    

  • SQL Server:

    TO BE ADDED
    

• Load numerator tables from CSV or Text files:
  • Postgres:

    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\BREAST.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\COLRECT.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\DIGOTHR.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\FEMGEN.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\LYMYLEUK.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\MALEGEN.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\OTHER.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\RESPIR.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2005.lo_2nd_half\URINARY.TXT' WITH CSV;
    
    /*
    C:\Users\Peter\Documents\Local data loading\SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak>wc -l *.TXT
       190126 BREAST.TXT
       112982 COLRECT.TXT
        98671 DIGOTHR.TXT
        84681 FEMGEN.TXT
        93322 LYMYLEUK.TXT
       161911 MALEGEN.TXT
       193120 OTHER.TXT
       122381 RESPIR.TXT
        70599 URINARY.TXT
      1127793 total
     */
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\BREAST.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\COLRECT.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\DIGOTHR.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\FEMGEN.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\LYMYLEUK.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\MALEGEN.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\OTHER.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\RESPIR.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1992_2013.sj_la_rg_ak\URINARY.TXT' WITH CSV;
    
    /*
    C:\Users\Peter\Documents\Local data loading>wc -l SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9*.TXT
        769261 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/BREAST.TXT
        528452 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/COLRECT.TXT
        364018 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/DIGOTHR.TXT
        434960 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/FEMGEN.TXT
        381579 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/LYMYLEUK.TXT
        636118 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/MALEGEN.TXT
        784697 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/OTHER.TXT
        643924 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/RESPIR.TXT
        320405 SEER_1973_2013_TEXTDATA/incidence/yr1973_2013.seer9/URINARY.TXT
       4863414 total
     */
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\BREAST.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\COLRECT.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\DIGOTHR.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\FEMGEN.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\LYMYLEUK.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\MALEGEN.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\OTHER.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\RESPIR.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr1973_2013.seer9\URINARY.TXT' WITH CSV;
    
    /*
    C:\Users\Peter\Documents\Local data loading\SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga>wc -l *.TXT
        505731 BREAST.TXT
        301745 COLRECT.TXT
        239579 DIGOTHR.TXT
        174656 FEMGEN.TXT
        247252 LYMYLEUK.TXT
        428840 MALEGEN.TXT
        618807 OTHER.TXT
        431674 RESPIR.TXT
        226912 URINARY.TXT
       3175196 total
     */
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\BREAST.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\COLRECT.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\DIGOTHR.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\FEMGEN.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\LYMYLEUK.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\MALEGEN.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\OTHER.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\RESPIR.TXT' WITH CSV;
    \copy seer9_yr1973_2013_fixed_length FROM 'SEER_1973_2013_TEXTDATA\incidence\yr2000_2013.ca_ky_lo_nj_ga\URINARY.TXT' WITH CSV;
    

  • SQL Server:

    TO BE ADDED
    

• Check all numerator table data has been loaded:
  • Postgres:

    --
    -- Check rowcount
    --
    SELECT COUNT(*) AS total FROM seer9_yr1973_2013_fixed_length;
    DO LANGUAGE plpgsql $$
    DECLARE
        c1 CURSOR FOR
            SELECT COUNT(*) AS total
              FROM seer9_yr1973_2013_fixed_length;
        c1_rec RECORD;
    BEGIN
        OPEN c1;
        FETCH c1 INTO c1_rec;
        CLOSE c1;
    --
        IF c1_rec.total = 9176963 THEN
            RAISE INFO 'Table: seer9_yr1973_2013_fixed_length has % rows', c1_rec.total;
        ELSE
            RAISE EXCEPTION 'Table: seer9_yr1973_2013_fixed_length has % rows; expecting 9176963', c1_rec.total;
        END IF;
    END;
    $$;
    

  • SQL Server:

    TO BE ADDED
    

• Convert numerator fixed length string into new numerator load table with the correct columns and datatypes:
  • Postgres:

    --
    -- Convert datatypes
    --
    CREATE TABLE seer9_yr1973_2013
    AS
    SELECT SUBSTRING(record_value FROM 39 FOR 4)::INTEGER AS year_dx,	/* Year of diagnosis */
           SUBSTRING(record_value FROM 24 FOR 1)::INTEGER AS sex,		/* Sex */
           SUBSTRING(record_value FROM 25 FOR 3)::INTEGER AS age_dx, 	/* Age at diagnosis */
           SUBSTRING(record_value FROM 246 FOR 2)::Text AS state_fips_code,	 /* State FIPS code */
           SUBSTRING(record_value FROM 248 FOR 3)::Text AS county_fips_code, /* County FIPS code */
           SUBSTRING(record_value FROM 208 FOR 4)::Text AS icdot10v, 	/* Recode ICD-O-2 to 10 */
           SUBSTRING(record_value FROM 1 FOR 8)::INTEGER AS pubcsnum,	/* Patient ID */
           SUBSTRING(record_value FROM 35 FOR 2)::INTEGER AS seq_num,  	/* Sequence number */
           SUBSTRING(record_value FROM 53 FOR 4)::Text AS histo3v,		/* Histologic Type ICD-O-3 */
           SUBSTRING(record_value FROM 57 FOR 1)::Text AS beho3v,		/* Behavior code ICD-O-3 */
           SUBSTRING(record_value FROM 233 FOR 1)::INTEGER AS rac_reca,	/* Race recode A (WHITE, BLACK, OTHER) */
           SUBSTRING(record_value FROM 234 FOR 1)::INTEGER AS rac_recy,	/* Race recode Y (W, B, AI, API) */
           SUBSTRING(record_value FROM 235 FOR 1)::INTEGER AS origrecb,	/* Origin Recode NHIA (HISPANIC, NON-HISP) */
           SUBSTRING(record_value FROM 255 FOR 5)::Text AS codpub,	  	/* Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html) */
           SUBSTRING(record_value FROM 9 FOR 10)::INTEGER AS reg		/* SEER registry */
      FROM seer9_yr1973_2013_fixed_length;
    

  • SQL Server:

    TO BE ADDED
    

• Create numerator table from load table:
  • Postgres:

    /* Cancer registry codes:
    
    Code			Description (first year of data)
    0000001501		San Francisco-Oakland SMSA (1973)
    0000001502		Connecticut (1973)
    0000001520		Metropolitan Detroit (1973)
    0000001521		Hawaii (1973)
    0000001522		Iowa (1973)
    0000001523		New Mexico (1973)
    0000001525		Seattle (Puget Sound) (1974)
    0000001526		Utah (1973)
    0000001527		Metropolitan Atlanta (1975)
    0000001529		Alaska*
    0000001531		San Jose-Monterey*
    0000001535		Los Angeles*
    0000001537		Rural Georgia*
    0000001541		Greater California (excluding SF, Los Angeles & SJ)**
    0000001542		Kentucky**
    0000001543		Louisiana**
    0000001544		New Jersey**
    0000001547		Greater Georgia (excluding AT and RG)**
    (Year in parentheses refers to first diagnosis year data reported to SEER)
    
    *Note: The incidence/yr1992_2013.sj_la_rg_ak directory files contain cases for Alaska, San Jose-Monterey, Los Angeles and
           Rural Georgia registries beginning in 1992. Cases have been collected by SEER for these registries prior to 1992 but have
           been excluded from the SEER Research Data file.
    **Note: The incidence/yr2000_2013.ca_ky_lo_nj_ga directory files contain cases for Greater California, Kentucky, Louisiana,
            New Jersey and Greater Georgia registries beginning in 2000. For the year 2005, only January through June diagnoses are
            included for Louisiana. The July through December incidence cases can be found in the yr2005.lo_2nd_half directory.
    */
    
    --
    -- Extract cancer
    -- * Convert age, sex to RIF age_sex_group 1
    -- * Convert FIPS codes to the geographic Names Information System (GNIS) codes used by the RIF
    -- * Enforce primary key (Patient ID, Sequence number)
    -- * Check AGE_SEX_GROUP, handle uncoded age
    -- * Convert unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g.
    --   900 series to represent county/independent city combinations in Virginia.
    -- * Use icdot10v (ICD 10 site code - recoded from ICD-O-2 to 10) as the ICD field
    --
    DROP TABLE IF EXISTS :USER.seer_cancer;
    CREATE TABLE seer_cancer
    AS
    SELECT a.year_dx AS year,
           'US'::Text AS cb_2014_us_nation_5m,
           d.statens AS cb_2014_us_state_500k,
           COALESCE(c.countyns, 'UNKNOWN: '||a.county_fips_code) AS cb_2014_us_county_500k,
           (a.sex*100)+
                CASE WHEN a.age_dx = 999 THEN 99
                ELSE                     b.offset END AS age_sex_group,
           a.icdot10v,		/* ICD 10 site code - recoded from ICD-O-2 to 10 */
           a.pubcsnum,		/* Patient ID */
           a.seq_num,		/* Sequence number */
           a.histo3v,		/* Histologic Type ICD-O-3 */
           a.beho3v,		/* Behavior code ICD-O-3 */
           a.rac_reca,		/* Race recode A (WHITE, BLACK, OTHER) */
           a.rac_recy,		/* Race recode Y (W, B, AI, API) */
           a.origrecb,		/* Origin Recode NHIA (HISPANIC, NON-HISP) */
           a.codpub,	  	/* Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html) */
           a.reg-1500 AS reg		/* SEER registry (minus 1500 so same as population file) */
      FROM seer9_yr1973_2013 a
            LEFT OUTER JOIN rif40_age_groups b ON (b.age_group_id = 1 AND a.age_dx BETWEEN b.low_age AND b.high_age /* limit 255! */)
            LEFT OUTER JOIN cb_2014_us_county_500k c ON (a.state_fips_code = c.statefp AND a.county_fips_code = c.countyfp)
            LEFT OUTER JOIN cb_2014_us_state_500k d ON (a.state_fips_code = d.statefp)
     ORDER BY a.pubcsnum, a.seq_num;
    

  • SQL Server:

    TO BE ADDED
    

• Comment numerator load table:
  • Postgres:

    COMMENT ON TABLE seer_cancer IS 'SEER Cancer data 1973-2013. 9 States in total';
    COMMENT ON COLUMN seer_cancer.year IS 'Year';
    COMMENT ON COLUMN seer_cancer.cb_2014_us_nation_5m IS 'United States to county level including territories';
    COMMENT ON COLUMN seer_cancer.cb_2014_us_state_500k IS 'State geographic Names Information System (GNIS) code';
    COMMENT ON COLUMN seer_cancer.cb_2014_us_county_500k IS 'County geographic Names Information System (GNIS) code. Unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g. the 900 series to represent county/independent city combinations in Virginia.';
    COMMENT ON COLUMN seer_cancer.age_sex_group IS 'RIF age_sex_group 1 (21 bands)';
    COMMENT ON COLUMN seer_cancer.icdot10v IS 'ICD 10 site code - recoded from ICD-O-2 to 10';
    COMMENT ON COLUMN seer_cancer.pubcsnum IS 'Patient ID';
    COMMENT ON COLUMN seer_cancer.seq_num IS 'Sequence number';
    COMMENT ON COLUMN seer_cancer.histo3v IS 'Histologic Type ICD-O-3';
    COMMENT ON COLUMN seer_cancer.beho3v IS 'Behavior code ICD-O-3';
    COMMENT ON COLUMN seer_cancer.rac_reca IS 'Race recode A (WHITE, BLACK, OTHER)';
    COMMENT ON COLUMN seer_cancer.rac_recy IS 'Race recode Y (W, B, AI, API)';
    COMMENT ON COLUMN seer_cancer.origrecb IS 'Origin Recode NHIA (HISPANIC, NON-HISP)';
    COMMENT ON COLUMN seer_cancer.codpub IS 'Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html)';
    COMMENT ON COLUMN seer_cancer.reg IS 'SEER registry (minus 1500 so same as population file)';
    

  • SQL Server:

    TO BE ADDED
    

• Check rowcount:
  • Postgres:

    SELECT COUNT(*) AS total FROM seer_cancer;
    DO LANGUAGE plpgsql $$
    DECLARE
        c1 CURSOR FOR
            SELECT COUNT(*) AS total
              FROM seer_cancer;
        c1_rec RECORD;
    BEGIN
        OPEN c1;
        FETCH c1 INTO c1_rec;
        CLOSE c1;
    --
        IF c1_rec.total = 9176963 THEN
            RAISE INFO 'Table: seer_cancer has % rows', c1_rec.total;
        ELSE
            RAISE EXCEPTION 'Table: seer_cancer has % rows; expecting 9176963', c1_rec.total;
        END IF;
    END;
    $$;
    

  • SQL Server:

    TO BE ADDED
    

• Add constraints:
  • Postgres:

    ALTER TABLE seer_cancer ALTER COLUMN year SET NOT NULL;
    ALTER TABLE seer_cancer ALTER COLUMN cb_2014_us_state_500k SET NOT NULL;
    ALTER TABLE seer_cancer ALTER COLUMN cb_2014_us_county_500k SET NOT NULL;
    
    ALTER TABLE seer_cancer ADD CONSTRAINT seer_cancer_asg_ck
        CHECK (age_sex_group BETWEEN 100 AND 121 OR age_sex_group BETWEEN 200 AND 221 OR age_sex_group IN (199, 299) /* No age coded */);
    
    ALTER TABLE seer_cancer ALTER COLUMN age_sex_group SET NOT NULL;
    

  • SQL Server:

    TO BE ADDED
    

• Add indexes:
  • Postgres:

    ALTER TABLE seer_cancer ADD CONSTRAINT seer_cancer_pk
        PRIMARY KEY (pubcsnum, seq_num);
    CREATE INDEX seer_cancer_year ON seer_cancer (year);
    CREATE INDEX seer_cancer_cb_2014_us_nation_5m ON seer_cancer(cb_2014_us_nation_5m);
    CREATE INDEX seer_cancer_cb_2014_us_state_500k ON seer_cancer(cb_2014_us_state_500k);
    CREATE INDEX seer_cancer_cb_2014_us_county_500k ON seer_cancer(cb_2014_us_county_500k);
    CREATE INDEX seer_cancer_age_sex_group ON seer_cancer(age_sex_group);
    CREATE INDEX seer_cancer_icdot10v ON seer_cancer(icdot10v);
    CREATE INDEX seer_cancer_reg ON seer_cancer(reg);
    

  • SQL Server:

    TO BE ADDED
    

• Unload table:
  • Postgres:

    \copy seer_cancer TO 'seer_cancer.csv' WITH CSV HEADER
    

  • SQL Server:

    TO BE ADDED
    

Load Processing

• Remove numerator setup data, views and tables;
  • Postgres:

    DROP TABLE IF EXISTS rif_data.t_seer_cancer;
    DROP VIEW IF EXISTS rif_data.seer_cancer;
    
    DELETE FROM rif40.rif40_table_outcomes
     WHERE numer_tab='SEER_CANCER';
    
    DELETE FROM rif40.rif40_tables
     WHERE table_name='SEER_CANCER';
    

  • SQL Server:

    IF OBJECT_ID('rif_data.t_seer_cancer', 'U') IS NOT NULL BEGIN
        DROP TABLE rif_data.t_seer_cancer;
        DROP VIEW rif_data.seer_cancer;
        DELETE FROM rif40.rif40_table_outcomes
         WHERE numer_tab='SEER_CANCER';
        DECLARE c1 CURSOR FOR
            SELECT table_name
              FROM rif40.rif40_tables
             WHERE table_name='SEER_CANCER';
        DECLARE @c1_table AS VARCHAR(30);
        OPEN c1;
        FETCH NEXT FROM c1 INTO @c1_table;
        IF @c1_table = 'SEER_CANCER' BEGIN
            DELETE FROM rif40.rif40_tables
             WHERE table_name='SEER_CANCER';
        END;
        CLOSE c1;
        DEALLOCATE c1;
    END;
    GO
    

    If studies have already been created you will get the error:

    Msg 51147, Level 16, State 1, Server PETER-PC\SAHSU, Procedure tr_rif40_tables_checks, Line 45
    Table name: [rif40].[rif40_tables], Cannot DELETE from RIF40_TABLES
    

• Create numerator table;
  • Postgres:

    CREATE TABLE rif_data.t_seer_cancer
    (
      year integer NOT NULL, -- Year
      cb_2014_us_nation_5m text, -- United States to county level including territories
      cb_2014_us_state_500k text NOT NULL, -- State geographic Names Information System (GNIS) code
      cb_2014_us_county_500k text NOT NULL, -- County geographic Names Information System (GNIS) code. Unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g. the 900 series to represent county/independent city combinations in Virginia.
      age_sex_group integer NOT NULL, -- RIF age_sex_group 1 (21 bands)
      icdot10v text, -- ICD 10 site code - recoded from ICD-O-2 to 10
      pubcsnum integer NOT NULL, -- Patient ID
      seq_num integer NOT NULL, -- Sequence number
      histo3v text, -- Histologic Type ICD-O-3
      beho3v text, -- Behavior code ICD-O-3
      rac_reca integer, -- Race recode A (WHITE, BLACK, OTHER)
      rac_recy integer, -- Race recode Y (W, B, AI, API)
      origrecb integer, -- Origin Recode NHIA (HISPANIC, NON-HISP)
      codpub text, -- Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html)
      reg integer, -- SEER registry (minus 1500 so same as population file)
      CONSTRAINT seer_cancer_pk PRIMARY KEY (pubcsnum, seq_num),
      CONSTRAINT seer_cancer_asg_ck CHECK (
            (age_sex_group >= 100 AND age_sex_group <= 121) OR
            (age_sex_group >= 200 AND age_sex_group <= 221) OR
            (age_sex_group IN (199, 299))
        )
    );
    

  • SQL Server:

    CREATE TABLE rif_data.t_seer_cancer
    (
      year integer NOT NULL, /* Year */
      cb_2014_us_nation_5m VARCHAR(200), /* United States to county level including territories */
      cb_2014_us_state_500k VARCHAR(200) NOT NULL, /* State geographic Names Information System (GNIS) code */
      cb_2014_us_county_500k VARCHAR(200) NOT NULL, /* County geographic Names Information System (GNIS) code. Unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g. the 900 series to represent county/independent city combinations in Virginia. */
      age_sex_group integer NOT NULL, /* RIF age_sex_group 1 (21 bands) */
      icdot10v VARCHAR(200), /* ICD 10 site code - recoded from ICD-O-2 to 10 */
      pubcsnum integer NOT NULL, /* Patient ID */
      seq_num integer NOT NULL, /* Sequence number */
      histo3v VARCHAR(200), /* Histologic Type ICD-O-3 */
      beho3v VARCHAR(200), /* Behavior code ICD-O-3 */
      rac_reca integer, /* Race recode A (WHITE, BLACK, OTHER) */
      rac_recy integer, /* Race recode Y (W, B, AI, API) */
      origrecb integer, /* Origin Recode NHIA (HISPANIC, NON-HISP) */
      codpub VARCHAR(200), /* Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html) */
      reg integer, /* SEER registry (minus 1500 so same as population file) */
      CONSTRAINT seer_cancer_pk PRIMARY KEY (pubcsnum, seq_num),
      CONSTRAINT seer_cancer_asg_ck CHECK (
            (age_sex_group >= 100 AND age_sex_group <= 121) OR
            (age_sex_group >= 200 AND age_sex_group <= 221) OR
            (age_sex_group IN (199, 299))
        )
    );
    GO
    

• Create numerator views:

  --
  -- Create a test view
  --
  CREATE OR REPLACE VIEW rif_data.seer_cancer AS
  SELECT * FROM rif_data.t_seer_cancer;
  

• Load CSV data into numerator table;
  • Postgres:

    \copy rif_data.t_seer_cancer FROM 'seer_cancer.csv' WITH CSV HEADER;
    

  • SQL Server:

    BULK INSERT rif_data.t_seer_cancer
    FROM '$(pwd)/seer_cancer.csv'	-- Note use of pwd; set via -v pwd="%cd%" in the sqlcmd command line
    WITH
    (
        FIRSTROW = 2,
        FORMATFILE = '$(pwd)/seer_cancer.fmt',		-- Use a format file
        TABLOCK					-- Table lock
    );
    GO
    

• Check all numerator table data has been loaded;
  • Postgres:

    SELECT COUNT(*) AS total FROM t_seer_cancer;
    DO LANGUAGE plpgsql $$
    DECLARE
        c1 CURSOR FOR
            SELECT COUNT(*) AS total
              FROM t_seer_cancer;
        c1_rec RECORD;
    BEGIN
        OPEN c1;
        FETCH c1 INTO c1_rec;
        CLOSE c1;
    --
        IF c1_rec.total = 9176963 THEN
            RAISE INFO 'Table: t_seer_cancer has % rows', c1_rec.total;
        ELSE
            RAISE EXCEPTION 'Table: t_seer_cancer has % rows; expecting 9176963', c1_rec.total;
        END IF;
    END;
    $$;
    

  • SQL Server:

    SELECT COUNT(*) AS total FROM rif_data.t_seer_cancer;
    DECLARE c1 CURSOR FOR
        SELECT COUNT(*) AS total FROM rif_data.t_seer_cancer;
    DECLARE @c1_total AS INTEGER;
    OPEN c1;
    FETCH NEXT FROM c1 INTO @c1_total;
    IF @c1_total = 9176963
        PRINT 'Table: t_seer_cancer has 9176963 rows';
    ELSE
        RAISERROR('Table: t_seer_cancer has %i rows; expecting 9176963', 16, 1, @c1_total);
    CLOSE c1;
    DEALLOCATE c1;
    GO
    

• Add indexes to numerator table:

    CREATE INDEX seer_cancer_age_sex_group
      ON rif_data.t_seer_cancer
      (age_sex_group);
  
    CREATE INDEX seer_cancer_cb_2014_us_county_500k
      ON rif_data.t_seer_cancer
      (cb_2014_us_county_500k);
  
    CREATE INDEX seer_cancer_cb_2014_us_nation_5m
      ON rif_data.t_seer_cancer
      (cb_2014_us_nation_5m);
  
    CREATE INDEX seer_cancer_cb_2014_us_state_500k
      ON rif_data.t_seer_cancer
      (cb_2014_us_state_500k);
  
    CREATE INDEX seer_cancer_icdot10v
      ON rif_data.t_seer_cancer
      (icdot10v);
  
    CREATE INDEX seer_cancer_reg
      ON rif_data.t_seer_cancer
      (reg);
  
    CREATE INDEX seer_cancer_year
      ON rif_data.t_seer_cancer
      (year);
  

• Comment numerator table and columns;
  • Postgres:

    COMMENT ON TABLE rif_data.t_seer_cancer
      IS 'SEER Cancer data 1973-2013. 9 States in total';
    COMMENT ON COLUMN rif_data.t_seer_cancer.year IS 'Year';
    COMMENT ON COLUMN rif_data.t_seer_cancer.cb_2014_us_nation_5m IS 'United States to county level including territories';
    COMMENT ON COLUMN rif_data.t_seer_cancer.cb_2014_us_state_500k IS 'State geographic Names Information System (GNIS) code';
    COMMENT ON COLUMN rif_data.t_seer_cancer.cb_2014_us_county_500k IS 'County geographic Names Information System (GNIS) code. Unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g. the 900 series to represent county/independent city combinations in Virginia.';
    COMMENT ON COLUMN rif_data.t_seer_cancer.age_sex_group IS 'RIF age_sex_group 1 (21 bands)';
    COMMENT ON COLUMN rif_data.t_seer_cancer.icdot10v IS 'ICD 10 site code - recoded from ICD-O-2 to 10';
    COMMENT ON COLUMN rif_data.t_seer_cancer.pubcsnum IS 'Patient ID';
    COMMENT ON COLUMN rif_data.t_seer_cancer.seq_num IS 'Sequence number';
    COMMENT ON COLUMN rif_data.t_seer_cancer.histo3v IS 'Histologic Type ICD-O-3';
    COMMENT ON COLUMN rif_data.t_seer_cancer.beho3v IS 'Behavior code ICD-O-3';
    COMMENT ON COLUMN rif_data.t_seer_cancer.rac_reca IS 'Race recode A (WHITE, BLACK, OTHER)';
    COMMENT ON COLUMN rif_data.t_seer_cancer.rac_recy IS 'Race recode Y (W, B, AI, API)';
    COMMENT ON COLUMN rif_data.t_seer_cancer.origrecb IS 'Origin Recode NHIA (HISPANIC, NON-HISP)';
    COMMENT ON COLUMN rif_data.t_seer_cancer.codpub IS 'Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html)';
    COMMENT ON COLUMN rif_data.t_seer_cancer.reg IS 'SEER registry (minus 1500 so same as population file)';
    
    COMMENT ON VIEW rif_data.seer_cancer
      IS 'SEER Cancer data 1973-2013 view test. 9 States in total';
    COMMENT ON COLUMN rif_data.seer_cancer.year IS 'Year';
    COMMENT ON COLUMN rif_data.seer_cancer.cb_2014_us_nation_5m IS 'United States to county level including territories';
    COMMENT ON COLUMN rif_data.seer_cancer.cb_2014_us_state_500k IS 'State geographic Names Information System (GNIS) code';
    COMMENT ON COLUMN rif_data.seer_cancer.cb_2014_us_county_500k IS 'County geographic Names Information System (GNIS) code. Unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g. the 900 series to represent county/independent city combinations in Virginia.';
    COMMENT ON COLUMN rif_data.seer_cancer.age_sex_group IS 'RIF age_sex_group 1 (21 bands)';
    COMMENT ON COLUMN rif_data.seer_cancer.icdot10v IS 'ICD 10 site code - recoded from ICD-O-2 to 10';
    COMMENT ON COLUMN rif_data.seer_cancer.pubcsnum IS 'Patient ID';
    COMMENT ON COLUMN rif_data.seer_cancer.seq_num IS 'Sequence number';
    COMMENT ON COLUMN rif_data.seer_cancer.histo3v IS 'Histologic Type ICD-O-3';
    COMMENT ON COLUMN rif_data.seer_cancer.beho3v IS 'Behavior code ICD-O-3';
    COMMENT ON COLUMN rif_data.seer_cancer.rac_reca IS 'Race recode A (WHITE, BLACK, OTHER)';
    COMMENT ON COLUMN rif_data.seer_cancer.rac_recy IS 'Race recode Y (W, B, AI, API)';
    COMMENT ON COLUMN rif_data.seer_cancer.origrecb IS 'Origin Recode NHIA (HISPANIC, NON-HISP)';
    COMMENT ON COLUMN rif_data.seer_cancer.codpub IS 'Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html)';
    COMMENT ON COLUMN rif_data.seer_cancer.reg IS 'SEER registry (minus 1500 so same as population file)';
    

  • SQL Server:

    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'SEER Cancer data 1973-2013. 9 States in total',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer';
    GO
    
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Year',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'year';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'United States to county level including territories',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'cb_2014_us_nation_5m';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'State geographic Names Information System (GNIS) code',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'cb_2014_us_state_500k';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'County geographic Names Information System (GNIS) code. Unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g. the 900 series to represent county/independent city combinations in Virginia.',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'cb_2014_us_county_500k';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'RIF age_sex_group 1 (21 bands)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'age_sex_group';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'ICD 10 site code - recoded from ICD-O-2 to 10',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'icdot10v';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Patient ID',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'pubcsnum';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Sequence number',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'seq_num';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Histologic Type ICD-O-3',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'histo3v';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Behavior code ICD-O-3',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'beho3v';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Race recode A (WHITE, BLACK, OTHER)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'rac_reca';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Race recode Y (W, B, AI, API)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'rac_recy';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Origin Recode NHIA (HISPANIC, NON-HISP)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'origrecb';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'codpub';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'SEER registry (minus 1500 so same as population file)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'Table', @level1name = 't_seer_cancer',
        @level2type = N'Column', @level2name = 'reg';
    GO
    
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'SEER Cancer data 1973-2013 test view. 9 States in total',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer';
    GO
    
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Year',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'year';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'United States to county level including territories',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'cb_2014_us_nation_5m';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'State geographic Names Information System (GNIS) code',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'cb_2014_us_state_500k';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'County geographic Names Information System (GNIS) code. Unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g. the 900 series to represent county/independent city combinations in Virginia.',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'cb_2014_us_county_500k';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'RIF age_sex_group 1 (21 bands)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'age_sex_group';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'ICD 10 site code - recoded from ICD-O-2 to 10',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'icdot10v';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Patient ID',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'pubcsnum';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Sequence number',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'seq_num';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Histologic Type ICD-O-3',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'histo3v';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Behavior code ICD-O-3',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'beho3v';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Race recode A (WHITE, BLACK, OTHER)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'rac_reca';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Race recode Y (W, B, AI, API)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'rac_recy';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Origin Recode NHIA (HISPANIC, NON-HISP)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'origrecb';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'Cause of death to SEER site recode (see: https://seer.cancer.gov/codrecode/1969+_d09172004/index.html)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'codpub';
    GO
    EXECUTE sp_addextendedproperty
        @name = N'MS_Description',
        @value = N'SEER registry (minus 1500 so same as population file)',
        @level0type = N'Schema', @level0name = 'rif_data',
        @level1type = N'View', @level1name = 'seer_cancer',
        @level2type = N'Column', @level2name = 'reg';
    GO
    

• Grant grant access on numerator tables and views to an appropriate role;

    GRANT SELECT ON rif_data.seer_cancer TO seer_user;
  

• Setup numerator tables and views:

  INSERT INTO rif40.rif40_tables (
       theme,
       table_name,
       description,
       year_start,
       year_stop,
       total_field,
       isindirectdenominator,
       isdirectdenominator,
       isnumerator,
       automatic,
       sex_field_name,
       age_group_field_name,
       age_sex_group_field_name,
       age_group_id)
    SELECT
       'cancers',			/* theme */
       'SEER_CANCER',		/* table_name */
       'SEER Cancer data 1973-2013. 9 States in total',				/* description */
       MIN(year),			/* year_start */
       MAX(year),			/* year_stop */
       NULL,				/* total_field */
       0,					/* isindirectdenominator */
       0,					/* isdirectdenominator */
       1,					/* isnumerator */
       1,					/* automatic */
       NULL,				/* sex_field_name */
       NULL,				/* age_group_field_name */
       'AGE_SEX_GROUP',		/* age_sex_group_field_name */
       1					/* age_group_id */
      FROM rif_data.seer_cancer;
  
    --
    -- Setup ICD field (SEER_ICDOT10V)
    -- * ICD-O-3 histology (HISTO3V) to follow later
    --
    INSERT INTO rif40.rif40_outcome_groups(
       outcome_type, outcome_group_name, outcome_group_description, field_name, multiple_field_count)
    SELECT
       'ICD' AS outcome_type,
       'SEER_ICDOT10V' AS outcome_group_name,
       'SEER ICDOT10V' AS outcome_group_description,
       'ICDOT10V' AS field_name,
       0 AS multiple_field_count
    WHERE NOT EXISTS (SELECT outcome_group_name FROM  rif40.rif40_outcome_groups WHERE outcome_group_name = 'SEER_ICDOT10V');
  
    INSERT INTO rif40.rif40_table_outcomes (
       outcome_group_name,
       numer_tab,
       current_version_start_year)
    SELECT
       'SEER_ICDOT10V',
       'SEER_CANCER',
       MIN(year)
    FROM rif_data.seer_cancer;
  

Denominator

Pre Processing

• Create denominator load tables:
  • Postgres:

    DROP TABLE IF EXISTS :USER.seer_wbo_single_ages_fixed_length;
    DROP TABLE IF EXISTS :USER.seer_wbo_single_ages;
    
    --
    -- Load singleages.txt as a fixed length record
    --
    CREATE TABLE seer_wbo_single_ages_fixed_length (
        record_value VARCHAR(28)
    );
    

  • SQL Server:

    TO BE ADDED
    

• Load singleages.txt as a fixed length record:
  • Postgres:

    \copy seer_wbo_single_ages_fixed_length FROM 'SEER_1973_2013_TEXTDATA\populations\white_black_other\yr1973_2013.seer9\singleages.txt' WITH CSV;
    \copy seer_wbo_single_ages_fixed_length FROM 'SEER_1973_2013_TEXTDATA\populations\expanded.race.by.hispanic\yr1992_2013.seer9.plus.sj_la_rg_ak\singleages.txt' WITH CSV;
    \copy seer_wbo_single_ages_fixed_length FROM 'SEER_1973_2013_TEXTDATA\populations\expanded.race.by.hispanic\yr2000_2013.ca_ky_lo_nj_ga\singleages.txt' WITH CSV;
    \copy seer_wbo_single_ages_fixed_length FROM 'SEER_1973_2013_TEXTDATA\populations\expanded.race.by.hispanic\yr2005.lo_2nd_half\singleages.txt' WITH CSV;
    

  • SQL Server:

    TO BE ADDED
    

• Check all denominator table data has been loaded:
  • Postgres:

    SELECT COUNT(*) AS total FROM seer_wbo_single_ages_fixed_length;
    DO LANGUAGE plpgsql $$
    DECLARE
        c1 CURSOR FOR
            SELECT COUNT(*) AS total
              FROM seer_wbo_single_ages_fixed_length;
        c1_rec RECORD;
    BEGIN
        OPEN c1;
        FETCH c1 INTO c1_rec;
        CLOSE c1;
    --
        IF c1_rec.total = 10234237 THEN
            RAISE INFO 'Table: seer_wbo_single_ages_fixed_length has % rows', c1_rec.total;
        ELSE
            RAISE EXCEPTION 'Table: seer_wbo_single_ages_fixed_length has % rows; expecting 10234237', c1_rec.total;
        END IF;
    END;
    $$;
    

  • SQL Server:

    TO BE ADDED
    

• Convert denominator fixed length string into new numerator load table with the correct columns and datatypes:
  • Postgres: ```sql CREATE TABLE seer_wbo_single_ages AS SELECT SUBSTRING(record_value FROM 1 FOR 4)::INTEGER AS year, SUBSTRING(record_value FROM 5 FOR 2) AS state_postal_abbreviation, SUBSTRING(record_value FROM 7 FOR 2)::Text AS state_fips_code, SUBSTRING(record_value FROM 9 FOR 3)::Text AS county_fips_code, SUBSTRING(record_value FROM 12 FOR 2)::INTEGER AS registry, SUBSTRING(record_value FROM 14 FOR 1)::INTEGER AS race, SUBSTRING(record_value FROM 15 FOR 1)::INTEGER AS origin, SUBSTRING(record_value FROM 16 FOR 1)::INTEGER AS sex, SUBSTRING(record_value FROM 17 FOR 2)::INTEGER AS age, SUBSTRING(record_value FROM 19 FOR 10)::NUMERIC AS population FROM seer_wbo_single_ages_fixed_length; /*
    • Registry codes: * 01 = San Francisco-Oakland SMSA 02 = Connecticut 20 = Detroit (Metropolitan) 21 = Hawaii 22 = Iowa 23 = New Mexico 25 = Seattle (Puget Sound) 26 = Utah 27 = Atlanta (Metropolitan) 29 = Alaska Natives 31 = San Jose-Monterey 35 = Los Angeles 37 = Rural Georgia 41 = California excluding SF/SJM/LA 42 = Kentucky 43 = Louisiana 44 = New Jersey 47 = Greater Georgia */ ```
  • SQL Server:

    TO BE ADDED
    

• Create denominator table from load table:
  • Postgres:

    --
    -- Extract population
    -- * Convert age, sex to RIF age_sex_group 1
    -- * Convert FIPS codes to the geographic Names Information System (GNIS) codes used by the RIF
    -- * Enforce primary key
    -- * Check AGE_SEX_GROUP
    -- * Convert unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g.
    --   900 series to represent county/independent city combinations in Virginia.
    --
    DROP TABLE IF EXISTS :USER.seer_population;
    CREATE TABLE seer_population
    AS
    SELECT a.year,
           'US'::Text AS cb_2014_us_nation_5m,
           d.statens AS cb_2014_us_state_500k,
           COALESCE(c.countyns, 'UNKNOWN: '||a.county_fips_code) AS cb_2014_us_county_500k,
           (a.sex*100)+b.offset AS age_sex_group,
           SUM(a.population) AS population
      FROM seer_wbo_single_ages a
            LEFT OUTER JOIN rif40_age_groups b ON (b.age_group_id = 1 AND a.age BETWEEN b.low_age AND b.high_age)
            LEFT OUTER JOIN cb_2014_us_county_500k c ON (a.state_fips_code = c.statefp AND a.county_fips_code = c.countyfp)
            LEFT OUTER JOIN cb_2014_us_state_500k d ON (a.state_fips_code = d.statefp)
     GROUP BY a.year, d.statens, COALESCE(c.countyns, 'UNKNOWN: '||a.county_fips_code), a.sex, b.offset
     ORDER BY 1,2,3,4,5;
    

  • SQL Server:

    TO BE ADDED
    

• Comment numerator load table:
  • Postgres:

    COMMENT ON TABLE seer_population IS 'SEER Population 1972-2013. Georgia starts in 1975, Washington in 1974. 9 States in total';
    COMMENT ON COLUMN seer_population.year IS 'Year';
    COMMENT ON COLUMN seer_population.cb_2014_us_nation_5m IS 'United States to county level including territories';
    COMMENT ON COLUMN seer_population.cb_2014_us_state_500k IS 'State geographic Names Information System (GNIS) code';
    COMMENT ON COLUMN seer_population.cb_2014_us_county_500k IS 'County geographic Names Information System (GNIS) code. Unjoined county FIPS codes to "UNKNOWN: " + county FIPS code; e.g. the 900 series to represent county/independent city combinations in Virginia.';
    COMMENT ON COLUMN seer_population.age_sex_group IS 'RIF age_sex_group 1 (21 bands)';
    COMMENT ON COLUMN seer_population.population IS 'Population';
    

  • SQL Server:

    TO BE ADDED
    

• Check rowcount:
  • Postgres:

    SELECT COUNT(*) AS total FROM seer_population;
    DO LANGUAGE plpgsql $$
    DECLARE
        c1 CURSOR FOR
            SELECT COUNT(*) AS total
              FROM seer_population;
        c1_rec RECORD;
    BEGIN
        OPEN c1;
        FETCH c1 INTO c1_rec;
        CLOSE c1;
    --
        IF c1_rec.total = 614360 THEN
            RAISE INFO 'Table: seer_population has % rows', c1_rec.total;
        ELSE
            RAISE EXCEPTION 'Table: seer_population has % rows; expecting 614360', c1_rec.total;
        END IF;
    END;
    $$;
    

  • SQL Server:

    TO BE ADDED
    

• Add constraints:
  • Postgres:

    ALTER TABLE seer_population ADD CONSTRAINT seer_population_pk
        PRIMARY KEY (year, cb_2014_us_nation_5m, cb_2014_us_state_500k, cb_2014_us_county_500k, age_sex_group);
    ALTER TABLE seer_population ADD CONSTRAINT seer_population_asg_ck
        CHECK (age_sex_group BETWEEN 100 AND 121 OR age_sex_group BETWEEN 200 AND 221);
    

  • SQL Server:

    TO BE ADDED
    

• Add indexes:
  • Postgres:

    CLUSTER seer_population USING seer_population_pk;
    CREATE INDEX seer_population_year ON seer_population (year);
    CREATE INDEX seer_population_cb_2014_us_nation_5m ON seer_population(cb_2014_us_nation_5m);
    CREATE INDEX seer_population_cb_2014_us_state_500k ON seer_population(cb_2014_us_state_500k);
    CREATE INDEX seer_population_cb_2014_us_county_500k ON seer_population(cb_2014_us_county_500k);
    CREATE INDEX seer_population_age_sex_group ON seer_population(age_sex_group);
    

  • SQL Server:

    TO BE ADDED
    

• Unload table:
  • Postgres:

    \copy seer_population TO 'seer_population.csv' WITH CSV HEADER;
    

  • SQL Server:

    TO BE ADDED