%BRM_IMPORT_LOOKUP

Imports lookup tables from the specified CSV file into the SAS Decision Manager database.

Requirement: This macro must be run on the server tier.

Syntax

%BRM_IMPORT_LOOKUP (CSV=%STR(input_filename.CSV),
REJECT=%STR(reject_filename.CSV)<, BRM_USER=user_ID>);

Required Arguments

CSV=%STR(input_filename)

specifies the full pathname to the CSV file where you want to import the data from. For more information, see Format of the Lookup CSV Input File.

REJECT=%STR(reject_filename)

specifies the full pathname to the CSV file where you want the macro to write any records that were not imported to the SAS Decision Manager database. See Using the %BRM_IMPORT_LOOKUP Macro for more information.

Optional Argument

BRM_USER=user_ID

specifies the user ID that you want to be associated with the data that is imported. This user ID is associated with the imported objects in the SAS Decision Manager database and is displayed in the interface.

Default User ID of the user that is logged on to the server and running the macro

Details

Using the %BRM_IMPORT_LOOKUP Macro

The %BRM_IMPORT_LOOKUP macro enables you to do the following tasks:
  • add new lookup tables
  • add new key-value pairs to existing lookup tables
  • update (refresh) existing key-value pairs in existing lookup tables
The macro uses the lookup table name and pathname to determine whether a lookup table already exists. If the lookup table already exists, then it is updated. If the pathname exists but the lookup table does not exist, the lookup table is created. If the pathname does not exist, then the lookup table is rejected.
The %BRM_IMPORT_LOOKUP macro runs several validation checks as it imports the lookup tables. For example, the macro checks whether the LOOKUP_NM or NAME columns in the input file are empty or whether the LOOKUP_NM column specifies an invalid lookup name. All valid key-value pairs are imported. If the macro finds an invalid key-value pair in the CSV file, it writes a message to the SAS log, and the key-value pair is rejected. The macro writes the input records for the rejected key-value pairs to the CSV file that was specified in the REJECT= option.

Format of the Lookup CSV Input File

The first row of the input file must be a header row. SAS Business Rules Manager begins importing data with row two.
Each subsequent row of the CSV input file identifies a key-value pair and the lookup table in which it belongs. The CSV file must contain all of the columns listed in the following table, in the order listed. You must specify values for all columns, except as noted in the table. To create a blank column in the CSV file, specify two comma separators without any content between them. The following example specifies the keys AU and CA and associates them with the values Australia and Canada, respectively. These key-value pairs will be imported into the lookup table Country_Codes.
FOLDER_PATH,LOOKUP_NM,DESCRIPTION,NAME,VALUE
Loans/Retail,Country_Codes,,AU,Australia
Loans/Retail,Country_Codes,,CA,Canada
This input file would appear in Microsoft Excel as shown below.
Microsoft Excel file that shows column headers in row 1 and lookup table entries for Australia and Canada on rows 2 and 3
Note: When you import a lookup table with the %BRM_IMPORT_LOOKUP macro, the first line of the input file must be a header row.
Format of the Lookup CSV Input File
Column
Description
Can Column Be Blank
FOLDER_PATH
The pathname to the business rules folder where you want to import the lookup table. This pathname must exist. Separate folder names with forward slashes.
No
LOOKUP_NM
The name of the lookup table.
No
DESCRIPTION
The description of the lookup table.
Yes
NAME
The lookup key.
No
VALUE
The lookup value.
Yes
Last updated: February 22, 2017