SAS/ACCESS Interface to Google BigQuery passes the following SAS functions to Google BigQuery for processing. When the Google BigQuery function name differs from the SAS function name, the Google BigQuery name appears in parentheses. For more information, see Passing Functions to the DBMS Using PROC SQL.
| ABS | LOG10 |
| ARCOS (ACOS) | LOWCASE |
| ARSIN (ASIN) | MAX |
| ATAN | MIN |
| ATAN2 | SIGN |
| AVG | SIN |
| CEILING (CEIL) | SQRT |
| COS | STDDEV |
| COUNT | SUM |
| EXP | TAN |
| FLOOR | UPCASE |
| LOG | VAR (VARIANCE) |
SQL_FUNCTIONS=ALL allows for SAS functions that have slightly different behavior from corresponding database functions that are passed down to the database. Only when SQL_FUNCTIONS=ALL can the SAS/ACCESS engine also pass these SAS SQL functions to Google BigQuery. Because of incompatibility in date and time functions between Google BigQuery and SAS, Google BigQuery might not process them correctly. Check your results to determine whether these functions are working as expected. For more information, see SQL_FUNCTIONS= LIBNAME Statement Option.
|
SAS Function |
Google BigQuery Function |
Usage Notes |
|---|---|---|
|
BYTE |
CHR |
none |
|
COALESCEC |
COALESCE |
This function applies to character strings only. |
|
COMPRESS |
TRANSLATE |
none |
|
DATE |
CURRENT_DATE |
Value is passed as a constant only when the DBMS uses the CURRENT_DATE function. |
|
DATETIME |
CURRENT_TIMESTAMP |
Value is passed as a constant only when the DBMS uses the CURRENT_TIMESTAMP function. |
|
DAY |
EXTRACT(DAY FROM …) |
SAS passes this function through for DATETIME, TIME, and TIMESTAMP values. This function is not passed through for DATE values. |
|
HOUR |
EXTRACT (HOUR FROM ...) |
SAS passes this function through for DATETIME, TIME, and TIMESTAMP values. This function is not passed through for DATE values. |
|
INDEX |
STRPOS |
none |
|
LENGTH |
BYTE_LENGTH |
none |
|
MINUTE |
EXTRACT (MINUTE FROM ...) |
SAS passes this function through for DATETIME, TIME, and TIMESTAMP values. This function is not passed through for DATE values. |
|
MOD |
MOD |
The MOD function does not pass FLOAT64 values, because SAS does not modify non-integer arguments to the MOD function. |
|
MONTH |
EXTRACT (MONTH FROM ...) |
SAS passes this function through for DATE, DATETIME, and TIMESTAMP values. This function is not passed through for TIME values. |
|
SECOND |
EXTRACT (SECOND FROM ...) |
SAS passes this function through for DATETIME, TIME, and TIMESTAMP values. This function is not passed through for DATE values. |
|
TIME |
CURRENT_TIME |
Value is passed as a constant only when the DBMS uses the CURRENT_TIME function. |
|
TRANWRD |
REPLACE |
none |
|
TRIM |
RTRIM |
SAS and Google BigQuery handle empty strings differently. |
|
TODAY |
CURRENT_DATE |
Value is passed as a constant only when the DBMS uses the CURRENT_DATE function. |
|
WEEK |
EXTRACT(WEEK FROM …) |
SAS passes this function through for DATE, DATETIME, and TIMESTAMP values. This function is not passed through for TIME values. |
|
YEAR |
EXTRACT (YEAR FROM ...) |
SAS passes this function through for DATE, DATETIME, and TIMESTAMP values. This function is not passed through for TIME values. |