SAS/IntrNet 9.2: htmSQL

Tips and Techniques for Using htmSQL

This page includes tips and hints that other users have found useful.

Checking the Version Number

To find out which version of htmSQL you are running, invoke htmSQL without specifying an input file. htmSQL displays the version number at the end of the usage page that it displays.

Comparing Floating Point Values

Numeric values in htmSQL are always represented as floating point values. When comparing numeric values in htmSQL, you must ensure that the values you compare can be represented exactly in floating point notation. That is, the value you specify must be able to remain the same after going through conversions from binary floating point to string and vice versa (these conversions are necessary for SQL functions). Some floating point values, namely fractions, cannot be exactly reproduced following conversions.

Be aware that when you use the SAS TIME() and DATETIME() functions to generate your time and date values, your generated values are likely to contain fractions because SAS dates and times are stored as floating point values.

If you want to use a WHERE clause that compares fractional values or htmSQL variable references with numeric columns that contain fractional values (for example, WHERE x=1.22 or WHERE x={&myfloat}), you can apply any of the following strategies:

Truncate date and time values to integer values before you store them. Note that htmSQL does not output the fractional part of dates and times.

Use the PUT() function to change the floating point value into a different format before you store it.

Unique Namespaces

For any given scope, there is one namespace for variable names (that is, there is only one list in which variable names are stored). Each input file has a separate scope. Include files have separate scopes so they also have separate namespaces.

If, in a query, you select a variable with the same name as a variable that was passed in on the query string or that was selected in a previous query, then the original value of that variable is replaced by your newly selected value.

It is also possible to overwrite the value of an automatic variable if you pass a value for an automatic variable in the query string of a URL.

Using Existing Connections

htmSQL recognizes when a {query} directive uses the same parameters as the previous {query} directive. Instead of making a new connection, htmSQL uses the existing connection, which saves in execution time.

Using SAS Formats and the PUT() Function

If you want to format data using a SAS format that is not supported by htmSQL, you can use the PUT() function in your SQL statement to specify that format. For example, if you want to use the SAS mmddyy8. format to format a date, write an SQL statement like the following SELECT statement:

     select put(datevar,mmddyy8.) as datevar ...

The value that is returned in the datevar variable is a character value in the mmddyy8. format. You can then provide more formatting for the variable by using the format option. For example,

     {&datevar format=left}

applies a format that htmSQL supports (left) onto the date variable.

Note that if you use the following select statement,

     select datevar format=mmddyy8...

htmSQL does not format the datevar variable using the mmddyy8. format. Instead a numeric value is returned and is formatted using the default date format.

Using User-defined Formats

If you want to use a format that you yourself defined, then you must provide a libref definition in the SAS program that starts your SAS server. For example:

     libname myfmts '/u/joeuser/formats';
     options fmtsearch=myfmts;
     .
     .
     .
     proc serverid=shr1...;

After you define your formats in this way, you can use the PUT() function to specify the format in your SQL code.

Top of Page