I have the following data with 20+ measures. Raw data shown here is a sample with only one measure. WIth Tabulate I can get subtotal for each site for all levels of Var2 (0,1,2,3) and level of Var1 (A, B). For example, the subtotal for Site1=461 (438+19+2+2). I am comparing the percentage of measure for each level of Var2 between the level of Var1 for each Site. Table1 presents subtotal for each site for each level of Var2 and Var1. Table 2 shows the percentage of the total for each level of Var2 and Var1. This is the desired percentage that I like to compare and contrast. In this example (Table 2), there is not much variability between A & B (Var1 level) across each level of Var2 (0,1,2,3) for each site. However, I need to perform similar comparisons across 20+ measures. I appreciate any guidance/help to calculate the percentages.
Thank you,
Raw Data
Site | Var1 | Var2 | Measure |
Site1 | A | 0 | 438 |
Site1 | A | 1 | 19 |
Site1 | A | 2 | 2 |
Site1 | A | 3 | 2 |
Site2 | A | 0 | 321 |
Site2 | A | 1 | 0 |
Site2 | A | 2 | 0 |
Site2 | A | 3 | 0 |
Site1 | B | 0 | 57 |
Site1 | B | 1 | 4 |
Site1 | B | 2 | 0 |
Site1 | B | 3 | 0 |
Site2 | B | 0 | 37 |
Site2 | B | 1 | 1 |
Site2 | B | 2 | 0 |
Site2 | B | 3 | 0 |
Table 1
| Site1 | | | | | Site2 | | | | |
| Var2 | | | | | Var2 | | | | |
| 0 | 1 | 2 | 3 | | 0 | 1 | 2 | 3 | |
Var1 | N | N | N | N | Total | N | N | N | N | Total |
A | 438 | 19 | 2 | 2 | 461 | 321 | 0 | 0 | 0 | 321 |
B | 57 | 4 | 0 | 0 | 61 | 37 | 1 | 0 | 0 | 38 |
All | 495 | 23 | 2 | 2 | 522 | 358 | 1 | 0 | 0 | 359 |
| Site1 | | | | | Site2 | | | | |
| Var2 | | | | | Var2 | | | | |
| 0 | 1 | 2 | 3 | | 0 | 1 | 2 | 3 | |
Var1 | % | % | % | % | Total | % | % | % | % | Total |
A | 95% | 4% | 0% | 0% | 100% | 100% | 0% | 0% | 0% | 100% |
B | 93% | 7% | 0% | 0% | 100% | 97% | 3% | 0% | 0% | 100% |
All | 95% | 4% | 0% | 0% | 100% | 100% | 0% | 0% | 0% | 100% |