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.
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.
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 if any automatic lookup is to be performed
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.
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, select
Tools – 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" on page 47. 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 .
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.
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.
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.
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" on page 73 for more information about the SQL Query Window user profile.
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.
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 rc 8 msg 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.