8 Replies Latest reply on May 10, 2018 7:05 AM by Bryan Egan

    Trouble with a Calculated Field and Question on Blended Data

    Bryan Egan

      Hi!

       

      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!

        • 1. Re: Trouble with a Calculated Field and Question on Blended Data
          Deepak Rai

          Remove aggregation (SUM) from your formula

           

          A+B+C/D

          On blending question, if possible join your data source on same field which you wanted to use for blending

          1 of 1 people found this helpful
          • 2. Re: Trouble with a Calculated Field and Question on Blended Data
            Bryan Egan

            The two variables I am most interested in for the blended set is Grade and there is no Enrolled variable since this is the essentially the entire list of enrolled classes.

             

            So just for example this is how both look.

             

            Set 1

            Student          A Grade   B Grade   C Grade   Enrolled   Success        Year

            A                        1               2               3               8               0.75           15

            B                        0               5               1               6               1                15

            C                        6               0               0               6               1                15

             

             

            Blended set:

            Student             Course             Grade       Year

            A                           Math                A              15

            A                           English            B              15

            A                          Math 2              B              15

            A                          Writing              C              14

            A                          Film                  C               12

            B                          Math                 B               15

            C                          English             A                14

            C                          Music               C                13

             

             

            With both sets broken out by year. The first set is the list of students by year and the total number of classes they took. The second is the breakdown in classes that was taken. Would I blend it on student in this case?

            • 3. Re: Trouble with a Calculated Field and Question on Blended Data
              Jim Van Sistine

              It's an order of operations problem (PEMDAS )

               

              SUM(A)+SUM(B)+SUM(C)/SUM(Enrolled) will perform the division first.  That's why you see 1 A, 0 B, 1 C resolve to 1.5.  1+0+(1/2)

               

              You need parentheses in your numerator.

              ( SUM(A+B+C) ) / sum(Enrolled)

               

              Hope this helps!

              Jim

              1 of 1 people found this helpful
              • 4. Re: Trouble with a Calculated Field and Question on Blended Data
                Bryan Egan

                Wow I feel embarrassed about that. I do not know how I completely missed that. Thanks a bunch!

                 

                Any idea on the second part?

                • 5. Re: Trouble with a Calculated Field and Question on Blended Data
                  Jim Van Sistine

                  No worries, it happens. 

                   

                  For the percentage of grades that were A's question, wouldn't your set 1 give you that information by doing SUM(A)/SUM(Enrolled)?  Or do you need that information at the course level (i.e Math)?

                   

                  A blend will not give you all of the rows from the more detailed file, it has to aggregate it to the level of the primary one.  So, in your case with file 1 at the student level, a blend can only give you 1 result per student.  If you try to grab course, you'll see a * value.  If you need to know the % of As given in Math, I'd probably just use the detailed file.  Or create an aggregated crosstab file similar to your student file, only by course.

                  • 6. Re: Trouble with a Calculated Field and Question on Blended Data
                    Bryan Egan

                    The goal would be to create a stacked bar graph showing the percentage of courses in each layer (A, B, C, others) all combining into the total number. I can right now show a graph showing the percentage of each grade separate, but it looks quite cumbersome over many years since it shows 4 lines per year. I would like to have a stacked bar chart for each year to highlight each grades percentage and ease of comparison to other years.

                    • 7. Re: Trouble with a Calculated Field and Question on Blended Data
                      Jim Van Sistine

                      Try using Measure Names/Values to build your stacked bar.

                       

                      Something like this?

                       

                      Workbook (v10.5) is attached.

                      1 of 1 people found this helpful
                      • 8. Re: Trouble with a Calculated Field and Question on Blended Data
                        Bryan Egan

                        Wow thank you very much. This worked perfectly. No need to use blended data whatsoever.