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

# Simplifying String Calculation

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

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

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

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