Formats for Variable Values and Labels

Formats in htmSQL

htmSQL supports formats in two different ways:

Specified Formats

The following values can be used for the format= parameter of htmSQL variable references and directives:
comma
formats numeric values using commas to separate every three digits. When you specify a format of w.d with the comma format, you must specify either a 0 or 2 for the value of d (the number of decimal places to the right of the decimal character). If you specify any other value for d, then d defaults to a value of 2. When d is equal to 2, htmSQL outputs a decimal point followed by two fraction digits.
The following is an example of how to use this format: 
{&abc format=(comma, 10.2)}
commax
formats numeric values using periods to separate every three digits. When you specify a format of w.d with the commax format, you must specify either a 0 or 2 for the value of d (the number of decimal places to the right of the decimal character). If you specify any other value for d, then d defaults to a value of 2. When d is equal to 2, the htmSQL outputs a comma followed by two fraction digits.
The following is an example of how to use this format: 
{&abcx format=(commax, 10.2)}
dollar
formats numeric values using a leading dollar sign ($) or currency symbol and using commas to separate every three digits. If you specify a format of w.d with the dollar format, and you specify a nonzero value for d (the number of decimal places to the right of the decimal character), then htmSQL outputs a decimal point followed by two fraction digits.
The following are examples of how to use this format: 
{&abc format=(dollar, 15.2)}
{&abc format=dollar}
dollarx
formats numeric values using a leading dollar sign ($) or currency symbol and using periods to separate every three digits. If you specify a format of w.d with the dollarx format, and you specify a nonzero value for d (the number of decimal places to the right of the decimal character), then htmSQL outputs a comma followed by two fraction digits.
The following are examples of how to use this format: 
{&abcx format=(dollarx, 15.2)}
{&abc format=dollarx}
exp
formats numeric values in scientific notation. For example, if the numeric variable abc has a value of -13454, then htmSQL formats the value as -1.345e+04 if you use the following variable reference: 
{&abc format=(exp, 10.3)}
.
hex
formats numeric values in hexadecimal. If you use the w.d format with the hex format, htmSQL ignores the d value.
htmlescape
replaces each of the following special characters with the corresponding character entity reference whenever htmSQL encounters the special character in a variable value:
Special Character
Character Entity Reference
left angle bracket (<)
<
right angle bracket (>)
>
ampersand (&)
&
double quotation mark (")
"
Use this option if the variable's value includes special characters that should be rendered as is when the output Web page appears.
left
prints the value of a numeric variable with no leading blanks.
notrim
retains the trailing blank spaces in the variable value. Retaining trailing blanks enables you to line up values on the Web page more easily. If you do not specify format=notrim, htmSQL discards trailing blanks in the variable value when it resolves the reference.
Note: Because most Web browsers collapse consecutive blank spaces, the notrim format is most effective when it is used with the <PRE> HTML element.
right
right aligns character variable values and pads enough blank spaces on the left to fill up the field width. For example, if the variable linename contains the string "line01", then:{&linename format=10}: appears as 
:line01 :
and :{&linename format=(right, 10)}: appears as 
: line01:
Note: Because most Web browsers collapse consecutive blank spaces, the right format is most effective when it is used with the <PRE> HTML element.
urlencode
URL-encodes the value of the variable. When the values are URL-encoded, the spaces are replaced with plus signs (+). All other nonalphanumeric characters are replaced with escape sequences (%xx), where xx is the hexadecimal representation of the ASCII code point.
Use this option when you include variable references in the values for the ACTION or HREF attributes of HTML elements.
wd.
w specifies the width of the print field. The allowed values for w are integers from 1 to 32767. d is the precision specifier (specifies the number of decimal places to the right of the decimal character). The maximum value for d depends on the exponent of the largest numeric value that an operating system can store in a double. If you do not specify a value for d, the default value is 0.
You can specify a value either for w, for d, or for both. If you specify d by itself, you must precede it with a period (.). The value for d is useful only for numeric values and is ignored for variables containing character and integer values. Note that the following format values are all equivalent: format=8., format=8, and format=8.0.
Note: The htmSQL implementation of field widths (w) for numeric values differs from the SAS implementation. For SAS software, w is generally an absolute specification for the field width, and SAS software changes the formatting of the number to accommodate the width (by doing such things as reducing precision and changing formats). For numeric values in htmSQL, the w width specification is a minimum and is adjusted upward, if necessary, to accommodate the numeric value and the precision specifier (d).
The htmSQL implementation of field widths (w) for character data is the same as the SAS implementation. Both implementations indicate the exact number of characters to format, either truncating or blank-padding as necessary.
zero
pads enough zeros on the left of numeric values to fill up the field width. Without the zero format, numeric values are left-padded with blanks. The zero format is ignored when either the left or exp formats are used.

Associated Formats

  • When you refer to a column in a query results set, if you do not specify the format= parameter but the column has one of the following formats associated with it, then htmSQL uses the associated format to resolve the reference:
    • w.d
    • COMMAw.d
    • COMMAXw.d
    • DOLLARw.d
    • DOLLARXw.d
    • Ew.
    • Zw.d
  • When you refer to a column in a query results set, if the column has a date, time, or datetime format associated with it, the following formats are used:
    For date values:
    Regardless of what date format your column has, htmSQL always outputs date values as either ddMmmyy or ddMmmyyyy, where
    • dd is the day of the month
    • Mmm is the first three letters of the month of the year
    • yy and yyyy are the last two and four digits of the year, respectively (depending on the value of the YEARDIGITS run-time configuration option).
    For time values:
    Regardless of what time format your column has, htmSQL always outputs time values as hh:mm:ss, where
    • hh is the hour of the day using a 24-hour clock
    • mm is the minute of the hour
    • ss is the second of the minute. Note that htmSQL does not handle fractions of seconds.
    For datetime values:
    Regardless of what datetime format your column has, htmSQL always outputs datetime values as ddMmmyy hh:mm:ss or ddMmmyyyy hh:mm:ss, where
    • dd is the day of the month
    • Mmm is the first three letters of the month of the year
    • yy and yyyy are the last two and four digits of the year, respectively (depending on the value of the YEARDIGITS run-time configuration option)
    • there are two spaces separating the date value and the time value
    • hh is the hour of the day using a 24-hour clock
    • mm is the minute of the hour
    • ss is the second of the minute. Note that htmSQL does not handle fractions of seconds.
Note: If you want to use other date, time, or datetime formats, you can use the PUT() function in your SQL query to change the format.
Copyright © SAS Institute Inc. All rights reserved.