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.

The field level rules are not here - they are in mp_derivedvarcheck, which reads the same control table and writes one row per definition that breaks one. This macro adds the two checks that need the target table, and the dependency graph, then generates the statements.

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
[in]	abort=	(YES) Set to NO to return the validation failures in errds= instead of aborting - for tests, and for a caller that wants to report every failure rather than the first. No statement is generated when any row is returned
[out]	errds=	(work.dv_errors) The validation failures when abort=NO: one row per offending VAR_NM per rule broken, so a definition that breaks two rules appears twice, with the rule in REASON. Empty, not absent, when the configuration is valid. The cycle in VAR_DEPS is reported as a single row, because one member identifies the cycle

Version: 5.7

Author: 4GL Apps Ltd

Definition in file mp_derivedvars.sas.