3 Replies Latest reply on Nov 19, 2013 12:29 PM by Daniel Hassler

    Simplifying String Calculation

    Daniel Hassler

      I work at a University and we are attempting to calculate standard IPEDS ethnicity categories based on the data as it comes out of our database. The hierarchy definition works like this:

       

      • If a student International (and not a permanent resident), they are classified as Non-Resident Alien
      • If they are Hispanic, they are classified as Hispanic
      • If they have two or more ethnicities, they are classified as Two or More
      • Everyone else is put in to the category that best fits

       

      I have created a calculation that works, but it seems repetitive. My question is whether there is a more efficient way to calculate the International students. The existence of nulls in the data as well as the exclusion of permanent residents seemingly requires me to repeat the same calculation multiple times. Thank you in advance!

       

      Dan

      --

      IF ISNULL ([Citizen Country]) THEN

          IF CONTAINS ([Ethnicity],"hispanic") THEN "Hispanic"

          ELSEIF CONTAINS ([Ethnicity],";") THEN "Two or More"

          ELSEIF CONTAINS ([Ethnicity],"two") THEN "Two or More"

          ELSEIF CONTAINS ([Ethnicity],"white") THEN "White"

          ELSEIF CONTAINS ([Ethnicity],"black") THEN "Black"

          ELSEIF CONTAINS ([Ethnicity],"asian") THEN "Asian"

          ELSEIF CONTAINS ([Ethnicity],"amindian") THEN "American Indian"

          ELSEIF CONTAINS ([Ethnicity],"native") THEN "American Indian"

          ELSEIF CONTAINS ([Ethnicity],"hawaiian") THEN "Hawaiian"

          ELSE "Not Specified"

          END

      ELSEIF [Visa Type] = "PR" THEN

          IF CONTAINS ([Ethnicity],"hispanic") THEN "Hispanic"

          ELSEIF CONTAINS ([Ethnicity],";") THEN "Two or More"

          ELSEIF CONTAINS ([Ethnicity],"two") THEN "Two or More"

          ELSEIF CONTAINS ([Ethnicity],"white") THEN "White"

          ELSEIF CONTAINS ([Ethnicity],"black") THEN "Black"

          ELSEIF CONTAINS ([Ethnicity],"asian") THEN "Asian"

          ELSEIF CONTAINS ([Ethnicity],"amindian") THEN "American Indian"

          ELSEIF CONTAINS ([Ethnicity],"native") THEN "American Indian"

          ELSEIF CONTAINS ([Ethnicity],"hawaiian") THEN "Hawaiian"

          ELSE "Not Specified"

          END

      ELSEIF [Citizen Country] <> "USA" THEN "Non-Resident Alien"

      ELSEIF CONTAINS ([Ethnicity],"alien") THEN "Non-Resident Alien"

      ELSEIF CONTAINS ([Ethnicity],"hispanic") THEN "Hispanic"

      ELSEIF CONTAINS ([Ethnicity],";") THEN "Two or More"

      ELSEIF CONTAINS ([Ethnicity],"two") THEN "Two or More"

      ELSEIF CONTAINS ([Ethnicity],"white") THEN "White"

      ELSEIF CONTAINS ([Ethnicity],"black") THEN "Black"

      ELSEIF CONTAINS ([Ethnicity],"asian") THEN "Asian"

      ELSEIF CONTAINS ([Ethnicity],"amindian") THEN "American Indian"

      ELSEIF CONTAINS ([Ethnicity],"native") THEN "American Indian"

      ELSEIF CONTAINS ([Ethnicity],"hawaiian") THEN "Hawaiian"

      ELSE "Not Specified"

      END

      --

        • 1. Re: Simplifying String Calculation
          kettan

          It is not easy to test this without data and more understanding of your data, but I have a feeling this is enough:

          .

          IF ISNULL ([Citizen Country]) OR [Visa Type] = "PR" THEN
               IF CONTAINS ([Ethnicity],"hispanic")     THEN "Hispanic"
               ELSEIF CONTAINS ([Ethnicity],";")        THEN "Two or More"
               ELSEIF CONTAINS ([Ethnicity],"two")      THEN "Two or More"
               ELSEIF CONTAINS ([Ethnicity],"white")    THEN "White"
               ELSEIF CONTAINS ([Ethnicity],"black")    THEN "Black"
               ELSEIF CONTAINS ([Ethnicity],"asian")    THEN "Asian"
               ELSEIF CONTAINS ([Ethnicity],"amindian") THEN "American Indian"
               ELSEIF CONTAINS ([Ethnicity],"native")   THEN "American Indian"
               ELSEIF CONTAINS ([Ethnicity],"hawaiian") THEN "Hawaiian"
               ELSE "Not Specified"
               END
          ELSEIF [Citizen Country] <> "USA"             THEN "Non-Resident Alien"
          ELSEIF CONTAINS ([Ethnicity],"alien")         THEN "Non-Resident Alien"
          ELSE "Not Specified"
          END
          
          
          
          
          
          1 of 1 people found this helpful
          • 2. Re: Simplifying String Calculation
            kettan

            Or maybe this:

            ..

            IF [Citizen Country] <> "USA"            THEN "Non-Resident Alien"
            ELSEIF CONTAINS ([Ethnicity],"alien")    THEN "Non-Resident Alien"
            ELSEIF CONTAINS ([Ethnicity],"hispanic") THEN "Hispanic"
            ELSEIF CONTAINS ([Ethnicity],";")        THEN "Two or More"
            ELSEIF CONTAINS ([Ethnicity],"two")      THEN "Two or More"
            ELSEIF CONTAINS ([Ethnicity],"white")    THEN "White"
            ELSEIF CONTAINS ([Ethnicity],"black")    THEN "Black"
            ELSEIF CONTAINS ([Ethnicity],"asian")    THEN "Asian"
            ELSEIF CONTAINS ([Ethnicity],"amindian") THEN "American Indian"
            ELSEIF CONTAINS ([Ethnicity],"native")   THEN "American Indian"
            ELSEIF CONTAINS ([Ethnicity],"hawaiian") THEN "Hawaiian"
            ELSE "Not Specified"
            END
            
            
            
            
            
            
            
            • 3. Re: Simplifying String Calculation
              Daniel Hassler

              Kettan,

               

              Thank you, this was close to what I needed. I ended up with the calculation below, which still has a little duplication, but not as much.

               

              IF ISNULL ([Citizen Country]) OR [Visa Type] = "PR" THEN

                  IF CONTAINS ([Ethnicity],"hispanic") THEN "Hispanic"

                  ELSEIF CONTAINS ([Ethnicity],";") THEN "Two or More"

                  ELSEIF CONTAINS ([Ethnicity],"two") THEN "Two or More"

                  ELSEIF CONTAINS ([Ethnicity],"white") THEN "White"

                  ELSEIF CONTAINS ([Ethnicity],"black") THEN "Black"

                  ELSEIF CONTAINS ([Ethnicity],"asian") THEN "Asian"

                  ELSEIF CONTAINS ([Ethnicity],"amindian") THEN "American Indian"

                  ELSEIF CONTAINS ([Ethnicity],"native") THEN "American Indian"

                  ELSEIF CONTAINS ([Ethnicity],"hawaiian") THEN "Hawaiian"

                  ELSE "Not Specified"

                  END

              ELSEIF [Citizen Country] <> "USA" THEN "Non-Resident Alien"

              ELSEIF CONTAINS ([Ethnicity],"alien") THEN "Non-Resident Alien"

              ELSEIF CONTAINS ([Ethnicity],"hispanic") THEN "Hispanic"

              ELSEIF CONTAINS ([Ethnicity],";") THEN "Two or More"

              ELSEIF CONTAINS ([Ethnicity],"two") THEN "Two or More"

              ELSEIF CONTAINS ([Ethnicity],"white") THEN "White"

              ELSEIF CONTAINS ([Ethnicity],"black") THEN "Black"

              ELSEIF CONTAINS ([Ethnicity],"asian") THEN "Asian"

              ELSEIF CONTAINS ([Ethnicity],"amindian") THEN "American Indian"

              ELSEIF CONTAINS ([Ethnicity],"native") THEN "American Indian"

              ELSEIF CONTAINS ([Ethnicity],"hawaiian") THEN "Hawaiian"

              ELSE "Not Specified"

              END