Generates the SAS statements that create derived variables. More...

Detailed Description

Derived (computed) variables are configured in a control table, one row per variable. The macro writes the statements that create them to a fileref, so that callers can compose them into a view:

%mp_derivedvars(libds=&libds,control=perm.derived_vars,outref=dercode)
data work.vw_derived / view=work.vw_derived;
  set &libds;
  %include dercode;
run;

Alternatively pass outview= and the macro will build the view itself.

Building a view is what makes a derived variable filterable. A WHERE statement applies to the data set named on the SET statement, so it cannot reference a variable created in the same data step:

data out;
  set work.vw_derived;
  where <filter>;
run;

Each variable produces two statements:

attrib REGION length=$21 label='REGION=<expression>' format=$21.;
REGION = <expression>;

Type and length are declared in the control table, so the result is deterministic.

VAR_DEPS lists every input the expression uses - physical columns and derived variables alike. The order the statements are generated in is the dependency order (variables with no unsatisfied dependency first, ties broken by name), so a derived variable may build on another one without the administrator maintaining a sequence number. A cycle is rejected.

The declaration is checked against the expression: a derived variable that is referenced must be declared, a declared name that is not referenced is an error, and a declared name must be a column of the target table or a derived variable defined for it. Semicolons, macro triggers and comments are rejected, so a row can only ever be an expression. A derived variable may not reuse the name of a physical column, and may not reference itself: an intra-row calculation would imply a retained value, which depends on the order rows happen to arrive in.

VAR_LABEL is optional. When it is missing or blank, the label is the variable name, an equals sign and the expression - so a reader can see both what the column is and where it comes from. A configured label replaces that, and is used as given.

The control table is created by mddl_derivedvars.sas, and must carry LIBREF, DSN, VAR_NM, VAR_DEPS, VAR_TYPE, VAR_LEN, VAR_FMT and VAR_LOGIC, with VAR_LABEL optional. Extra columns are ignored, but a control table that holds several versions of the same VAR_NM - for example a bitemporal TX_FROM / TX_TO pair - must be narrowed with where= (typically to the current versions), because two rows with the same name are rejected as a configuration error.

SAS Macros

Related Macros

mddl_derivedvars.sas

Parameters

[in]	libds=	The target table (libref.dataset, lib must be assigned)
[in]	control=	The control table holding the variable definitions
[in]	where=	(0) Optional additional filter for the control table
[out]	outref=	(0) Fileref to receive the generated statements
[out]	outvars=	(work.derived_varlist) Dataset of derived variable names
[in]	outview=	(0) Optional two-level name for the derived view
[in]	reserved=	(0) Space separated list of names that may not be used

Version: 5.6

Author: 4GL Apps Ltd

Definition in file mp_derivedvars.sas.