So I am having trouble with a data set. WI am trying to create a calculated field where I am summing up three different numbers and dividing it by a fourth. The data is a summary of courses for each student by semester. For example, student 1 took 10 courses (Enrolled) in the Fall. Of those, they received an A (A Grade Success) in 3 of the courses, a B (B Grade Success) in 3 other courses, and a C (C Grade Success) in 2 of the remaining courses. The two other courses the student either failed or withdrew. Therefore, the goal would be for (# of A's) + (# of B's) + (# of C's) / Total Enrolled or (3 + 3 + 2) / 10 = .8 Success Rate.
But when I look at the calculation, it is way off. For instance, for person one, they have 1 in A success, 0 in B success, and 1 in C success and a 2 in Enrolled. This should give me a Success of 1. Instead I am getting a success of 1.5. A separate one has 6 in A success and 4 in B success and a 10 in Enrolled. Meaning out of 10 classes the student took, they got an A grade in 6 and a B in the other 4. The success rate should again be 1. But it is showing 10. Is there something I am doing wrong here?
My second question pertains to blended data although if someone could fix it for me without using blended data that would be nice.
In my set as noted above, I have course success data. I have for each student in the file the total number of courses they enrolled in (Enrolled), the number of courses they received an A in (A Grade Success), the number of courses they received a B in (B Grade Success), and the number of courses they received a C in (C Grade Success). Above I am trying to find out the overall success rate. So the percentage of courses that students passed in a given semester.
I would also like to look at the given percentages for each grade if possible. For example, using the example I noted earlier, Student A received an A grade in 30% of his classes for the semester, received a B in 30% of his classes, and a C in 20% of his classes. He failed or did not complete the other two (20%). The goal would be to aggregate those numbers for all students. So 40% of grades in the year were A's, or 70% of grades were B's, etc. I have as a SAS file the breakdown for each class and the subsequent grade. But it is massive in comparison to this aggregated one. There were over 700,000 courses taken for one year for instance and I am looking at 5 years worth of data. So ideally, looking at an aggregated version would be better. Would it be best to blend this data set with my above one to find the percentages for each grade? Or is it possible to do with the data I have on hand?
If I do have to blend, how should I go about blending? Do I need a specific variable to link the two together?
Thanks a lot!