Making Rule Sets More Efficient with Lookup Tables
Using lookup tables can dramatically improve the efficiency of your SAS Intelligent Decisioning rules sets. Sadly, I learned this the hard way. Trying to accommodate several key-value pairs without lookup tables led me down a dreadful trail of unnecessary long rule sets containing several IF-THEN statements. It worked, but it was ugly, it took too long to build, and then editing was tedious. In this blog, I will attempt to save you from my mistakes and provide instructions for how to create and use lookup tables in SAS Intelligent Decisioning.
First, what is a lookup table? A lookup table is a table of key-value pairs where each lookup key is unique in the table and is associated with a lookup
value. For some common examples think: SKU/product name, state abbreviation/state name, or ID number/employee name.
Scenario:
A volunteer coordinator needs to buy enough food to serve the volunteers during an upcoming event. Using a custom function discussed in detail in my last blog, Creating Custom Functions in SAS Intelligent Decisioning , we learn how she can calculate how many pizzas to purchase by providing an event date and group size. She orders from a pizza parlor that offers deals depending on the week of the month. To save money, she always buys whichever pizza size is on sale that week. To complete the calculation used by the custom function, we need to determine which pizza size to order and how many slices are in the selected pizza. This is where lookup tables simplify her rule-writing process.
Let’s explore how the rule set would look without lookup tables.
The rule set below uses numerous IF-THEN statements to assign the pizza size based on the week of the month that the event takes place. Notice that the first assignment statement uses SAS date/time functions, INTCK and INTX, to determine which week of the month the event date falls. Although this rule set will get us where we want, it would have been more concise with a lookup table.
Select any image to see a larger version.
Mobile users: To view the images, select the "Full" version at the bottom of the page.
The rule set below produces the same results in a more concise way, utilizing the WeeklySpecials lookup table. The lookup table contains the key-value pair, week number and discounted pizza size.
The IF statement performs the LOOKUP function verifying that the provided week number is found in the WeeklySpecials lookup table. When weeknum is found in the table, the LOOKUPVALUE operator assigns the PiSize variable to the corresponding value from the table.
Once the pizza size is known, we can use the Sectors lookup table to determine how many slices it contains. The Sectors lookup table contains the key-value pair, pizza size and number of slices.
At this moment you are thinking you need lookup tables in your SAS Intelligent Decisioning life, right? I know! Let me show you how lookup tables are created.
From the lookup table view, click New Lookup Table and enter a name and location. The table name must be 250 characters or less. That’s plenty of space to enter a descriptive table name but remember there is also an optional description box available. Labels are optional but recommended as they help any user of the table to better understand the structure.
At this point, you can add table build the table manually or import a table. If you don't have very many entries, manual entry is a good method. Click New Entries. Notice how the optional key and values labels I added make this part more straightforward.
Alternatively, you can click Import to import a CSV file.
If you have a lot of entries this method is easiest. It is recommended that lookup tables have no more than 10,000 rows. Notice on both the spreadsheet and text CSV examples below, no header row is included.
Now that you know why you want to use lookup tables and how to create them, here are a some helpful tips related to lookup table management:
- Before a lookup table is activated, you can add entries, modify values, modify key and value labels, and delete entries. If you need to modify the key, you must delete the entry and add it back with the correction.
- You must activate a lookup table before it can use used in a rule set. Activating the table is as simple as clicking Activate when the lookup table is open.
- Activating a table locks the current version and creates a new unlocked version. The locked version is activated for use within a decision and can no longer be edited. The new unlocked version remains editable until activated.
Once you have the hang of these basics, there are a few more lookup table features with which I recommend you become more familiar. Be sure to visit the User’s Guide for SAS Intelligent Decisioning to learn how to use your lookup table with another environment by exporting, compare lookup tables to each other, generate detailed documentation for a table, or determine which objects in SAS Intelligent Decisioning use a specific table.
