How to Create Flag Fields with Hive Arrays
Creating a field to flag specific values for a field represented by many different fields (i.e., ICD Diagnosis Codes, Condition Codes, etc.), can be done by putting the fields into an array and searching the entire array for a particular value.
The syntax below shows an example of putting the potential 30 condition code fields into an array and create a flag that identifies if any of the field values is equal to a particular value of interest.
- It creates and calls a macro that selects and renames condition code field 1 through 30 into a macro variable that is called to extract from Hive claims table.
- Then that table is used to create a new Hive Table that uses the Hive array function and searches for a particular value ('44' in this example) throughout the array.
- The output value is set to 1 if found, or 0 if not found in the newly created field (cond44).