Skip directly to searchSkip directly to the site navigationSkip directly to the page's main content

IBIS-Q System Documentation - Create Back-end Query File

Back-End Query Files

This 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.

Environment Containing the Interface and Back-End Files
Figure 3-1. Environment Containing the Interface and Back-End Files

Suggested Practices
  1. Establish a schedule or routine by which the various data stewards release their new datasets to you.
  2. Compile naming conventions and standards list to simplify naming the variables and help maintain consistency.
  3. 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 Files

The 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:

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.
  1. 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
    1. Variables: Year, sex, and age
    2. Display:
      1. Year will be shown as single years
      2. Sex will have Male, Female and unknown.
      3. 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+.
  2. Check variable availability in both main data and population data.
    1. 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
    2. 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.
    3. Sex is in both mortality and population data.
  3. Regroup variables if needed.
    The variable and variable values specified in xml file should have a corresponding variable and value in the SAS dataset.
    1. 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
    2. 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.
  4. Make variable numeric if possible
    All three variables are numeric in datasets.
  5. SAS data contents
    1. Mortality data contents

    2. CONTENTS Procedure
      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
      
    3. Arizona population data contents

    4. CONTENTS Procedure
      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.
    1. Enter the number for mortality data (numerator).
    2. Enter the number for population data (denominator).
  • Enter variable list (lines 41-43).
    1. 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.)
    2. 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:
    3. 42 varx VarX null Variable&X num 0 
    4. The name_html (name used in xml file) is case sensitive. For example, AgeGrp11 is different from ageGrp11.
  • Enter variable type and length (lines 41-43). The comment section (lines 25-38) explain the available options for variable type.
    1. Type1 is the SAS data type
      1. num for numeric, such as sex, 1,2,9
      2. char for character, such as cause, A123-B345
    2. 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:
      1. Proxy variables = 10. For example, YearProxy.
      2. Dynamic variables = 3. For example, Year99-Pres.
      3. Predefined numeric variable = 0. For example, Sex, AgeGrp11
      4. Variable in standard population dataset = 7. For example, stdpop (used in age-adjusted measures).

Note. Enter the variable length. In this example the length is 4.

Create Function File

Create 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.)
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 Data

In 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.

For example:
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.
The information provided was retrieved on: Sat, 08 August 2020 16:58:28.

Content updated: Mon, 3 Mar 2008 11:40:27 MST