|
|
SAS/IntrNet 9.1: htmSQL |
|
This page includes tips and hints that other users have found useful. If you have tips that you think others could use, please let us know.
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 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.
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 in 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,
then 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.
If you want to use a format that you yourself defined, then you must provide a libname 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.
|
|
SAS/IntrNet 9.1: htmSQL |
|