Functions

Overview

GTL supports a large number of functions, including SAS functions that can be used in the context of a WHERE expression, and other functions that are defined only in GTL.
SAS functions that can be used in a WHERE expression include the following types of functions:
  • character handling functions
  • date and time functions
  • mathematical and statistical functions.
Note: Not all SAS functions are available in WHERE expressions. Call routines and other functions that are restricted to the DATA step (LAG, VNAME, and OPEN, for example) are the types of functions that cannot be used.
All of the functions that are used in GTL must be enclosed within an EVAL function.

General Functions Supported Only in GTL

The following table shows functions that are used only in GTL. In all of these functions, column can be either the name of a column in the input data set, or a dynamic variable or macro variable that resolves to a column.
Function Name
Description
COLNAME( column )
returns the case-sensitive name of the column
COLLABEL( column )
returns the case-sensitive label of the column. If no label is defined for the column, the case-sensitive name of the column is returned.
EXISTS ( item )
returns 1 if the specified item exists, 0 otherwise. If item is a column, EXISTS tests for the presence of the column in the input data set. If item is a dynamic variable or a macro variable, EXISTS tests whether the variable has been initialized at runtime.
EXPAND( numeric-column,
freq-column )
creates a new column whose values equal ( numeric-column * frequency-column )
ASORT (column,
RETAIN=ALL)
sorts all of the data object's columns, in ascending order, by the values of column. SORT is an alias for ASORT.
WARNING: if the RETAIN=ALL argument is not included, column alone is sorted, not the other columns, thereby losing row-wise correspondence.
DSORT (column,
RETAIN=ALL)
sorts all of the data object's columns, in descending order, by the values of column.
WARNING: if the RETAIN=ALL argument is not included, column alone is sorted, not the other columns, thereby losing row-wise correspondence.
NUMERATE( column)
returns a column that contains the ordinal position of each observation in the input data set (similar to an OBS column).
The following code shows some example uses of the GTL functions:
/* arrange bars in descending order of response values */
 barchartparm x=region y=eval(dsort(amount,retain=all));
 
/* Label outliers with their position in the data set.
   It does not matter which column is used for NUMERATE(). */
boxplot x=age y=weight / datalabel=eval(numerate(age));

/* Add information about the column being processed.
   The column name is passed by a dynamic. */
entrytitle "Distribution for " eval(colname(DYNVAR));

GTL Summary Statistic Functions

The following functions return a numeric constant, based on a summary operation that is performed on a numeric column. The results of these functions are the same as if the corresponding statistics were requested with PROC SUMMARY. These functions take a single argument, which resolves to the name of a numeric column. They take precedence over similar multi-argument DATA step functions.
number = EVAL( function-name( numeric-column ) )
Function Name
Description
CSS
Corrected sum of squares
CV
Coefficient of variation
KURTOSIS
Kurtosis
LCLM
One-sided confidence limit below the mean
MAX
Largest (maximum) value
MEAN
Mean
MEDIAN
Median (50th percentile)
MIN
Smallest (minimum) value
N
Number of non-missing values
NMISS
Number of missing values
P1
1st percentile
P5
5th percentile
P25
25th percentile
P50
50th percentile
P75
75th percentile
P90
90th percentile
P95
95th percentile
P99
99th percentile
PROBT
p-value for Student's t statistic
Q1
First quartile
Q3
Third quartile
QRANGE
Interquartile range
RANGE
Range
SKEWNESS
Skewness
STDDEV
Standard deviation
STDERR
Standard error of the mean
SUM
Sum
SUMWGT
Sum of weights
T
Student's t statistic
UCLM
One-sided confidence limit above the mean
USS
Uncorrected sum of squares
VAR
Variance
The following example uses GTL summary statistic functions to dynamically construct reference lines and a table of statistics for a numeric variable, which is supplied at runtime.
Distribution of MRW
proc template;
 define statgraph expression;
  dynamic NUMVAR "required";
  begingraph; 
   entrytitle "Distribution of  " eval(colname(NUMVAR));
   layout overlay /  xaxisopts=(display=(ticks tickvalues line));
    histogram NUMVAR;

	 /* create reference lines at computed positions */
    referenceline x=eval(mean(NUMVAR)+2*std(NUMVAR)) /
      lineattrs=(pattern=dash) curvelabel="+2 STD";
    referenceline x=eval(mean(NUMVAR)) /
      lineattrs=(thickness=2px) curvelabel="Mean";
    referenceline x=eval(mean(NUMVAR)-2*std(NUMVAR)) /
      lineattrs=(pattern=dash) curvelabel="-2 STD";

 /* create inset */
    layout gridded / columns=2 order=rowmajor
      autoalign=(topleft topright) border=true;
     entry halign=left "N";
     entry halign=left eval(strip(put(n(NUMVAR),12.0)));
     entry halign=left "Mean";
     entry halign=left eval(strip(put(mean(NUMVAR),12.2)));
     entry halign=left "Std Dev";
     entry halign=left eval(strip(put(stddev(NUMVAR),12.2)));
    endlayout;
   endlayout;
  endgraph;
 end;
run;

proc sgrender data=sashelp.heart template=expression;
  dynamic numvar="MRW";
run;