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.

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;