Defining a Format Outside the SQL Query Window

You can use the FORMAT procedure to define additional output formats. In this example, you define a format using the FORMAT procedure and then use that format to create a report with the SQL Query Window.

Creating the Format

In the Program Editor, submit the following SAS code:

proc
format;
   value edlevel  1-12  = 'No High School Diploma'
                  12    = 'High School Diploma'
                  13    = 'Completing Associate'
                  14    = 'Associate'
                  15    = 'Completing Bachelors'
                  16    = 'Bachelors'
                  17    = 'Completing Masters'
                  18    = 'Masters'
                  19    = 'Completing PhD'
                  20-99 = 'PhD'
                  .     = 'No Education Data';
run;

The previous procedure creates the EDLEVEL. format, which prints a text string that corresponds to the numeric education level value.

See Base SAS Procedures Guide for more information about the FORMAT procedure.

Selecting Your Format

Invoke the SQL Query Window. In the SQL QUERY TABLES window, select SAMPLE.EMPINFO from the Available Tables list and add it to the Selected Tables list. Select OK.

In the SQL QUERY COLUMNS window, select NAME and Education level from the Available Columns List and add them to the Selected Columns list.

Select

Education
                     level

from the Selected Columns list. Select Column Formats to display the Column Formats dialog box.

Select the right arrow next to the Format field to display the Format Names list.

Select edlevel from the Format Names list. Select OK to return to the Column Formats window. Select OK to return to the SQL QUERY COLUMNS window.

Using Formatted Values in a WHERE Expression

Select View

Where Conditions for Subset to display the WHERE EXPRESSION window. Select Education level from the Available Columns list. Select EQ from the list of operators.

Select <LOOKUP distinct values> from the Available Columns list. The Lookup Values list contains the distinct values for the Education Level column using the EDLEVEL. format that you defined.

Select PhD from the list. Because the EQ operator can take only one value, the WHERE EXPRESSION window automatically reappears. Select OK to return to the SQL QUERY COLUMNS window.

Viewing Your Output

Select Tools

Run Query

Run Immediate to display a list of the employees whose education level is PhD.

In the SQL QUERY COLUMNS window, select Tools

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