Tips and Techniques for Using htmSQL

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.

Numeric values in htmSQL are always represented as floating-point values. When comparing numeric values in htmSQL, you must ensure that the values that you compare can be represented exactly in floating-point notation. That is, the value that 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:

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.

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 execution time.

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, the following applies a format that htmSQL supports (left) onto the date variable:

{&datevar format=left}

Note that if you use the following select statement, htmSQL does not format the datevar variable using the mmddyy8. format:

select datevar format=mmddyy8...

Instead a numeric value is returned and formatted using the default date format.

If you want to use a format that you 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.