SUPPORT / SAMPLES & SAS NOTES
 

Support

Problem Note 58918: Code rounds large _SK variable values, causing incorrect values to be stored in the cxtchfg.cnfg.Internal_promo_sk_max_sk_values table

DetailsHotfixAboutRate It

In SAS® Customer Experience Targeting, the following code rounds large SK values, which causes incorrect values to be stored in the cxtcnfg.Internal_promo_sk_max_sk_values table.

%if %sysfunc(exist(&new_md5_tbl)) %then %do; /* New surrogate keys possibly assigned */ /* Retrieve the highest _SK variable assigned */ proc sql noprint; select max(&sk_variable) into :max_sk from &new_md5_tbl; quit; %end; /* New surrogate keys are possibly assigned */ %else %let max_sk=0; /* No new surrogates assigned if the table doesn't exist */ /* It is possible that no new surrogate keys are assigned. */ /* In that case, you want to be sure to retain the original */ /* _SK value. Otherwise, take the highest, new one. */ data &curr_max_out; attrib max_sk format=8.; max_sk=max(&&&sk_variable._value, &max_sk); output; run;

When the code above fails, the following error message appears in the SAS Customer Experience Targeting CXT_ETL_cxt_2022_load_custom_attributes job:

MPRINT(CXT_DB_TBL_APPEND): ; MPRINT(CXT_DB_TBL_APPEND): data vSTAGE_CUST_ATTR (drop= ) / view=vSTAGE_CUST_ATTR; MPRINT(CXT_DB_TBL_APPEND): set CXTSTAGE.STAGE_CUST_ATTR; NOTE: Line generated by the macro variable "LOAD_DTTM". 623 "0 __ 77 ERROR: Invalid date/time/datetime constant "0"dt. 25 Das SAS System 08:16 Thursday, August 4, 2016 MPRINT(CXT_DB_TBL_APPEND): load_dttm="0"dt; MPRINT(CXT_DB_TBL_APPEND): run; ERROR 77-185: Invalid number conversion on "0"dt.

To resume the ETL flows, follow the steps in this workaround:

  1. Back up the cxtcnfg.Internal_promo_sk_max_sk_values and cxtcnfg.Internal_promotion_md5 tables.
  2. Run the reset job, and save the log for future reference.
  3. Submit the following code update.
    proc sql; update cxtcnfg.Internal_promo_sk_max_sk_values set sk_value=101470260 where sk_value=101470000; quit;
  4. After the update runs, confirm that the value in the table for MAX(SK_VALUE) is 101470260.
  5. Place the attached file in the ucmacros folder (which resides in a location similar to .../SASHome/SASFoundation/9.4/ucmacros/cxasvr/). You should see *_Clean_*.sas files in this directory.
  6. Put the following SAS statement in the appserver_autoexec_usermods.sas file (which is located in a directory path similar to SAS-configuration-directory\Lev1\SASApp\).
    options mlogic mprint symbolgen;
    Adding this line enables you to go through the logs easily while you are debugging.
  7. Run the ETL flow again.
  8. If the flow fails, send all of the log files to SAS Technical Support.


Operating System and Release Information

Product FamilyProductSystemProduct ReleaseSAS Release
ReportedFixed*ReportedFixed*
SAS SystemSAS Customer Experience AnalyticsMicrosoft® Windows® for x646.4
Microsoft Windows 8 Enterprise 32-bit6.4
Microsoft Windows 8 Enterprise x646.4
Microsoft Windows 8 Pro 32-bit6.4
Microsoft Windows 8 Pro x646.4
Microsoft Windows 8.1 Enterprise 32-bit6.4
Microsoft Windows 8.1 Enterprise x646.4
Microsoft Windows 8.1 Pro 32-bit6.4
Microsoft Windows 8.1 Pro x646.4
Microsoft Windows 106.4
Microsoft Windows 95/986.4
Microsoft Windows 2000 Advanced Server6.4
Microsoft Windows 2000 Datacenter Server6.4
Microsoft Windows 2000 Server6.4
Microsoft Windows 2000 Professional6.4
Microsoft Windows NT Workstation6.4
Microsoft Windows Server 2003 Datacenter Edition6.4
Microsoft Windows Server 2003 Enterprise Edition6.4
Microsoft Windows Server 2003 Standard Edition6.4
Microsoft Windows Server 2003 for x646.4
Microsoft Windows Server 20086.4
Microsoft Windows Server 2008 R26.4
Microsoft Windows Server 2008 for x646.4
Microsoft Windows Server 2012 Datacenter6.4
Microsoft Windows Server 2012 R2 Datacenter6.4
Microsoft Windows Server 2012 R2 Std6.4
Microsoft Windows Server 2012 Std6.4
Microsoft Windows XP Professional6.4
Windows 7 Enterprise 32 bit6.4
Windows 7 Enterprise x646.4
Windows 7 Home Premium 32 bit6.4
Windows 7 Home Premium x646.4
Windows 7 Professional 32 bit6.4
Windows 7 Professional x646.4
Windows 7 Ultimate 32 bit6.4
Windows 7 Ultimate x646.4
Windows Millennium Edition (Me)6.4
Windows Vista6.4
Windows Vista for x646.4
Linux for x646.4
* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.