Case using contains and value conversion

mc1392

I have two columns:

1. primary assembly

2. secondary assembly.

 

A person has 1 primary assembly, but at least 1 secondary assembly.

I am trying to do something like

if primaryAssembly contains "ABC" or if secondaryAssembly contains "ABC" then 1

 

Here is the case I am trying but the resulting values are totally not right:

 

(CASE

 when `PrimaryAssembly` like '%Allergy Immunology & Inflammation%' or `SecondaryAssemblies` like '%Allergy Immunology & Inflammation%' then 1
 when `PrimaryAssembly` like '%Behavioral Science and Health Services Research%' or `SecondaryAssemblies` like '%Behavioral Science and Health Services Research%' then 2
 when `PrimaryAssembly` like '%Clinical Problems%' or `SecondaryAssemblies` like '%Clinical Problems%' then 3
end
)

 

I am getting numbers all over the place. not a true conversion in value.

 

How can I can values in both columns, and then assign a resulting value?

then convert that vlaue to another value?

 

so if "ABC" leads to 1 then 1 should then be converted into "This has ABC".

 

I am trying to get a count of each type, whether in the primary or the secondary.