Problem Note 61097: The customer risk ranking (CRR) program fails when SAS® Customer Due Diligence uses Microsoft SQL Server
 |  |  |  |  |
You encounter the following error when you run the CRR process in SAS® Anti-Money Laundering and the software is configured with Microsoft SQL Server as the database:
ERROR: CLI execute error: [Microsoft][ODBC SQL Server Driver][SQLServer]Incorrect syntax near the keyword 'ON'.
The error is caused by incorrect syntax in an SQL procedure in the %CDD_CRR_ENTITY_FACT_UPDT macro. A parenthesis is placed, incorrectly, after an alias named data. The parenthesis should be placed before the alias. To work around the problem, follow these steps:
- Open the cdd_crr_entity_fact_updt.sas file, which is located in the !SASROOT/ucmacros/antimnycmn directory in UNIX and LINUX operating environments and in the !SASROOT\antimnycmn\ucmacros folder in Microsoft Windows operating environments.
- Locate the PROC SQL code that is shown below. Note the parenthesis that appears after the alias data, as shown in this code.
proc sql feedback noprint;
connect to &dbflavor as &dbflavor (authdomain="&segkcAuthDomain." &segKCDBConnOpts);
/*----------------------------------------------------------
* Update FSK_CD_ENTITY_FACT.LAST_SCORING_EVENT_KEY
* This step takes a while to run.
*---------------------------------------------------------*/
execute(
MERGE INTO &segKCSchema..fsk_cdd_entity_fact
USING (
SELECT
temp_char_1_value AS entity_number,
temp_num_1_value AS scoring_event_key
FROM &segKCSchema..fsk_temp_table
data )
ON (primary_entity_number=entity_number AND primary_entity_level_code='PTY')
WHEN MATCHED THEN
UPDATE
SET last_scoring_event_key=scoring_event_key
%if %upcase(&dbflavor) eq ODBC %then %do;
;
%end;
) by &dbflavor. ;
disconnect from &dbflavor;
quit;
connect to &dbflavor as &dbflavor (authdomain="&segkcAuthDomain." &segKCDBConnOpts);
/*----------------------------------------------------------
* Update FSK_CD_ENTITY_FACT.LAST_SCORING_EVENT_KEY
* This step takes a while to run.
*---------------------------------------------------------*/
execute(
MERGE INTO &segKCSchema..fsk_cdd_entity_fact
USING (
SELECT
temp_char_1_value AS entity_number,
temp_num_1_value AS scoring_event_key
FROM &segKCSchema..fsk_temp_table
data )
ON (primary_entity_number=entity_number AND primary_entity_level_code='PTY')
WHEN MATCHED THEN
UPDATE
SET last_scoring_event_key=scoring_event_key
%if %upcase(&dbflavor) eq ODBC %then %do;
;
%end;
) by &dbflavor. ;
disconnect from &dbflavor;
quit;
- Move the parenthesis before the alias data, as shown below:
) data
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Customer Due Diligence | Microsoft® Windows® for x64 | 7.1 | |||
| 64-bit Enabled AIX | 7.1 | |||||
| 64-bit Enabled Solaris | 7.1 | |||||
| Linux for x64 | 7.1 | |||||
A fix for this issue for SAS Compliance Solutions 7.1 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/A5T.html#61097| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2017-09-29 13:48:07 |
| Date Created: | 2017-09-15 10:47:21 |