Home
Macro Language Elements
The section contains detailed specifications for all 1010data Macro Language elements.
Data Transformation
Data transformation operations use functions and expressions to functionally alter tabular data. These operations take 0, 1, or 2 tables as input and produce a single table as a result.
<sort>
Changes the order in which rows appear in a table by sorting according to the values in a column.

Macro Language Elements
The section contains detailed specifications for all 1010data Macro Language elements.
- Data Transformation
  Data transformation operations use functions and expressions to functionally alter tabular data. These operations take 0, 1, or 2 tables as input and produce a single table as a result.
  - <amend>
    Update a specified value, or column of values, either by entering a new value or by using an expression. The expression can be applied to the column the value is in or to another column.
  - <base>
    <base> is the concrete macro operation that represents the action of opening the initial table.
  - <col>
    <col> changes the display attributes of a column in a table. It does not change the name of a column, but it does change the heading, as an example.
  - <colord>
    <colord> tells the system which columns to display and in what order.
  - <cols>
    Modifies the metadata of multiple columns at once, based on a query result or formatted tabular input.
  - <columns>
    <columns> is a special operation that retrieves column information from a table.
  - <directory>
    <directory> returns a table that provides information about the child objects of a particular folder. These include any tables, folders, and queries that have been saved to that folder.
  - <find>
    Efficiently finds (without selecting) the first n rows, between the specified row numbers, where the specified Boolean value expression is true.
  - <ignore>
    <ignore> encases code in a Macro Language query that should not be executed by the system.
  - <link>
    Link a foreign table to a base table or worksheet. Both tables must share a column on which to link.
  - <materialize>
    Creates a new physical table.
  - <merge>
    Merge a table to another table or worksheet.
  - <meta>
    <meta> allows settings to be made at the level of an individual query (and saved along with the query).
  - <note>
    <note> may be used to place a comment in the Macro Language. The contents of the element is any standard text.
  - <sel>
    Select the rows relevant to your analysis.
  - <sort>
    Changes the order in which rows appear in a table by sorting according to the values in a column.
  - <stats:>
    The <stats:> package provides more advanced statistics operations.
  - <table>
    <table> is a special operation that creates a temporary table within the scope of a query.
  - <tabu>
    <tabu> defines a tabulation or cross-tabulation.
  - <transpose>
    <transpose> results in a table in which the rows from the original table are its columns and the columns from the original table are its rows.
  - <willbe>
    Define a new computed column in an existing worksheet with a value expression.
- Blocks
  Blocks allow users to reuse sections of Macro Language code and to parameterize values in that code for greater flexibility and power.
- Application Development

`<sort>`

Changes the order in which rows appear in a table by sorting according to the values in a column.

Syntax

<sort col="[COLUMN_NAME]" dir="[SORT_DIRECTION]"/>

Attributes

col

The name of the column (real or computed) whose values will be used to order the rows.

You can use an expression for the col attribute. For example, <sort col="lowercase(foo)"/> first converts column foo to lower case, and then performs the sort.

dir

The direction in which to sort: up or down. The default is up.

Valid values for dir

up: Sort from lowest to highest numeric value or alphabetically.
down: Sort from highest to lowest numeric value or reverse-alphabetically.

naorder

Tells the system to include N/A values first or last in the collation order.

first: N/A values appear in the beginning of the collation order, the default behavior. This means they appear at the beginning when the table is sorted up, and at the end when the table is sorted down.
last: N/A values appear at the end of the collation order. This means they appear at the end when the table is sorted up, and at the beginning when the table is sorted down.

cols

The cols attribute allows for sorting by multiple columns. Provide column names in a comma-separated list.

Columns are sorted in ascending order by default.

You can use expressions in the cols attribute. See the col attribute for an example.

Prefixing a column name with a backtick character (`) specifies that the column should be sorted in descending order. (Available as of prod-9)

Note: You cannot use the dir and cols attributes together. You must use the backtick character to control the sort direction of each of the columns specified by the cols attribute.

Example

The following example sorts the pub.demo.retail.item table such that the values in the date column are in descending order (dir="down") and any N/A values appear at the beginning of the sorted table (naorder="last").

<base table="pub.demo.retail.item"/>
<sort col="date" dir="down" naorder="last"/>