Excel and Epi-Info How-to for Exercise 7

Exercise 7 Part 1: Questions using Site Verification Dataset and Excel:

1. When identifying priority sites, we want to look only at sites that were verified by a completed interview. At how many sites was an interview completed?

   Delete those sites where no interview was completed successfully.

   For this question, you will need to look at "Outcome of visit", question C19 in column E. Select the entire column E.

   Click on the data menu and chose "sort". A pop-up box will appear asking if you want to expand your selection. Choose the "expand the selection" option. A box entitled "Sort" will appear. Make sure that "Outcome of visit" is selected under "Sort by"; if another variable is selected, change the selection to "Outcome of Visit". Make sure to indicate that your list has a header row. Then click "OK".

   The entire dataset will now be sorted by "Outcome of visit" in ascending order. Scroll to the bottom of the dataset and you can see how many interviews were successfully conducted by counting the number of rows with "Outcome of visit" equal to "1" (see Site Verification Form). (Look at the left hand dataset column which counts the number of rows; subtract one for the header row.)

   To delete those sites where no interview was successfully completed, highlight the rows at the bottom where no interview was successfully completed. Then, from the Edit pull-down menu at the top of the page, select "Delete". All information in the selected rows should disappear.

2. One of the criteria for priority sites is that they are well known or popular places that draw large numbers of socializing individuals. To establish a list of popular sites, make a list that includes sites that were reported by 10 or more community informants and sites where at least 100 people were socializing during a busy time. Interventions aim to reach a large number of people and so it is reasonable to include the sites with the largest number of patrons among the priority sites. To establish a list of popular sites, make a list that includes sites that were reported by 10 or more community informants and sites where at least 100 people were socializing during a busy time.

   First sort the dataset (as described above in number 1) by the variable for number of community informant reports to select those sites reported by 10 or more informants. Copy the names of these sites. Then, resort the dataset by total number of people socializing during a busy time and select sites where at least 100 people were socializing. Copy these site names into a second list. Now, combine the two lists and eliminate any duplicates.

3. Priority sites often include sites where youth can be reached. In some countries, one of the most important prevention strategies is to keep the epidemic from reaching the next generations by focusing on youth. List those sites where most of the patrons during busiest times are either young men or young women aged 15-19.

   To look at both genders together, select the entire dataset by clicking in the upper right corner. Then go to the data menu and select sort. When the sort pop-up box appears, make sure to indicate that the dataset has a header row.

   In the white box under "Sort by", choose the variable about socializing females aged 15 to 19. Indicate that you want the sort order to be "descending", because you are looking for values of 2 for most (see questionnaire). In the white box under "Then by", choose the variable about socializing males aged 15 to 19. Indicate that you want the sort order to be "descending", also. Then click "OK".

   At the top of the page you will see sites where most males or most females socializing, or both, were aged 15-19. Copy the site names for these sites and make a list.

4. Lastly, priority sites usually include sites where it seems evident that people are meeting new sexual partners at the site and that the people who visit the site are likely to have new and multiple partnerships. Establish a list of risky behavior sites where any of the following reportedly occur, according to the person knowledgeable about the site interviewed in Step 3:

   • Someone onsite helps partners to hook up or link up

   • Female sex workers solicit customers

   • Partners who meet onsite have sex onsite

   • Female staff meet new sexual partners

   • Male staff meet new sexual partners

   • Women appear to be buying or selling sex

   • Men appear to be buying or selling sex

   Once you have your complete list of sites, compile an abbreviated list of sites where at least 3 risky behaviors from this list were reported.

   To identify sites with each listed characteristic, sort the dataset by the variable for that characteristic, following the directions for sorting described above. You will then be able to easily select the list of sites for each characteristic. Make a list of sites that appear in 3 or more lists.

5. To evaluate which 25 sites should be priority sites for intervention, you will need to consider the priority site characteristics and lists that you have developed above. Compile a final table of sites that were either popular (from number 2 above), sites where most people socializing were youth (from number 3 above), and sites with at least 3 risky behaviors (from number 4 above). Make a column for each priority site characteristic so that you can identify those sites with more than one priority characteristic. Which 25 sites would you choose, based on this information? (Note that there are many ways of determining priority sites; this exercise has guided you through one possible way, but you could easily use a different cut-off for site size, or different risk behaviors, and arrive at a different answer!)

   Excel isn't necessary for this question.

Part 2: Questions using Socializing Individuals Dataset and Epi Info:

First, open the Epi Info Analysis program. From the menu on the left side of your computer screen, select Read(Import) to open the Socializing Individuals dataset (indiv.mdb) for analysis. A pop-up box will appear. Make sure that indiv.mdb is listed under Current Project. If it isn't, then click the "Change Project" button at the bottom of the screen and direct the program to indiv.mdb.

Under Data Formats, make sure that "Epi 2000" is selcted. Under Show, make sure that "All" is selected, and under "All", click on "indiv". Then click the button that says "OK". You are now ready to proceed with analysis.

1. Sex for money: Some sites where patrons were interviewed may be more likely than others to attract patrons engaged in sex work. Using the data from interviews with people socializing at the site, list all of the sites where 5 or more people interviewed at the site reported exchanging sex for money in the past twelve months.

   For this question you will need to look at both the variable for site name (D4) and the variable for whether or not a person gave or received money for sex in the past 12 months (D53a). From the left menu, under Statistics, click on Tables. A pop-up menu will appear on your screen. Under Exposure Variable, select variable D4 and under Outcome Variable select D53a. Then click "OK" and a table will appear that you can use to select sites for your list. (You will need to refer to the questionnaire for a key to the numeric coding used in the dataset.)

2. New partners: Some sites may be more likely to attract men and women with high rates of new sexual partner acquisition. List all sites where more than 10 people have at least one new partner in the past 4 weeks.

   For this question you will need to look at both the variable for site name (D4) and the variable for whether or not a person had at least one new partner in the past 4 weeks (D27). From the left menu, under Statistics, click on Tables. A pop-up menu will appear on your screen. Under Exposure Variable, select variable D4 and under Outcome Variable select D27. Then click "OK" and a table will appear that you can use to select sites for your list. (You will need to refer to the questionnaire for a key to the numeric coding used in the dataset.)

3. Low condom use: The need for condom promotion messages may also vary by site. To get an insight into the extent to which this may be true in the Port City, list all sites where 5 or more people didn't use a condom the first time they had sex with their most recent new sexual partner in the past year.

   For this question you will need to look at both the variable for site name (D4) and the variable for whether or not a person used a condom the first time they had sex with their most recent new sexual partner in the past year (D30). From the left menu, under Statistics, click on Tables. A pop-up menu will appear on your screen. Under Exposure Variable, select variable D4 and under Outcome Variable select D30. Then click "OK" and a table will appear that you can use to select sites for your list. (You will need to refer to the questionnaire for a key to the numeric coding used in the dataset.)

4. STD Symptoms: Many people who are infected with a sexually transmitted disease (STD) do not know that they are infected because they do not have any symptoms of infection. Some people with symptoms, however, never visit a clinic to be treated. Review the data to determine if there are sites that may need a visit by an outreach nurse to encourage proper health seeking behaviors when symptoms of STD occur. List all sites where at least 5 men reported having STD symptoms in the past four weeks.

   Here you will need to select men who had pain on urination (D39a) or unusual discharge (D39b) or sores (D39c) and count them. You can select these people by choosing "Select" under "Select/If" in the left hand area of your screen. In the white box that appears, type: D39a=1 OR D39b=1 OR D39c=1. You are specifying that you want to select all men who answered yes (coded "1") to either D39a, D39b, or D39c. Now, until you "deselect", all of your analyses will be restricted to men who reported at least 1 STI symptom.

   To find sites where at least 5 men reported at least 1 STI in the past 4 weeks, you will want to make a frequency table of site names. Select "Frequencies", found under "Statistics" in the left hand column on your screen. Select the site name variable (D4) in the white box under "Frequency of" and click "OK". The table produced will assist you in forming the list.

   When you are finished, don't forget to "deselect" by clicking on "Cancel Select" under "Select/If". Otherwise, your analysis will continue to be restricted to men who had an STI symptom in the past 4 weeks!

5. This does not require Epi Info.