business key"n, a syntax error occurs when that variable name does not follow standard SAS® naming conventions." />
SUPPORT / SAMPLES & SAS NOTES
 

Support

Problem Note 64285: The SCD Type 2 Loader transformation in SAS® Data Integration Studio generates "ERROR 22-322: Syntax error, expecting one of the following:..."

DetailsHotfixAboutRate It

When you use the slowly changing dimensions (SCD) Type 2 Loader transformation and a business key column has special characters in its name, you receive a syntax error. An example portion of the SAS® log is shown here:

MPRINT(ETLS_SCDLOADER):   data work.etls_sortedxref(keep = "business key"n "begin date"n "end date"n DIGEST_VALUE);
MPRINT(ETLS_SCDLOADER):   length etls_md5 $16.;
MPRINT(ETLS_SCDLOADER):   length DIGEST_VALUE $32.;
MPRINT(ETLS_SCDLOADER):   set work.etls_sortedxref;
MPRINT(ETLS_SCDLOADER):   by "business key"n "end date"n;
NOTE: Line generated by the invoked macro "ETLS_SCDLOADER".
655         key;  etls_str = '"' ||TRIM(LEFT("Name"n)) || TRIM(LEFT("Sex"n))        || TRIM(LEFT("Age"n)) || TRIM(LEFT("Height"n))
            ___
            22
655      !       || TRIM(LEFT("Weight"n)) || '"'; etls_md5 = md5(etls_str); DIGEST_VALUE = put(etls_md5, hex32.);  run;
MPRINT(ETLS_SCDLOADER):   if last.business key;
MPRINT(ETLS_SCDLOADER):   etls_str = '"' ||TRIM(LEFT("Name"n)) || TRIM(LEFT("Sex"n)) || TRIM(LEFT("Age"n)) || TRIM(LEFT("Height"n))
|| TRIM(LEFT("Weight"n)) || '"';
MPRINT(ETLS_SCDLOADER):   etls_md5 = md5(etls_str);
MPRINT(ETLS_SCDLOADER):   DIGEST_VALUE = put(etls_md5, hex32.);
MPRINT(ETLS_SCDLOADER):   run;
ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, -, /, <, <=, <>, =, >, ><, >=, AND, EQ, GE, GT, IN,
              LE, LT, MAX, MIN, NE, NG, NL, NOTIN, OR, ^=, |, ||, ~=. 
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
      655:87    655:109   655:141  
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
WARNING: The data set WORK.ETLS_SORTEDXREF may be incomplete.  When this step was stopped there were 0 observations and 4 variables.
WARNING: Data set WORK.ETLS_SORTEDXREF was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.03 seconds
      cpu time            0.00 seconds

The error occurs because of an incorrect LAST. reference to the business key:

if last.business key;

Use of the following workarounds to circumvent this problem. These workarounds use the business key example shown above.

  • Workaround 1
    • Rename the column with a name that does not require the column to be referenced as a name literal. An example is business_key.
  • Workaround 2
    • Select Properties for the transformation. On the Code tab in the properties window, in the Code generation mode field, select User written body. Then locate the IF LAST. line in the DATA step that creates the data set WORK.ETLS_SORTEDXREF, and change the line to read as follows:

         if "last.business key"n ;

      Note: The quotation marks must go around the entire reference, not just around business key.

Click the Hot Fix tab in this note to access the hot fix for this issue.



Operating System and Release Information

Product FamilyProductSystemProduct ReleaseSAS Release
ReportedFixed*ReportedFixed*
SAS SystemSAS Data Integration StudioMicrosoft Windows Server 2012 Datacenter4.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 104.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 8.1 Pro x644.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 8.1 Pro 32-bit4.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 8.1 Enterprise x644.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 8.1 Enterprise 32-bit4.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 8 Pro x644.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 8 Pro 32-bit4.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 8 Enterprise x644.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows 8 Enterprise 32-bit4.9044.9059.4 TS1M69.4 TS1M7
Microsoft® Windows® for x644.9044.9059.4 TS1M69.4 TS1M7
Windows 7 Ultimate x644.9044.9059.4 TS1M69.4 TS1M7
Windows 7 Ultimate 32 bit4.9044.9059.4 TS1M69.4 TS1M7
Windows 7 Professional x644.9044.9059.4 TS1M69.4 TS1M7
Windows 7 Professional 32 bit4.9044.9059.4 TS1M69.4 TS1M7
Windows 7 Home Premium x644.9044.9059.4 TS1M69.4 TS1M7
Windows 7 Home Premium 32 bit4.9044.9059.4 TS1M69.4 TS1M7
Windows 7 Enterprise x644.9044.9059.4 TS1M69.4 TS1M7
Windows 7 Enterprise 32 bit4.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows Server 20194.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows Server 20164.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows Server 2012 Std4.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows Server 2012 R2 Std4.9044.9059.4 TS1M69.4 TS1M7
Microsoft Windows Server 2012 R2 Datacenter4.9044.9059.4 TS1M69.4 TS1M7
* 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.