wiki:HowToUpdateASurveyQueryModuleForANewYearOfData

How to Update a Survey Query Module for a New Year of Data: BRFSS

In general, datasets will be updated annually. This page will describe the basic procedure for updating a query module with a new year of survey data. In general, the following steps need to be taken (order between front end and back end is not important):


Web Page/Front? End

  1. Create a new dataset in SAS without formats. To make sure that there are no formats in the data set, run a proc contents.
  1. WinSCP3 and login.

Set your directory to /srv/ibis-q/sasData/final

  1. Double click on the highlighted blue bar at the top of the right-hand side window in WinSCP3. This will bring up a new window entitled “Open Directory”
  2. Highlight /srv/ibis-q/sasData/final by clicking on it
  3. Hit OK
  1. Put the new data set in the srv/ibis-q/sasData/final folder:
    1. Navigate to your data set in the correct directory in the left-hand window
    2. Go up to options/preferences/transfer and make sure it is set to ‘binary’
    3. Rename the data set. Every year we write over the old data set and use the same name.
    4. Drag over to /srv/ibis-q/sasData/final. Click “copy” then “yes” to overwrite the file. Close WinSCP3.
  1. Update your excel codebook. You want to pay particular attention to any changes you have made.
  1. Print a copy to refer to as you work. It can help to tape the pages together to form one large sheet with everything on it.
  1. Add the new year to the year dimension file or dimension files if there is more than one year selection for the module. For example: Go into C:\tomcat\webapps\ibisph-view\xml\query\module\brfss\dimension and open the year.xml. Add the current year at the bottom of the list. Save. Close.
<?xml version="1.0" encoding="ISO-8859-1"?>

<DIMENSION>
	<NAME>Year</NAME>
	<TITLE>Year</TITLE>
	<VALUES>
		<VALUE>1989</VALUE> 		<VALUE>1990</VALUE>
		<VALUE>1991</VALUE>		<VALUE>1992</VALUE>
		<VALUE>1993</VALUE>		<VALUE>1994</VALUE>
		<VALUE>1995</VALUE>		<VALUE>1996</VALUE>
		<VALUE>1997</VALUE>		<VALUE>1998</VALUE>
		<VALUE>1999</VALUE>		<VALUE>2000</VALUE>
		<VALUE>2001</VALUE>		<VALUE>2002</VALUE>
		<VALUE>2003</VALUE>		<VALUE>2004</VALUE>
		<VALUE>2005</VALUE>		<VALUE>2006</VALUE>
		<VALUE>2007</VALUE>
		<VALUE>.<TITLE>Total</TITLE><NOT_ASSOCIATED_FLAG/></VALUE>
	</VALUES>
</DIMENSION>
  1. Do the same thing with the YearSarea.xml. In Utah we have small area available for 2001-2007.
<?xml version="1.0" encoding="ISO-8859-1"?>

<DIMENSION>
	<NAME>YearSarea</NAME>
	<TITLE>Year</TITLE>
	<VALUES>
		<VALUE>2001</VALUE>
		<VALUE>2002</VALUE>
		<VALUE>2003</VALUE>
		<VALUE>2004</VALUE>
		<VALUE>2005</VALUE>
		<VALUE>2006</VALUE>
		<VALUE>2007</VALUE>
		<VALUE>.<TITLE>Total</TITLE><NOT_ASSOCIATED_FLAG/></VALUE>
	</VALUES>
</DIMENSION>
  1. The next step is to make sure that the proper years of data will show up in the query builder pages. This step is unique to survey data because the questions included on the survey can vary from year to year. On BRFSS in particular, there are many modules that are included on the survey every other year. To do this, you will need to refer to your excel codebook to see when a question was asked. I will use ‘Clinical Breast Examination’ (A clinical breast exam is when a doctor, nurse, or other health professional feels the breast for lumps. Have you ever had a clinical breast exam? How long has it been since your last breast exam?) as an example here.
    1. From the excel codebook I determine that BreExamClinic was asked in 1990-2000, 2002, 2004, 2006. We therefore need to make sure that 1989, 2001, 2003, 2005 and 2007 are being excluded from the query builder page.
    2. Next go to the following folder: C:\tomcat\webapps\ibisph-view\xml\query\module\brfss and update the *.xml files. For BreExamClinic the first file is BRFSSCrude40.xml.
  1. Open the BRFSSCrude40.xml file
  2. Search on ‘BreExamClinic’
  3. Scroll down to the <CRITERIA> section and add 2007 to the exclusions. 1989, 2001, 2003, 2005 should already appear as exclusions.

 <CRITERIA>
<EXCLUDE>
		<SELECTIONS>
			<SELECTION>
				<NAME>Year</NAME>
				<VALUE>1989</VALUE>
			</SELECTION>
			<SELECTION>
				<NAME>Year</NAME>
				<VALUE>2001</VALUE>
			</SELECTION>
			<SELECTION>
				<NAME>Year</NAME>
				<VALUE>2003</VALUE>
			</SELECTION>
			<SELECTION>
				<NAME>Year</NAME>
				<VALUE>2005</VALUE>
			</SELECTION>
			<SELECTION>
				<NAME>Year</NAME>
				<VALUE>2007</VALUE>
			</SELECTION>
		</SELECTIONS>
	</EXCLUDE>
</CRITERIA>

  1. Scroll down and make the same changes to the exclusions for the following sections:
    1. <NAME>BreExamClinicQuartile</NAME>
    2. <NAME>BreExamClinicHighLowSame</NAME>
    3. and any other sections with contain BreExamClinic
  1. Save changes and close BRFSSCrude40.xml
  1. Repeat the process for the BRFSSCrude40Sarea.xml
  1. Continue this process for each of your IBIS measures.



Data Set/Backend?

  1. Open WinSCP3 and login.
  1. Set your directory to /srv/ibis-q/modules
  1. Double click on the highlighted blue bar at the top of the right-hand side window in WinSCP3. This will bring up a new window entitled “Open Directory”
  2. Highlight /srv/ibis-q/modules by clicking on it
  3. Hit OK
  4. You will now be in the correct directory
  1. Get into the BRFSS folder to view the *.def files
  1. Open every *.def file and make sure the years are correct in the “data_where” statement
  1. Each Measure has nine *.def files you will need to update. Using the above example, the following files would need to be updated for ‘Clinical Breast Examination’:
    1. BreExamClinic.def
    2. BreExamClinicAgeAdj.def
    3. BreExamClinicAgeAdjHighLowSame.def
    4. BreExamClinicAgeAdjHighLowSameRace.def
    5. BreExamClinicAgeAdjQuartile.def
    6. BreExamClinicAgeAdjQuartileRace.def
    7. BreExamClinicAgeAdjRace.def
    8. BreExamClinicHighLowSame.def
    9. BreExamClinicQuartile.def
  1. Below is an example of the code from the BreExamClinic.def:
f label Percentage&of&Persons&Who&Reported&Current&Cigarette&Smoking
f type special_survey
f include TotalCrudeSurv.sas
f data_where year&in(1990,1991,1992,1993,1994,1995,1996,1997,1998,1999,2000,2002,2004,2006)&and&sex=2
#f pop_where sex=1
#########################################
--------BoNdArY--------
1 script 
%include %include%;
%crudrt(bpe2yr,weight);
--------BoNdArY--------
f out_variable percent
#################################################
# definition for output file
f xml_out_map_file XMLPercentNumerLCLUCL.map
--------BoNdArY--------
f out_detail lbl_not_used__see_xml_out_map_file 
  percent 7.4
 n 15.0
 lower 7.4
 upper 7.4
--------BoNdArY--------



Extensively test all changes you have made to the query builder pages on your local host. You need to do this for each measure!

  1. Start Tomcat
  1. Make sure that the appropriate years show up and that the correct data is returned for the following:
    1. Quick Selection - Main Table
    2. Advanced Selection – LHD – Crude Rates – Main Table
    3. Advanced Selection – LHD – Crude Rates – Quartile
    4. Advanced Selection – LHD – Crude Rates – High Low Same
    5. Advanced Selection – LHD – Age Adjusted Rates – Main Table
    6. Advanced Selection – LHD – Age Adjusted Rates – Quartile
    7. Advanced Selection – LHD – Age Adjusted Rates – High Low Same

Unable to test these completely on local host, but need to at least make sure the correct year appears in the drop down.

  1. Advanced Selection – Small Areas – Crude Rates – Main Table
  2. Advanced Selection – Small Areas – Crude Rates – Quartile
  3. Advanced Selection – Small Areas – Crude Rates – High Low Same
  4. Advanced Selection – Small Areas – Age Adjusted Rates – Main Table
  5. Advanced Selection – Small Areas – Age Adjusted Rates – Quartile
  6. Advanced Selection – Small Areas – Age Adjusted Rates – High Low Same

Commit files from C:\tomcat\webapps\ibisph-view\xml\query\module\brfss to the repository

  1. Using windows explorer, navigate to C:\tomcat\webapps\ibisph-view\xml\query\module\brfss
  2. Right-click anywhere inside of the explorer window
  3. Select ‘SVN Commit’
  4. Enter a message that you updated BRFSS for 2007
  5. Hit OK to commit



Back to IBIS HowTo Index

Last modified 9 years ago Last modified on 10/01/09 16:36:55