Chapter Contents
Previous
Next
SAS SQL Query Window User's Guide

Using the Automatic Lookup Feature

You can implement automatic lookup for any column in a table that can be accessed from the SQL Query window. An action automatically occurs when that column and an operator is selected from the Where Expression window.

Implement automatic lookup by creating a SAS data set called a lookup table. 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, 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

You can use the following PROC SQL statements 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.

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 window: 

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

SAMPLE.PROGRAM.REGION.FRAME is a FRAME entry that is part of the sample library you are using for these examples.

Using the Lookup Table

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

File
[arrow]
 List/Include Saved Queries...

to display the Saved Queries window.

[IMAGE]

Select SASUSER.PROFILE.COUNTS, which was created inCounting and Grouping Data Automatically. Select Include to include the query and to return to the SQL QUERY TABLES window.

Select

View
[arrow]
 Where Conditions for Subset...

to display the WHERE EXPRESSION window.

[IMAGE]

Select Operators. Select AND from the list of operators.

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

[IMAGE]

Select the westernmost site to complete the WHERE clause.

[IMAGE]

Viewing Your Output

Select OK.

Select

Tools
[arrow]
 Run Query
[arrow]
 Run Immediate

to display the results of your query.

[IMAGE]

Select

Tools
[arrow]
 Reset

to reset your query.

Creating 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 will associate the slider with EMPINFO.salary. Because you may 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

Use the following PROC SQL statements to create an empty lookup table in the SAMPLE directory. 

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 window: 

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 is part of the sample library you are using for these examples.

Creating a New Profile

Create an SQL Query Window profile that specifies SAMPLE.LOOKUP as the automatic lookup table. From the PMENU, select

Profile
[arrow]
 Set Preferences...

Select the [→] next to Automatic Lookup to display the Set Lookup SAS Data Set for Preferences window.

[IMAGE]

Select SAMPLE from the list of Libraries. Select OK to return to the Preference Settings for Profile window.

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

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 Entry description for the profile: field.

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

From the SQL QUERY TABLES window PMENU, select

Tools
[arrow]
 Switch to New Profile

Select the [→] next to Profile Name: to display a list of profiles. Select SASUSER.PROFILE.LOOKUP from the Preference Profiles in Catalog window.

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 on 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 list of Available Tables and add it to the list of Selected Tables. Select OK to display the SQL QUERY COLUMNS window.

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

Select

View
[arrow]
 Where Conditions for Subset...

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

[IMAGE]

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

[IMAGE]

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

Tools
[arrow]
 Run Query
[arrow]
 Run Immediate

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

[IMAGE]

Select

Tools
[arrow]
 Reset

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

How to Use SCL to Call a FRAME Entry

If your site is licensed to use SAS/AF software, you can use SAS Screen Control Language (SCL) to create a lookup table that uses the SAMPLE.PROGRAM.SALRANGE.FRAME entry or another FRAME entry that you design. 

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 on SCL.


Chapter Contents
Previous
Next
Top of Page

Copyright 1999 by SAS Institute Inc., Cary, NC, USA. All rights reserved.