SAS macros are stored in the MSTORE library. If you need to use one of these functions, make sure to include in the code the following statement:
options mstored sasmstore=MSTORE;
This will tell SAS where to search for the stored macros.
%stringTokenizer(string, separator, token)
Parses a string which is composed of several tokens separated by the separator character. It executes the code below it for each retrieved token, which is saved inside the token variable. The statement must be closed with the %endStringTokenizer macro.
Parameters:
string - string which needs to be tokenized separator - character which separates the tokens inside the string token - output variable which is generated by the macro containing the current retrieved token
Dependencies:
Requires MSTORE.strings package to be loaded.
Examples:
data _null_;
/* it is like a for loop iterated on the tokens */
%stringTokenizer("first$second$third", "$", myToken)
putlog "current token" myToken=;
%endStringTokenizer
run;
%iterateFacets(facets, facet, facetHeader, facetCode)
Works as the String Tokenizer macro, but it provides an improved interface to parse facets. In particular, it parses the facets variable and it executes the code below the iterateFacets statement for each facet, providing the current processed facet, its header and its code. The statement must be closed with the %endIterateFacets macro.
Parameters:
facets - facets list composed of facets with header and code, $-separated facet - the currently processed facet facetHeader - the header of the currently processed facet facetCode - the code of the currently processed facet
Dependencies:
Requires MSTORE.strings package to be loaded.
Examples:
This will print inside the log a line for each facet with the facet information:
data _null_;
%iterateFacets("F01.BBB$F02.CCC", fac, head, code)
putlog "facet (header+code)" fac "facet header" head "facet code" code;
%endIterateFacets
run;
%macro appendDataset(data, out)
Appends data to the out table. If out does not exist, it will be created
Parameters:
data - dataset which will be appended to out out - name of the dataset in which data will be appended
Examples:
data AAA;
A = 1;
B = 2;
output;
run;
%appendDataset(AAA, BBB); /* BBB will be created with one row as copy of AAA */
/* Now BBB exists */
%appendDataset(AAA, BBB); /* AAA will be appended to BBB (two rows now) */
%macro deleteDataset(data)
Deletes the dataset data if it exists. If it does not, no action is performed.
Parameters:
data - dataset which will be deleted (put also the library name for non “work” datasets)
Examples:
%deleteDataset(NOT_EXISTING_DATA); /* No action performed */
data AAA;
a = 1;
run;
%deleteDataset(AAA); /* AAA will be deleted */
%macro assertExists(dataset)
Asserts that the dataset passed as input exists, it gives an error in the SAS log otherwise. Function meant to be used only for creating test cases.
Parameters:
dataset - the SAS dataset which needs to be checked (put also the library name if needed)
Examples:
Example of false assertion:
%assertExists("AAA"); /* This will print an error in the log */
Example of true assertion:
data BBB;
run;
%assertExists("BBB"); /* OK, no error is printed */
%macro assertNotExists(dataset)
Asserts that the dataset passed as input does not exist, it gives an error in the SAS log otherwise. Function meant to be used only for creating test cases.
Parameters:
dataset - the SAS dataset which needs to be checked (put also the library name if needed)
Examples:
Example of true assertion:
%assertNotExists("AAA"); /* OK, no error is printed */
Example of false assertion:
data BBB;
run;
%assertNotExists("BBB"); /* This will print an error in the log */
%macro assertEqualsValue(expected, found)
Asserts that expected is equal to found, it gives an error in the SAS log otherwise. Function meant to be used only for creating test cases.
Parameters:
expected - the expected value for a variable found - the actual value found for a variable
Examples:
data AAA;
a = 1;
b = 1;
output;
a = 1;
b = 2;
output;
run;
/* if a = 1 then b should be 1 */
data _null_;
set AAA;
if (a = 1) then %assertEqualsValue(1, b);
run;
%macro assertNobsEquals(dataset, expectedNum)
Asserts that the number of rows of the dataset is equal to the expectedNum, it gives an error in the SAS log otherwise. Function meant to be used only for creating test cases.
Parameters:
dataset - the SAS dataset which needs to be checked (put also the library name if needed) expectedNum - the expected number of observations which should be present in the dataset
Dependencies:
Requires MSTORE.tables package to be loaded.
Throws:
An error is thrown if dataset does not exist.
Examples:
Example of true assertion:
data AAA;
a = 1;
output;
run;
%assertNobsEquals(AAA, 1);
Example of false assertion:
data AAA;
a = 1;
output;
run;
%assertNobsEquals(AAA, 3);
%macro DEAV_ENRICH_AND_VALIDATE(
inputTable=,
outputTable=,
action=,
parNames=,
parValues=,
dcfId=ID,
datasetId=DATASET_ID,
uniqueIdentifier=)
Interface for applying a general data enrichment and validation step inside the business rules engine. It enriches and validates the inputTable according to the required action. Each action has its own set of parameters which must be specified in the parNames and parValues parameters. In particular, parNames contains the names of the parameters and parValues their values (in the same order! See examples for further details).
Parameters:
inputTable - Input dataset which must be enriched/validated
outputTable - Table in which the data enrichment and validation errors are put. Note that this table will be created by the algorithm, therefore it should not exist before calling the macro.
action - The required data enrichment and validation action. Possible values are: - “FOODEX2_VALIDATION”, it validates a list of FoodEx2 codes and put the errors in the outputTable - “FOODEX2_TO_MATRIX”, it converts a list of FoodEx2 codes into matrix codes. A new column is added to the inputTable containing the matrix code (this column will be named as specified in the matrixColumn parameter)
parNames - Macro variable, list of comma separated names which identifies the specific parameters of a DEAV step. Note that since it is a comma separated list, it requires the %quote statement to be passed as input. Possible values are: - foodex2Column, used to specify a FoodEx2 column. For example, this can be used for the FoodEx2 validation or for the FoodEx2 to matrix mapping. - foodex2Hierarchy, used to specify the code of a FoodEx2 hierarchy. For example, this can be used to specify a hierarchy in which the FoodEx2 validation should be evaluated. - matrixColumn, used to specify a matrix code column. For example, this can be used for the FoodEx2 to matrix mapping.
parValues - Macro variable, list of comma separated values of the passed parameters listed in parNames. The order must be the same, that is, the first value in parValues is the value of the parameter named in the first value of parNames. Note that since it is a comma separated list, it requires the %quote statement to be passed as input.
dcfId - Name of the column of the input dataset containing the DCF id field. By default, it is ID.
datasetId - Name of the column of the input dataset containing the dataset id field. By default it is DATASET_ID.
uniqueIdentifier - Name of the column of the input dataset containing the record unique identifier field.
Dependencies: Add this piece of code before calling the macro:
options mstored sasmstore=MSTORE;
options fmtsearch=(FMTLIB BRS_STG); /* Required to use BR formats for parents */
options cmplib=(MSTORE.strings MSTORE.catalogues MSTORE.mtx MSTORE.dcf MSTORE.DEAV MSTORE.FOODEX2_VALIDATION MSTORE.tables);
Examples: It validates the “FX2” FoodEx2 column of the MY_DATA dataset. The validation results are returned in MY_ERRORS.
data MY_DATA;
FX2 = "A0BA0"; output;
FX2 = "A029F#F20.A07QQ$F21.A07RV"; output;
run;
%let parNames=foodex2Column;
%let parValues=FX2;
%DEAV_ENRICH_AND_VALIDATE(inputTable=MY_DATA, outputTable=MY_ERRORS, action="FOODEX2_VALIDATION", parNames=%quote(&parNames.), parValues=%quote(&parValues.));
It validates the “FX2” FoodEx2 column of the MY_DATA dataset using the reporting hierarchy as parent-child relationships evaluator. The validation results are returned in MY_ERRORS.
data MY_DATA;
FX2 = "A0BA0"; output;
FX2 = "A029F#F20.A07QQ$F21.A07RV"; output;
run;
%let parNames=foodex2Column,foodex2Hierarchy;
%let parValues=FX2,"REPORT";
%DEAV_ENRICH_AND_VALIDATE(inputTable=MY_DATA, outputTable=MY_ERRORS, action="FOODEX2_VALIDATION", parNames=%quote(&parNames.), parValues=%quote(&parValues.));
It maps the “FX2” FoodEx2 column of the MY_DATA dataset into matrix codes which will be inserted in a new column named “MATRIX”.
data MY_DATA;
FX2 = "A0BA0"; output;
FX2 = "A029F#F20.A07QQ$F21.A07RV"; output;
run;
%let parNames=foodex2Column,matrixColumn;
%let parValues=FX2,MATRIX;
%DEAV_ENRICH_AND_VALIDATE(inputTable=MY_DATA, outputTable=MY_ERRORS, action="FOODEX2_TO_MATRIX", parNames=%quote(&parNames.), parValues=%quote(&parValues.));
%macro DEAV_FOODEX2_VALIDATION(inputTable=, outputTable=, idColumns=, foodex2Column=, foodex2Hierarchy=, checkReportability=0, checkDeprecated=0, statistics=0)
Validates a set of FoodEx2 codes and returns the errors/warnings.
Parameters:
inputTable - Input table containing the data to validate
outputTable - Output table which will contain the detected errors. This table will be created by the algorithm.
idColumns - List of space separated columns names which specify the fields which identify a row of the inputTable. foodex2Column - The name of the column of input containing the FoodEx2 code to validate
foodex2Hierarchy - Optional parameter, it specifies which hierarchy should be used for performing additional validation steps, as the FOODEX.19 business rule. If omitted, the additional checks are skipped.
checkReportability - Optional parameter, it specifies if the algorithm should check the term reportability, that is, if a not reportable term/facet is used then an error is raised (0/1 values). Note that this will evaluate the up-to-date reportability of terms, without taking into consideration the period of time in which the terms were reported. This makes these checks not applicable to historical data, where the reportability of terms could be different. By default, terms reportability is not checked (i.e. 0 value).
checkDeprecated - Optional parameter, it specifies if the algorithm should highlight the use of deprecated terms/facets as an error (0/1 values). Note that this will evaluate the up-to-date deprecated state of terms, without taking into consideration the period of time in which the terms were reported. This makes these checks not applicable to historical data, where deprecated terms could be different. By default, deprecated terms are not checked (i.e. 0 value).
statistics - Optional parameter, it specifies if the algorithm performances should be evaluated or not while running the algorithm (0/1 values). By default, no statistics is computed (i.e. 0 value).
Missing implementation The procedure does not check if a term exist, it should be added. This leads also to a not always consistent deprecated check. For example, consider the case of A002M#F01.A002M. The code A002M does not exist in the F01 hierarchy, but an error will still be raised since the term itself is deprecated in general.
Dependencies: Add this piece of code before calling the macro:
options mstored sasmstore=MSTORE;
options fmtsearch=(FMTLIB BRS_STG); /* Required to use BR formats for parents */
options cmplib=(MSTORE.strings MSTORE.catalogues MSTORE.mtx MSTORE.dcf MSTORE.DEAV MSTORE.FOODEX2_VALIDATION MSTORE.tables);
Examples:
data MY_DATA;
DCFID = 1;
DATASET_ID = 2;
FOODEX = "A029F#F20.A07QQ$F21.A07RV";
run;
%DEAV_FOODEX2_VALIDATION(inputTable=MY_DATA, outputTable=ERR_TABLE, idColumns=DCFID DATASET_ID, foodex2Column=FOODEX); /* without hierarchy */
%DEAV_FOODEX2_VALIDATION(inputTable=MY_DATA, outputTable=ERR_TABLE, idColumns=DCFID DATASET_ID, foodex2Column=FOODEX, foodex2Hierarchy="REPORT"); /* with reporting hierarchy */
%DEAV_FOODEX2_VALIDATION(inputTable=MY_DATA, outputTable=ERR_TABLE, idColumns=DCFID DATASET_ID, foodex2Column=FOODEX, statistics=1); /* compute statistics without hierarchy */
%DEAV_FOODEX2_VALIDATION(inputTable=MY_DATA, outputTable=ERR_TABLE, idColumns=DCFID DATASET_ID, foodex2Column=FOODEX, foodex2Hierarchy="REPORT", statistics=1); /* compute statistics with reporting hierarchy */
%macro DEAV_FOODEX2_TO_MATRIX(inputTable=, outputTable=, idColumns=, foodex2Column=,
matrixColumn=, statistics=0)
Maps a FoodEx2 column to matrix code.
Parameters:
inputTable - Input table containing the FoodEx2 codes outputTable - Output table which will contain the process errors (if any) idColumns - List of space separated columns names which specify the fields which identify a row of the inputTable. foodex2Column - The name of the column of input containing the FoodEx2 code to map matrixColumn - The name of the column which will be created in the input table containing the mapped matrix code statistics - Optional parameter, it specifies if the algorithm performances should be evaluated or not while running the algorithm (0/1 values). By default, no statistics is computed (i.e. 0 value).
Examples:
data MY_DATA;
ID = 1;
FOODEX = "A029F#F20.A07QQ$F21.A07RV";
run;
%DEAV_FOODEX2_TO_MATRIX(inputTable=MY_DATA, outputTable=ERR_TABLE, idColumns=ID, foodex2Column=FOODEX, matrixColumn=MATRIX); /* No statistics computed */
%DEAV_FOODEX2_TO_MATRIX(inputTable=MY_DATA, outputTable=ERR_TABLE, idColumns=ID, foodex2Column=FOODEX, matrixColumn=MATRIX, statistics=1); /* with performance statistics */
%macro DEAV_CREATE_EMPTY_ERR_TABLE(errorTable)
Creates an empty data enrichment and validation error table. The error table structure is the following:
| ROW_ID | ERR_CODE | ERR_TYPE | ERR_MESSAGE | ERR_COLUMN | ERR_VALUE |
|---|---|---|---|---|---|
| integer | code of the error | type of error | error message | involved column | involved column value |
A single error can create multiple rows (with same ROW_ID, ERR_CODE, ERR_TYPE, ERR_MESSAGE but different ERR_COLUMN with its own ERR_VALUE). This is used to have a table which can accommodate a dynamic set of erroneous columns.
Parameters:
errorTable - name of the table which will be created
SAS functions are stored in the MSTORE library and organized in several packages. If you need to use a function of a specific package, you will have to write a command to import that package in your code. For example, if it is required to use a function inside the MSTORE.strings package, it is needed to provide the following code:
options cmplib=(MSTORE.strings);
If two or more packages are needed, specify them space-separated in the same option, such as:
options cmplib=(MSTORE.strings MSTORE.catalogues);
This will enable using the functions of both strings and catalogues packages. In the following Sections every package and its functions are described.
This package contains handy functions for managing catalogues data and metadata.
function getHierarchyByAttributeCode(catalogueCode $, attrCode $) $ 400
Returns the code of an hierarchy of type “attribute” starting from the attribute code related to the hierarchy.
Parameters:
catalogueCode - The code of the catalogue containing the attribute and the hierarchy to retrieve attrCode - The code of the attribute which is related to the hierarchy to retrieve
Returns:
The code of the hierarchy related to the attribute if found, otherwise an empty string. If the attrCode or the catalogueCode do not exist in the CATALOG library, then 0 is returned.
Dependencies:
Requires stored macro to be correctly executed. See Macros for further details.
Examples:
Regarding the MTX catalogue, if the value “F01” is given as attrCode, the function will return “source”, because the source hierarchy is related to the F01 attribute.
data _null_;
hierarchy = getHierarchyByAttributeCode("MTX", "F01");
putlog hierarchy=; /* This will return "source" */
run;
function getAncestorAtLevel(term $, hierarchy $, level $) $ 4000
Returns the code of a term which is ancestor of the term specified in the input parameters at a certain level of the considered hierarchy.
Parameters:
term - Code of the term from which the retrieval of the ancestor is started hierarchy - Code of the hierarchy in which the ancestor of the term are searched level - Absolute level of the required ancestor in the selected hierarchy tree. In particular, level = 1 targets root ancestors, level = 2 targets ancestors in the second level of the hierarchy and so on. If the direct parent is needed, it is necessary to know the level of the child term and use that number minus 1.
Returns:
The code of the ancestor at the selected level in the hierarchy if found, otherwise empty string. If the level parameter is equal to the level of the child term, then the child term code is returned as if it was the ancestor of itself. If term does not exist in the hierarchy an empty string is returned. If level is bigger than zero and also bigger than the hierarchy level in which lies the child term, then an empty string is returned.
Dependencies:
Requires Business Rules format, add the following to your code:
options fmtsearch=(FMTLIB BRS_STG);
Throws:
An error is thrown if a level value less than 1 is passed. An error is thrown if a non-existing hierarchy code is passed.
Examples:
Get root ancestor of Barley grain, pearled in the reporting hierarchy :
data _null_;
ancestor = getAncestorAtLevel("A002K", "REPORT", 1);
putlog ancestor=; /* Root ancestor is "A0B6Z" which is "Food" */
run;
Get direct parent of Barley grain, pearled in the reporting hierarchy :
data _null_;
/* Get parent of "Barley grain, pearled" in reporting hierarchy */
/* Since "Barley grain, pearled" is at the 7-th level in the reporting hierarchy, the 6-th level is used to target the direct parent */
parent = getAncestorAtLevel("A002K", "REPORT", 6);
putlog parent =; /* parent is "A000P" which is "Barley grains" */
run;
Print all the parents of Barley grain, pearled in the reporting hierarchy:
data _null_;
i = 1;
do until (i = 8);
parent = getAncestorAtLevel("A002K", "REPORT", i);
putlog i= parent =;
i = i + 1;
end;
run;
function getAncestors(term $, hierarchy $) $ 4000
Returns a string containing the whole set of ancestors (dash-separated) of the specified term in the selected hierarchy. Note that also the term itself is included in the list of ancestors.
Parameters:
term - child term from which the ancestors are retrieved hierarchy - hierarchy in which the ancestors are searched
Returns:
String of ancestors codes dash-separated. if the term code does not exist in the hierarchy, an empty string is returned.
Dependencies:
Requires Business Rules format, add the following to your code: options fmtsearch=(FMTLIB BRS_STG);
Throws:
An error is thrown if a non-existing hierarchy code is passed.
Examples:
Get ancestors of Barley grain, pearled in the reporting hierarchy:
data _null_;
ancestors = getAncestors("A002K", "REPORT");
putlog ancestors=; /* A0B6Z - A000J - A0EZF - A000L - A0D9Y - A000P - A002K */
run;
function isAncestor(term $, target $, hierarchy $)
Checks if the term is a descendant of the target term in the considered hierarchy.
Parameters:
term - Code of the descendant term target - Code of the candidate ancestor term hierarchy - Code of the hierarchy in which the ancestor-descendant relationship is tested
Returns:
Returns 1 if the term is a descendant of the target term in the considered hierarchy, otherwise 0. If a non existing term or non existing target is passed, then 0 is returned.
Dependencies:
Requires Business Rules format, add the following to your code: options fmtsearch=(FMTLIB BRS_STG);
Throws:
An error is thrown if a non-existing hierarchy code is passed.
Examples:
Check if "Cereal grains (and cereal-like grains)" is ancestor of "Barley grain, pearled”:
data _null_;
if isAncestor("A002K", "A000L", "REPORT") then putlog "Yes, it is an ancestor";
else putlog "Not, it is not an ancestor";
run;
function isAttributeRepeatable(catalogueCode $, attrCode $)
Checks if an attribute is repeatable or not, that is, if it can contain several values $-separated.
Parameters:
catalogueCode - The code of the catalogue containing the attribute attrCode - The code of the attribute which should be checked
Returns:
1 if the attribute is repeatable, 0 otherwise. If the attrCode or the catalogueCode do not exist in the CATALOG library, 0 is returned.
Dependencies:
Requires stored macro to be correctly executed. See Macros for further details.
Examples:
Check if the source commodities attribute (F27) of the FoodEx2 catalogue (MTX) is repeatable:
data _null_;
if isAttributeRepeatable("MTX", "F27") then putlog "Yes, it is repeatable";
else putlog "Not, it is not repeatable";
run;
This package contains handy functions for managing the peculiar features of the FoodEx2 catalogue, such as the facets.
function isHierarchyTerm(detailLevel $)
Checks if the detail level of a term is correspondent to an hierarchy. More precisely, it checks if the detail level is = ‘H’.
Parameters:
detailLevel - the detail level of the term to check
Returns:
1 if detailLevel = ‘H’ 0 otherwise.
function isDerivative(termType $)
Checks if the type of term of a term is correspondent to a derivative. More precisely, it checks if the type of term is = ‘d’.
Parameters:
termType - the type of term of the term to check
Returns:
1 if termType= ‘d’ 0 otherwise.
function isComposite(termType $)
Checks if the type of term of a term is correspondent to a composite. More precisely, it checks if the type of term is = ‘c’ or ‘s’.
Parameters:
termType - the type of term of the term to check
Returns:
1 if termType= ‘c’ or termType=’s’, 0 otherwise.
function isRPC(termType $)
Checks if the type of term of a term is correspondent to a raw primary commodity. More precisely, it checks if the type of term is = ‘r’.
Parameters:
termType - the type of term of the term to check
Returns:
1 if termType= ‘r’ 0 otherwise.
function isFacet(termType $)
Checks if the type of term of a term is correspondent to a facet. More precisely, it checks if the type of term is = ‘f’.
Parameters:
termType - the type of term of the term to check
Returns:
1 if termType= ‘f’ 0 otherwise.
function isNonSpecific(termCode $)
Checks if the detail level of the FoodEx2 term is correspondent to non-specific. More precisely, it checks if the detail level of the term is ‘P’.
Parameters:
termCode - the code of term to check
Dependencies:
Requires Business Rules format, add the following to your code: options fmtsearch=(FMTLIB BRS_STG);
Returns:
1 if the detail level is ‘P’, 0 otherwise.
function getBaseTermFromCode(foodexCode $) $ 1000
Extracts the base term from a FoodEx2 code.
Parameters:
foodexCode - FoodEx2 code with syntax baseTerm#facetHeader.facetCode$…
Returns:
The base term contained in the foodexCode
Examples:
data _null_;
base = getBaseTermFromCode("A000A#F01.AHENY$F27.B09IJ");
putlog base=; /* A000A */
run;
function getFacetsFromCode(foodexCode $) $ 1000
Extracts the facets from a FoodEx2 code.
Parameters:
foodexCode - FoodEx2 code with syntax baseTerm#facetHeader.facetCode$…
Returns:
The foodexCode without base term (only facets)
Examples:
data _null_;
facets = getFacetsFromCode("A000A#F01.AHENY$F27.B09IJ");
putlog facets=; /* F01.AHENY$F27.B09IJ */
run;
function getFacetHeader(facet $) $ 1000
Extracts the facet header from a facet with both header and code.
Parameters:
facet - facet composed of header and code, such as F01.A923O
Returns:
The facet header
Examples:
data _null_;
h = getFacetHeader("F01.AHENY");
putlog h=; /* F01 */
run;
function getFacetCode(facet $) $ 1000
Extracts the facet code from a facet with both header and code.
Parameters:
facet - facet composed of header and code, such as F01.A923O
Returns:
The facet code
Examples:
data _null_;
h = getFacetCode("F01.AHENY");
putlog h=; /* AHENY */
run;
function getFacetsHeaders() $ 4000
Extracts every facet header of the MTX catalogue.
Returns:
A string containing in a space separated format all the facets headers of the MTX catalogue
Dependencies:
Requires stored macro to be correctly executed. See Macros for further details.
Examples:
data _null_;
h = getFacetsHeaders();
putlog h=; /* F01 F02 F03 F04 F06 F07 F08 F09 F10 F11 F12 F17 F18 F19 F20 F21 F22 F23 F24 F25 F26 F27 F28 F29 F30 F31 F32 F33 */
run;
function getDistinctFacets(facets $) $ 4000
Removes the facets which are repeated twice for the same facet category, in order to have a clean code without repetitions.
Parameters:
facets - string containing a list of facets $-separated with both header and code
Returns:
A distinct facets list extracted from the facets string, $-separated.
Dependencies:
Requires stored macro to be correctly executed. See Macros for further details.
Examples:
The F27.A026V facet is repeated, therefore the duplicate will be removed.
data _null_;
count = getDistinctFacets("F01.A000A$F27.A026V$F27.A026V");
putlog count=; /* F01.A000A$F27.A026V */
run;
function countFacets(facets $, facetHeader $)
Counts the number of facets in the facets string which have as header the specified facetHeader.
Parameters:
facets - string containing a list of facets $-separated with both header and code facetHeader - the target header the facets must have to be counted by the function
Returns:
The number of facets inside the facets string with the target facetHeader.
Dependencies:
Requires stored macro to be correctly executed. See Macros for further details.
Examples:
Count the number of source commodities inside the facets.
data _null_;
count = countFacets("F01.A000A$F27.B00SB$F27.BSJHR", "F27");
putlog count=; /* 2 */
run;
function countFacetsNotChildOfImplicit(facets $, implicitFacets $, facetHeader $)
Counts the number of facets inside the facets string with header facetHeader which are not descendant of the facets contained in the implicitFacets string. This is helpful for validation purposes, where it is needed to check if the explicit facets are actually descendant of the implicit facet, or not. Note that the term relationships are evaluated in the facet hierarchies identified by the header of the facet (e.g. facets with header F01 are checked in the source hierarchy of the MTX). Note also that in case of multiple implicit facets for the same facet category, the term should not be a descendant of all the implicit facets, in order to be counted.
Parameters:
facets - string containing a list of facets
Returns:
The number of facets inside the facets string with the target facetHeader which are not descendant of the implicit facets.
Dependencies:
Requires MSTORE.catalogues package to be loaded. Requires stored macro to be correctly executed. See Macros for further details.
Examples:
F27.A026V Fish (meat) is not a child of the implicit F27.A02BT Cods, hakes, haddocks, therefore it will be counted.
data _null_;
/* F27.A026V Fish (meat) is not a child of the implicit F27.A02BT */
count = countFacetsNotChildOfImplicit("F27.A026V",
"F02.A0EMT$F27.A02BT$F28.A07KG", "F27");
putlog count=; /* 1 */
run;
function getExplicitNotChildOfImplicit(facets $, implicitFacets $, facetHeader $)$4000
Extracts the facets inside the explicits string with header facetHeader which are not descendant of the facets contained in the implicits string. Note that the term relationships are evaluated in the facet hierarchies identified by the header of the facet (e.g. facets with header F01 are checked in the source hierarchy of the MTX). Note also that in case of multiple implicit facets for the same facet category, the term should not be a descendant of all the implicit facets, in order to be put in output.
Parameters:
facets - string containing a list of facets
Returns:
The facets inside the facets string with the target facetHeader which are not descendant of the implicit facets, $-separated.
Dependencies:
Requires MSTORE.catalogues package to be loaded. Requires stored macro to be correctly executed. See Macros for further details.
Examples:
F27.A026V Fish (meat) is not a child of the implicit F27.A02BT Cods, hakes, haddocks, therefore it will be put in output.
data _null_;
/* F27.A026V Fish (meat) is not a child of the implicit F27.A02BT */
count = getExplicitNotChildOfImplicit("F27.A026V",
"F02.A0EMT$F27.A02BT$F28.A07KG", "F27");
putlog count=; /* F27.A026V */
run;
function checkFacetCardinality(facets $, facetHeader $)
Checks if the cardinality of the facets with header facetHeader is respected or not.
Parameters:
facets - string containing a list of facets $-separated with both header and code facetHeader - the target header the facets must have to be counted by the function
Returns:
1 if the cardinality of the facet identified by the facetHeader is respected, 0 otherwise.
Dependencies:
Requires MSTORE.catalogues package to be loaded Requires stored macro to be correctly executed. See Macros for further details.
Examples:
Source commodity is repeatable and therefore it is possible to specify more than one F27:
data _null_;
if checkFacetCardinality("AhHSS#F01.A000A$F27.B00SB$F27.BSJHR", "F27")
then putlog "Respected";
else putlog "Not respected"; /* Respected */
run;
Source is not repeatable and therefore it is not possible to specify more than one F01:
data _null_;
if checkFacetCardinality("AhHSS#F01.A000A$F01.B00SB$F27.BSJHR", "F01")
then putlog "Respected";
else putlog "Not respected"; /* Not respected */
run;
function getFacetsByCategory(facets $, facetHeader $) $ 4000
Extracts from the facets string the facet which have the selected facet category (i.e. header).
Parameters:
facets - string containing a list of facets $-separated with both header and code facetHeader - the target header the facets must have to be counted by the function
Returns:
A string a list of facets $-separated with both header and code which have as header the passed facetHeader
Dependencies:
Requires stored macro to be correctly executed. See Macros for further details.
Examples:
Get only the F27 facets:
data _null_;
f = getFacetsByCategory("F02.A0EMT$F27.A920K$F27.A02BT$F28.A07KG", "F27");
putlog f=; /* F27.A920K$F27.A02BT */
run;
function removeRedundantExplicitFacets(explicits $, implicits $) $ 4000
Removes the not repeatable explicit facets which are ancestors of the implicit facets. This is because they are redundant and less detailed than the implicit ones. Note that the ancestor-descendant relationship is based on the facet hierarchies, depending on the facet header.
Parameters:
explicits - string containing a list of explicit facets
Returns:
A string containing the explicit facets $-separated with both header and code which are not ancestors of the implicit facets.
Dependencies:
Requires MSTORE.catalogues and MSTORE.strings packages to be loaded Requires stored macro to be correctly executed. See Macros for further details.
Examples:
The explicit facet F01.A059A Amaranth (as plant) is ancestor of the implicit F01.A0E0E Chinese amaranth (as plant) in the SOURCE hierarchy and therefore it will be removed. Note that this happens only because the F01 facet is not repeatable!
data _null_;
f = removeRedundantExplicitFacets("F02.A0EMT$F01.A059A", "F01.A0E0E");
putlog f=; /* F02.A0EMT */
run;
function addImplicitFacets(explicits $, implicits $) $ 4000
Merges the explicit facets with the implicit facets. In particular, if an explicit facet is descendant of an implicit, the implicit facet will not be included in the output because it would be redundant.
Parameters:
explicits - string containing a list of explicit facets
Returns:
A string of merged facets $-separated with both header and code.
Dependencies:
Requires MSTORE.catalogues and MSTORE.strings packages to be loaded Requires stored macro to be correctly executed. See Macros for further details.
Examples:
The explicit facet F01.A0E0E Chinese amaranth (as plant) is ancestor of the implicit A059A Amaranth (as plant) in the SOURCE hierarchy and therefore it will not be included in the output:
data _null_;
merged = addImplicitFacets("F01.A0E0E", "F01.A059A$F27.A026V");
putlog merged=; /* F01.A0E0E$F27.A026V */
run;
function isDeprecated(termCode $)
Checks if an MTX term is deprecated or not.
Parameters:
termCode - Code which identifies the MTX term to check
Returns:
A value of 1 if the termCode is deprecated, otherwise 0.
Dependencies:
Requires BRS_STG formats to be loaded
Examples:
data _null_;
deprecated = isDeprecated("A000A");
putlog deprecated=; /* 0 */
run;
This package contains handy functions for managing tables.
function getNobs(tableName $)
Returns the number of observations of a table.
Parameters:
tableName - name of the table (with library name included)
Returns:
The number of observations of the table.
Throws:
An error is thrown if the table does not exist.
Examples:
data AAA;
test = 1;
run;
data _null_;
nobs = getNobs("AAA");
putlog nobs=; /* 1 */
run;
This package contains handy functions for character variables.
function genRandomString(length)
Returns a random string composed as many characters as specified in the length parameter.
Parameters:
length - The length of the string which will be generated
Returns:
A random generated string
Examples:
data _null_;
string = genRandomString(10);
putlog string=; /* Random generated string of 10 characters */
run;
function removeElementsFromList(source $, elementsToRemove $, separator $) $ 4000
Removes a set of elements from a list of elements separated by a specific separator.
Parameters:
source - string containing a list of element separated by the separator character specified in the input parameters, this is the starting string elementsToRemove - string containing a list of element separated by the separator specified in the input parameters, these elements will be removed from the source separator - the character which separates the elements in source and in elementsToRemove
Returns:
A string containing a list of element separated by the separator character specified in the input parameters, where all the elementsToRemove were removed from the source string.
Examples:
data _null_;
source = "ABC$GBHDSH$SHEH$NBNBNB";
rm = "ABC$SHEH";
elem = removeElementsFromList(source, rm, "$");
putlog elem=; /* GBHDSH$NBNBNB */
run;
This package contains some functions which are specifically used for FoodEx2 validation purposes.
function isGeneric(termCode $);
Returns true if the MTX term is a generic term.
Parameters:
termCode - code of the term to check
Returns:
True if the term is a generic term. A generic term is identified by the following list: "A0C0R", "A0CHR", "A0CHS”
function isAmbiguous(termCode $);
Returns true if the MTX term is an ambiguous term.
Parameters:
termCode - code of the term to check
Returns:
True if the term is an ambiguous term. An ambiguous term is identified by the following list: “A00HQ”
function getForbiddenProcesses(termCode $, termLevel, hierarchy $, configTable $) $4000;
Returns a list containing the forbidden processes (facet F28) which cannot be applied to the specified termCode for the chosen hierarchy. The forbidden processes are identified by using groups of terms which are linked by parent-child relationships. The groups and the processes are defined in the configTable table (see DOCUMENTATION_FOODEX.19 for further details).
Parameters:
termCode - The code of the term whose forbidden processes are retrieved termLevel - The absolute tree level of the term in the hierarchy. Root terms have 1 as termLevel hierarchy - The code of the hierarchy in which the parent-child relationships are evaluated configTable - Table which contains the configuration for the FOODEX.19 business rule
Returns:
A list of $-separated codes of processes which cannot be applied to the chosen termCode. If no process is found, an empty string is returned.
Throws:
An error is thrown if the hierarchy code does not exist or if the configTable does not exist or if it has wrong metadata. An error is thrown if a termLevel equal to 0 is passed.
Examples:
Get the forbidden processes of the term “A000A” in the reporting hierarchy.
data _null;
FORBIDDEN_PROCS = getForbiddenProcesses("A000A", 1, "REPORT", CONFIG_TABLE);
run;
function areProcessesForbidden(term $, termLevel, hierarchy $, processes $, configTable $) $4000;
Returns the group root defined in the configuration if the processes list contains at least one process (or one combination of processes) which is forbidden for the considered term inside the specified hierarchy.
Parameters:
term - The code of the term (base term) on which the processes are applied as explicit facets termLevel - The absolute level of the term in the hierarchy tree. Root terms have as termLevel 1. processes - The list of processes (with header F28) $-separated which are applied to the term as explicit facets configTable - The configuration table which is used to configure the FOODEX.19 business rule
Returns:
The root of the group in which the term lies (the group defines the forbidden processes). If no forbidden process is detected, then an empty string is returned.
Throws:
An error is thrown if the hierarchy code does not exist or if the configTable does not exist or if it has wrong metadata. An error is thrown if a termLevel equal to 0 is passed.
Examples:
Check if it is possible to add the A07KG Drying (dehydratation) to A011Y Legumes fresh seeds (beans, peas etc.).
data _null;
GROUP = areProcessesForbidden("A011Y", 3, "REPORT", "A07KG", CONFIG_TABLE);
run;
This package contains functions related to data enrichment and validation.
function writeErrorTo(id, code $, type $, errorTable $, columns[*] $, values[*] $) varargs;
Adds a data enrichment and validation error to the errorTable.
Parameters:
id - id which identifies the processed row (row number is ok) code - code of the error type - type of error (e.g. warning/error) errorTable - name of the table which contains the errors, if not present it will be created columns - array of columns names which are involved in the error values - array of the values of the columns which are involved in the error. The specified values should be in the same order of the columns array, that is, in the first position of values there is the value of the column specified in the first position of columns.
Returns:
0 if everything went well, 1 otherwise.
Examples:
data AAA;
col1 = "dabs";
col2 = "doask";
output;
col1 = "smadmsam";
col2 = "d,sao,";
output;
run;
data _null_;
set AAA;
array cols(2) $4000 _temporary; cols[1] = "col1"; cols[2] = "col2";
array vals(2) $4000 _temporary; vals[1] = col1; vals[2] = col2;
writeErrorTo(_N_, "ERR001", "Error", "ERR_TABLE", cols, vals);
run;
In the following there are presented the custom formats which were created for EFSA specific purposes.
MTX_DEPRECATED.
Converts the code of an MTX term into a flag which identifies if the term is deprecated or not.
Dependencies:
Requires BRS_STG format library to be loaded.
options fmtsearch=(FMTLIB); /* Required to use formats */
Examples:
data _null_;
deprecated = input("A000A", MTX_DEPRECATED.); /* 0, not deprecated */
run;
$DETAILLEVEL_MTX.
Converts the code of an MTX term into a character which identifies its detail level.
Dependencies:
Requires BRS_STG format library to be loaded.
options fmtsearch=(FMTLIB); /* Required to use formats */
Examples:
data _null_;
detailLevel = put("A000A", $DETAILLEVEL_MTX.); /* H */
run;
REP_MTX. for the master
REP_MTX_[hierarchyCode]. for other hierarchies
Converts the code of an MTX term into a flag which identifies if the term is reportable or not in the specified hierarchy.
Dependencies
Requires FMTLIB format library to be loaded.
options fmtsearch=(FMTLIB); /* Required to use formats */
Examples
data _null_;
rep = input("A0B6Z", REP_MTX_REPORT.); /* 0, not reportable in reporting hierarchy */
rep = input("A0B6Z", REP_MTX.); /* 1, reportable in master */
run;