IBIS-Q System Documentation - Create Back-end Query File
Back-End Query FilesThis page describes how to create the back-end files. An additional page describes how to create the Interface XML files. Both the Interface XML files and the back-end files are needed to produce a complete query module. This page includes:
- description of the back-end files
- step-by-step procedures for creating the back-end files
Sequence is not important in which end you create first (the Interface XML files or the back-end files); more than one person may be involved in creating the various files. The important thing is to make sure the names of variables match across all associated files.
Figure 3-1. Environment Containing the Interface and Back-End Files
- Establish a schedule or routine by which the various data stewards release their new datasets to you.
- Compile naming conventions and standards list to simplify naming the variables and help maintain consistency.
- Use a tested, working module.xml file as a template, and modify that template with the new data as you gather it.
Table 3-1. Interface XML and Back-end File Types
|Interface XML Files||Back-end Files|
|HTML Content (HTML Content XML Elements)||configuration (.cfg)|
|Module selection (Module selection XML Elements)||function (.def)|
|Query builder (Query builder XML Elements)||XML element output value mapping (.map)|
|Xinclude (optional)||group (.grp)|
|head (.head) and tail (.tail)|
Overview of Back-end FilesThe IBIS-Q administrator back-end files reside on the SAS server.
|Configuration file||holds all ibis parameters and variables. It is a translator between query string and CGI application|
|Function file||contains the measure-specific information needed to run a query on a dataset. It consists of three parts: (1) data restrictions, (2) SAS template, (3) format and order of the output and the XML Output file to be used for the results|
|Group file||CGI application regroups categories by using SAS variables based on categories defined in the .grp file. The .grp file name must match the html name of the variable. The variable name will appear in the configuration file and the Query builder file.|
|Head and Tail files||Define the beginning and ending tags to the xml output from CGI application|
|XML Element Output Value Mapping file||Labels the output from the CGI with the appropriate XML tag from the module.xml file (specifically, the measure tag within the configuration tag)|
Other variable types to be aware of when creating the back-end files include:
- Dynamic variables - The variable defined dynamically by CGI application based on definitions within the group file. The type2 needs to be set to 3 in the configuration file.
- Proxy variables - The variables used to group similar variables, such as different age groupings. The type2 needs to be set to 10 in the configuration file.
Procedures for Creating Back-End Files
Prepare SAS Data (See also Preparing SAS Datasets page)Usually, two datasets need to be prepared:
- Main dataset (for example, Mortality data), numerator
- Population data for rate calculation, denominator
Decisions to make when preparing the data. Examples are given for each decision needed.
- Decide what variables will be used and how to display them on interface;
for example, showing age by single year or by age-adjusted group
- Variables: Year, sex, and age
- Year will be shown as single years
- Sex will have Male, Female and unknown.
- Age will be in 11 age groups: less than 1, 1-4, 5-14, 15-24, 25-34, 35-44, 45-54, 55-64, 65-74, 75-84, 85+.
- Check variable availability in both main data and population data.
- Year: available years in mortality data are 1995-2003. In population data the years available are 1995-2001. For this module we will use years 1995-2001
- Age: single year of age in mortality data and only 4-year age groups in population data. The single year of age wouldn't be available on interface.
- Sex is in both mortality and population data.
- Regroup variables if needed.
The variable and variable values specified in xml file should have a corresponding variable and value in the SAS dataset.
- AgeGpAA will be used in the xml file. The corresponding SAS variable is agepop. Agepop is the regrouped age variable. This variable is hard coded in both mortality data and population data
- Dynamic variable provides an option for not necessary to hard code variable in data even the variable displays on the interface. For example, different age groups could be displayed on the interface with only single year of age variables in both main dataset and population dataset.
- Make variable numeric if possible
All three variables are numeric in datasets.
- SAS data contents
- Mortality data contents
- Arizona population data contents
Data Set Name DATA2.AZDEATH Observations 376117 Member Type DATA Variables 9 Engine V9 Indexes 0 Created Monday, May 09, 2005 01:26:23 PM Observation Length 104 Last Modified Monday, May 09, 2005 01:26:23 PM Deleted Observations 0 Protection Compressed NO Data Set Type Sorted NO Label Data Representation WINDOWS_32 Encoding wlatin1 Western (Windows)
Engine/Host Dependent Information
Data Set Page Size 12288 Number of Data Set Pages 3215 First Data Page 1 Max Obs per Page 117 Obs in First Data Page 99 Number of Data Set Repairs 0 File Name d:\tomcat\webapps\ibisq\sasData\azdeath.sas7bdat Release Created 9.0101M3 Host Created WIN_SRV
Alphabetic List of Variables and Attributes
# Variable Type Len 1 AGE Num 8 7 agegrp1 Num 8 8 agepop Num 8 6 cause Char 6 4 county Char 45 2 race1 N um 3 5 sex Num 8 9 x Num 8 3 year Num 8
Data Set Name DATA.AZPOP Observations 50 Member Type DATA Variables 10 EngineV9 Indexes 0 Created Thursday, April 21, 2005 09:51:50 AM Observation Length 80 Last Modified Thursday, April 21, 2005 09:51:50 AM Deleted Observations 0 Protection Compressed NO Data Set Type Sorted NO Label Data Representation WINDOWS_32 Encoding Default
Engine/Host Dependent Information
Data Set Page Size 8192 Number of Data Set Pages 198 First Data Page 1 Max Obs per Page 101 Obs in First Data Page 75 Number of Data Set Repairs 0 File Name d:\tomcat\webapps\ibisq\sasData\azpop.sas7bdat Release Created 8.0202M0 Host Created WIN_PRO
Alphabetic List of Variables and Attributes
# Variable Type Len Label 3 County Num 8 16 County 5 POPCOUNT Num 8 32 Population count 2 Race Num 8 8 Race 4 Sex Num 8 24 Sex 1 Year Num 8 0 Year 6 agegrp Num 8 40 19 age groups coming with pop data label 8 agegrp1 Num 8 56 9 age groups same as Cancer registry data 9 agepop Num 8 64 Age adjusted age groups label 7 race1 Num 8 48 Race label
Create Configuration File
Create configuration file, mortality.cfg. (See configuration file page)
- Enter homepath and work path (lines 2-3).
- Enter the SAS dataset path and SAS data name (lines 5-9).
- Make sure all IBIS parameters specified in Mort.xml are defined in mortality.cfg file (lines 12-24).
- Define small numbers for suppression (lines 23-24). If you do not want anything suppressed then enter 0 for both lines. If you enter 5 then any number less than 5 will appear as **, with a note on the results page.
- Enter the number for mortality data (numerator).
- Enter the number for population data (denominator).
- All names in <NAME> tag under <SELECTIONS> and <DIMENSION> tags must be defined in this list. (See line 137 of the Query Module Example file.)
- For each variable, the SAS name in the mortality dataset, corresponding name in the module xml file, and the SAS name in population data must be entered. Put "null" if there is nothing to fill in. For example, with if the variable "VarX" is only used in the xml for the count measure (SAS variable "varx") and there is no corresponding population variable, then the line would be:
- The name_html (name used in xml file) is case sensitive. For example, AgeGrp11 is different from ageGrp11.
42 varx VarX null Variable&X num 0
- Type1 is the SAS data type
- num for numeric, such as sex, 1,2,9
- char for character, such as cause, A123-B345
- Type 2 is the type of variable in the IBIS system. (See the comment section (lines 25-38). In this example all variables have a Type 2 of 0. The majority of IBIS variables are one of the following:
Note. Enter the variable length. In this example the length is 4.
Create Function FileCreate function file In general a special function file will be used as it is the most flexible. The standard function file can be used for count, rate and age-adjusted rate if no specific requests on the variables exist. There is still some flexibility on standard function file. (Line numbers refer to the CrudeRate.def standard function file.)
- 1. "type" indicates what measure it is. Use "rate" for the crude rate measure (line 2). "Rate" and "age-adjusted rate" have more lines than "count" (See standard function file).
- 2. Population count variable name in the dataset is specified (line 6).
- 3. XML element output value mapping file must be specified (line 3). The xml_out_map_file statement must have a corresponding "XMLRateNumerDenomLCLUCL.map" file in the same directory as the configuration file and function file.
Create XML Element Output Value Mapping File(See XML element output value mapping file.)
- 1. The "XMLRateNumerDenomLCLUCL.map" must be created to correspond xml_out_map_file specified in the CrudeRate.def function file.
- 2. Copy the XMLResponse.head and XMLResponse.tail files (see Head and Tail files) into the directory with the configuration file, function file and XML element output value mapping file. These two files are identical for all modules. They provide the xml tags that enclose the query results.
Generally, you do not need to modify the back-end configuration and function files for data updates. You do need to modify the xml files, such as mortality.xml (See Updating Modules With New Years for more information).
Procedure for Updating BRFSS DataIn the BRFSS survey some questions are not asked every year. Many of the survey questions are asked every other year or only some years. On the interface only the years the question was asked should be shown (for selection on the query builder page and in the results on the results page). In the module.xml and the function files the administrator needs to specify which years are available for each measure.
If you look at Influenza Vaccination (under quick selection) - and look at the years listed in step 1: you will see that originally the question was only asked in the odd years (1993, 95, 97, 99), however this changed and recently it has been asked every year (2001, 2002, 2003, 2004).
In the module.xml file in the <CONFIGURATION> tag for Influenza Vaccination you will see all the years not available are excluded in the <CRITERIA>, <EXCLUDE> tag:
<CRITERIA> <EXCLUDE> <SELECTIONS> <SELECTION><NAME>Year</NAME><VALUE>1989</VALUE></SELECTION> <SELECTION><NAME>Year</NAME><VALUE>1990</VALUE></SELECTION> <SELECTION><NAME>Year</NAME><VALUE>1991</VALUE></SELECTION> <SELECTION><NAME>Year</NAME><VALUE>1992</VALUE></SELECTION> <SELECTION><NAME>Year</NAME><VALUE>1994</VALUE></SELECTION> <SELECTION><NAME>Year</NAME><VALUE>1996</VALUE></SELECTION> <SELECTION><NAME>Year</NAME><VALUE>1998</VALUE></SELECTION> <SELECTION><NAME>Year</NAME><VALUE>2000</VALUE></SELECTION> </SELECTIONS> </EXCLUDE> </CRITERIA>
In the top portion of the function file for Influenza Vaccination you see the following statement:
f data_where year&in&(1993,1995,1997,1999,2001,2002,2003,2004)This statement tells SAS to only include the years listed (years the question was asked) in the query. This list of years needs to match, exactly, the years listed/shown on the query-builder page to produce accurate results.
Thus, there are two places where years need to be specified: 1) in the module.xml file all unavailable years need to be excluded so that only the available years are shown as selections on the query-builder page, and 2) in the function file all available years need to be listed so that SAS only includes years the question was asked in the query. This makes it so that only years with data can be selected and only years with data can be queried. The years shown on the query-builder page and those listed in the function file need to match exactly to provide accurate results.