Using the Automatic Lookup Feature

How to Implement the Automatic Lookup Feature

You can implement automatic lookup for any column in a table that can be accessed from the SQL Query window. Automatic lookup causes an action, that varies according to the lookup strategy, to automatically occur when that column and an operator are selected from the WHERE EXPRESSION window.

In this example, you implement automatic lookup by creating a SAS data set called a lookup table. You then insert a set of values into the lookup table for each column for which you want a Lookup Values window to be displayed.

Lookup Strategies

You can specify any one of five lookup strategies for each column:

V (Value)

automatically retrieves the distinct values of the column that has been specified in the lookup table. The distinct values appear in a Lookup Values window in the WHERE EXPRESSION window when you have selected both the specified column from the Available Columns window and an operator from the menu that subsequently appears. When you select one or more values, these values are inserted into the WHERE expression. The EQ operator is converted to the IN operator to allow multiple selections.

T (Table)

reads a table and displays the values of all the columns in the Lookup Values window. The first column in the table must contain the values that are needed in the WHERE expression. You can use other columns to provide descriptive information.

If the first column contains a small number of distinct rows in comparison to the number of rows in the table, then the distinct values and their descriptions can be stored in a separate table. This table can be used to display automatic lookup values for the subset conditions.

L (List)

enables you to select specific columns from a table for display in the Lookup Values window. The first column that you specify must contain the values that are needed for the WHERE expression. You can use other columns to provide descriptive data values.

F (Format)

displays column data values and their corresponding formatted values that have been created with the FORMAT procedure.

P (Program)

invokes a user-written SAS/AF program. A list that contains the currently pending WHERE expression is passed to the program, where it can be either used or ignored.

Creating an Empty Lookup Table

Submit the following PROC SQL statements in the Program Editor to create an empty lookup table.

proc
sql;
create table sasuser.lookup
  (lookltc  char(100) label='library.table.column',
   lookinfo char(200) label='varies depending on strategy',
   strategy char(8)   label='lookup strategy to use'
  );

SASUSER.LOOKUP is the default name of the lookup table. The SQL Query Window looks for this table to determine whether any automatic lookup is to be performed

Adding a Row to the Lookup Table

After you create the empty lookup table, you can submit additional PROC SQL statements to insert values into the table's LOOKLTC, LOOKINFO, and STRATEGY columns. You can also invoke PROC FSEDIT to add this information. The syntax for inserting values into the table is

proc
sql;
insert into lookup.table
 
values('lookltc-value','lookinfo-value','strategy-value');

Add a row to the SASUSER.LOOKUP data set by submitting the following code in the Program Editor:

proc
sql;
insert into sasuser.lookup
  values('sample.empinfo.location','sample.program.region.frame','P');
quit;

This row contains information that the SQL Query Window uses to perform automatic lookup. Whenever the LOCATION column is selected from the SAMPLE.EMPINFO table in the WHERE EXPRESSION window for any query, the FRAME entry that is defined in SAMPLE.PROGRAM.REGION.FRAME is executed. The lookup strategy value of P indicates that the action that is to take place is a program execution.

Using the Lookup Table

Before you can use the lookup table, you do either of the following in order for the SQL Query Window to read the lookup table:

exit and restart the SQL Query Window
switch to a profile that uses SASUSER.LOOKUP as the automatic lookup table.

For this example, selectTools

Switch to New Profile. Select the SASUSER.PROFILE.QUERY profile and select OK. The SASUSER.PROFILE.QUERY profile uses SASUSER.LOOKUP as the automatic lookup table.

To display the number of employees in each division within a specific geographic region, from the SQL QUERY TABLES window, select File

List/Include Saved Queries to display the Saved Queries window.

Select SASUSER.PROFILE.COUNTS, which you created in Counting and Grouping Data Automatically . Select OK to include the query and to return to the SQL QUERY TABLES window.

Select View

Where Conditions for Subset to display the WHERE EXPRESSION window.

Select Operators. Select AND from the list of operators.

Select EMPINFO.LOCATION from the Available Columns list. Select EQ from the list of comparison operators that appears. Because you have defined EMPINFO.LOCATION with an automatic lookup, the Company Locations window automatically appears.

The Company Locations window is the FRAME entry that is defined in SAMPLE.PROGRAM.REGION.FRAME. Select the westernmost site to complete the WHERE clause.

Select OK.

Viewing Your Output

Select Tools

Run Query

Run Immediate to display the results of your query.

In the SQL QUERY TABLES window, select Tools

Reset to reset your query. Select OK from the dialog box that appears.

Using a Slider Bar to Indicate a Range

Overview of Using a Slider Bar to Indicate a Range

You can use a slider bar to select a range of lookup values in a query.

In this example, you associate the slider with the EMPINFO.SALARY column. Because you might not want to permanently associate these lookup values with the EMPINFO.SALARY column, you can insert the lookup table into a different profile and switch to that profile when you want to use the slider bar.

Creating a New Lookup Table

Submit the following PROC SQL statements in the Program Editor to create an empty lookup table in the SAMPLE library.

proc
sql;
create table sample.lookup
  (lookltc  char(100) label='library.table.column',
   lookinfo char(200) label='varies depending on strategy',
   strategy char(8)   label='lookup strategy to use'
  );

Add a row to the SAMPLE.LOOKUP data set by submitting the following code in the Program Editor:

proc
sql;
insert into sample.lookup
  values('sample.salary.salary','sample.program.salrange.frame','P');
quit;

SAMPLE.PROGRAM.SALRANGE.FRAME is a FRAME entry that defines the slider bar.

Creating a New Profile

Create an SQL Query Window profile that specifies SAMPLE.LOOKUP as the automatic lookup table as follows. Select Profile

Set Preferences.

Select the right arrow next to Automatic Lookup to display the Set Lookup SAS Data Set for Preferences window.

Select the right arrow next to the Library field. Select SAMPLE from the Libraries list and select OK. Select OK to return to the Preference Settings for Profile window.

Select the right arrow next to Data Restrictions to display the Data Restrictions for Profile window. Select SAMPLE from the Table Sources list. Select Add entire Table Source to preferences from the pop-up menu that appears. Select WORK from the Table Sources list. Select Add entire Table Source to preferences from the pop-up menu that appears.

Note: If you do not have write access to the SAMPLE library, then repeat the previous step for the SASUSER library.

Select OK to return to the Preference Settings for Profile window.

Select Save to save your new profile setting. Type LOOKUP in the Entry Name field of the Name Catalog Entry for Profile window. Type Slider Bar for Salary Range in the description field.

Select OK to return to the Preference Settings for Profile window. Select Close.

From the SQL QUERY TABLES window, select Tools

Switch to New Profile. The Preference Profiles in Catalog window appears.

Select the right arrow next to the Profile Name field to display a list of profiles. Select the SASUSER.PROFILE.LOOKUP profile.

Select OK to return to the SQL QUERY TABLES window and to complete the switch to the new profile.

See Setting Your Profile for more information about the SQL Query Window user profile.

A Demonstration of the Slider Bar

To show how the slider works, you can construct a simple WHERE expression that displays the range of salaries. In the SQL QUERY TABLES window, select SAMPLE.SALARY from the Available Tables list and add it to the Selected Tables list. Select OK to display the SQL QUERY COLUMNS window.

In the SQL QUERY COLUMNS window, select Salary and Identification Number from the Available Columns list and add them to the Selected Columns list.

Select View

Where Conditions for Subset.

In the WHERE EXPRESSION window, select Salary from the Available Columns list. Select Between from the OTHER Operators list. Because the lookup table is associated with the Salary column, the slider bar that is the FRAME entry appears.

Select OK to accept the value of 12000. The slider bar appears again because the Between operator requires a second value. Move the slider to the right until 51000 is displayed. Select OK to complete the WHERE expression.

Select OK to return to the SQL QUERY COLUMNS window. Select Tools

Run Query

Run Immediate to display the employee identification numbers whose salaries are between $12,000 and $51,000.

Select Tools

Reset to reset the query and return to the SQL QUERY TABLES window.

Using SCL to Call a FRAME Entry

If your site is licensed to use SAS/AF software, then you can use SAS Component Language (SCL) to create a lookup table that uses the SAMPLE.PROGRAM.SALRANGE.FRAME entry or another FRAME entry that you design. The following SCL program is associated with the SAMPLE.PROGRAM.SALRANGE.FRAME entry:

entry
looklst 8 lkuptype $1 rc 8 msg $40 wherelst 8;

init:
 salrange =12000;
 lkuptype = 'N';
return;


main:
return;

term:
return;

range:
  call notify('range', '_GET_VALUE_', value);
  call notify('salrange', '_SET_VALUE_', value);
return;

ok:
 call notify('salrange', '_GET_VALUE_', value);
 looklst  = insertn(looklst, value, 1);
 rc       = 0;
 _status_ = 'H';
 link term;
return;

Refer to SAS Component Language: Reference for more information about SCL.