ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: syntax error at or

[Gweeks]

Hello everyone, 

I've been tasked with registering a SAP table and pushing that table into Postgres within DI studio. I've registered the SAP table into SAS and once I try to create the table for Postgres it gives me the following error:

ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR: syntax error at or near "/" at character 3222; Error while executing the query. I've also tried to use user written code but it still gives out the same error. I'm still fairly new with SAS DI studio and writing tables into databases.

Your help will be greatly appreciated.

Gweek.

[Kurt_Bremser]

Please post the complete log of the PROC SQL step. Use this button for posting the log:

[Gweeks]

164        proc sql;
165        	create table pg_ecc."VDARL"n as
166          		select * from SASECC."VDARL"n;
ODBC: AUTOCOMMIT is NO for connection 1 0 1615459564 no_name 0 SQL (2) 
ODBC: AUTOCOMMIT turned ON for connection id 1 1 1615459564 no_name 0 SQL (2) 
  2 1615459564 no_name 0 SQL (2) 
ODBC_1: Prepared: on connection 1 3 1615459564 no_name 0 SQL (2) 
SELECT * FROM sap_ecc.VDARL WHERE 0=1  4 1615459564 no_name 0 SQL (2) 
  5 1615459564 no_name 0 SQL (2) 
ODBC: AUTOCOMMIT is NO for connection 2 6 1615459564 no_name 0 SQL (2) 
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
  7 1615459564 no_name 0 SQL (2) 
ODBC_2: Executed: on connection 2 8 1615459564 no_name 0 SQL (2) 
CREATE TABLE sap_ecc.VDARL (MANDT int2,BUKRS varchar(12),SARCHIV varchar(3),RANL varchar(39),RERF varchar(36),DERF date,TERF 
time,REHER varchar(30),RBEAR varchar(36),DBEAR date,TBEAR time,RBHER varchar(30),SOBJEKT varchar(30),SSTATI varchar(2),SANLF 
varchar(3),GSART varchar(9),RREFKONT varchar(24),HBKID varchar(15),HKTID varchar(15),STITEL varchar(3),SGLZB varchar(12),SFIBG 
varchar(4),SBILK varchar(6),XALKZ varchar(45),XALLB varchar(180),XAKT varchar(96),RANLALT1 varchar(39),RANLALT2 varchar(39),SGR1 
varchar(18),SGR2 varchar(18),RKLAMMER varchar(39),RANLKET varchar(39),RANTRAG varchar(39),SSOLIST varchar(3),SFRIST 
varchar(2),BANTRAG numeric(14,2),SANTWHR varchar(15),DANTRAG date,BZUSAGE numeric(14,2),DZUSAGE date,DZUSAGE_ORIGIN date,DBLFZ 
date,DELFZ date,DBDIS date,SDISMETH varchar(1),SSICHER varchar(12),SVZWECK varchar(6),SSONDER varchar(2),SFIWO varchar(6),SDTI 
varchar(5),SHOME varchar(3),SLAENDER varchar(9),AABTRET varchar(2),BABTRET numeric(14,2),SNICHTAB varchar(2),DARCHIV date,BGESDAR 
numeric(14,2),DKONS date,SKWGKRED varchar(1),SKWGDAR varchar(1),JORIG varchar(1),VVSLOEKZ varchar(1),SDTYP varchar(3),DBEWI 
date,SABTRANS varchar(3),AKONS varchar(3),SFGKZ varchar(3),SABT varchar(12),MABER varchar(6),MAHNS varchar(1),MADAT date,RPNR 
varchar(8),RSBEAR varchar(36),SONDST varchar(6),SGRP3 varchar(18),SGRP4 varchar(18),SZKEY varchar(2),BEGRU varchar(12),SVERFU 
varchar(2),STITART varchar(2),NABTRV varchar(45),NAVAL varchar(45),DABTR date,DVERFU date,SFGBEW varchar(2),DELSOZ date,RPORTB 
varchar(30),SREGION varchar(12),SGESST varchar(12),SBEA varchar(2),SOBEZ varchar(6),SBEZIRK varchar(12),SPLIT varchar(3),BNBUERG 
numeric(14,2),SREFZINS varchar(3),SMAHNM varchar(3),SIHERK varchar(6),RGENUM varchar(30),DRUECK date,SABGRENZ varchar(3),SFEST 
varchar(3),SRATING varchar(9),SRATAGEN varchar(30),RDARNEHM varchar(30),OBJNR varchar(66),HDN_KUNNR varchar(30),SKWG14_NEU 
varchar(6),RANLSTAMNR varchar(39),MIGDATE date,SDUNNCMP varchar(6),XNODUNN varchar(3),ZUOND varchar(54),REFER varchar(48),MERKM 
varchar(75),SDUNCHARG varchar(3),SWHRORG varchar(15),DCONVERT date,DCORRECT date,SAKTPAS varchar(3),RPZAHL varchar(30),GSBER 
varchar(12),JNOREPAY varchar(3),JNODOIP varchar(3),COM_VAL_CLASS varchar(4),SARC_EXCL varchar(3),SARC_DOCS varchar(3),NO_PLAN_DONE 
varchar(3),DPLAN_SET date,DOPEN date,DCLOSE date,DUNN_BLOCK_TILL date,XNOIOA varchar(3),NOIOA_TILL date,ORIGIN varchar(6),DCALLEND 
date,SVER_EXCL varchar(3),LC_DISBURSEMENT varchar(3),LC_CAPITALS varchar(3),LC_PLANNED_FLOW varchar(3),LC_DEBIT 
varchar(3),LC_CREDIT varchar(3),LC_STATUS varchar(3),PAYM_STATUS varchar(3),STOP_INDICATOR varchar(3),PRODUCT_ID 
varchar(54),PRODUCT_VERSION varchar(4),SCHUFA_MESSAGE varchar(3),DAT_FULL_PAY date,DAT_OF_CANCEL date,FLG_CNCL varchar(3),DINSTR 
date,ORGUNIT varchar(36),DISTRIB_CHANNEL varchar(30),FLG_CNCL_CLI varchar(3),FLG_NO_CHANGE varchar(3),SCHEME_ID 
varchar(9),SSTATE_OF_EXEC varchar(9),WAIVE_REASON varchar(9),WAIVE_METHOD varchar(1),BACKOFFICE_ID varchar(8),FLG_NON_ACCRUAL 
varchar(3),SFIMA_METHOD varchar(3),GRACE_DAYS varchar(3),NOIOA_TILL_BLOCK date,FLG_SYNDICATE varchar(3),FLG_FACILITY 
varchar(3),SHADOW_ACCNT varchar(3),SKWG14_OPEN varchar(6),BPCA_ID varchar(96),BPCA_TYPE_CODE varchar(18),/IZAZI/FACILITY 
varchar(39),/IZAZI/FUND varchar(39),/IZAZI/ZINDT date,AC_SCI varchar(180),AC_RECOURSE varchar(1),AC_PFL varchar(1),AC_TOS 
varchar(2),COICODE varchar(6),COILAND varchar(6),DCAC_CLASS varchar(3),DCAC_CURR varchar(15),OPTIMAL_LIST 
varchar(3),INSR_CONTRACT_ID varchar(51),INSR_OBJ_CAT varchar(6),INSR_OBJ_ID varchar(60),INSR_COND_STRUC varchar(9),RP_RELEVANT 
varchar(3),CONTRACT_BIC varchar(33),CONTRACT_IBAN varchar(102),IBAN_DET varchar(3),FAC_GROUP_NUMBER varchar(39),FLG_DD_REVOLVING 
varchar(3),FLG_FAC_INACTIVE varchar(3),OBJ_ID varchar(150),OBJ_KEY varchar(60),OBJ_OFFSET numeric(11,0),FLP varchar(3),FLP_NUMBER 
varchar(39),VDBEPP_DBEAR date,VDBEPP_TBEAR time,VDBEPP_RBHER varchar(30),RESERVED_AMOUNT numeric(14,2),ZZEASYPAY 
varchar(20),ZZVBELN varchar(30),ZZPMTLASTAMNT numeric(14,2),ZZPMTFIRSTDATE date,ZZPMTLASTDATE date,ZZINTACCRUED 
numeric(14,2),ZZPMTUNPAIDNUM varchar(3),ZZDUEMONTHAMNT numeric(14,2),ZZINSURETAMNT numeric(14,2),ZZINSUREAMNT 
numeric(14,2),ZZINSURESTAT varchar(3),ZZINSURECDTE date,ZZINSURECRES varchar(3),ZZINSURELAMNT numeric(14,2),ZZINSURELSTAT 
varchar(3),ZZINSURELCDTE date,ZZINSURELCRES varchar(3),ZZINDUPAMNT numeric(14,2),ZZINDUPDATE date,ZZINDUP_BLOCK_X 
varchar(3),ZZCLUBTYPE varchar(9),ZZBLOCKCODEDATE date,ZZDIGIT varchar(13),ZZDODATE date,ZZDOCANCEL_X varchar(3),ZZDOCANCELDATE 
7                                                          The SAS System                         Thursday 11 March 2021 12:46:00 PM

date,ZZDOREASONCODE varchar(12),ZZDOAMOUNT numeric(14,2),ZZUSER5DATE date,ZZOPENDATE date,ZZDEFAULTDATE date,ZZDEFAULTAMNT 
numeric(14,2),ZZSTMT_OPTION varchar(3),ZZSACCRAPAID_X varchar(3),ZZSACCRAPAIDDATE date,ZZPRINDEBT numeric(14,2),ZZSTITART_BLOCK_X 
varchar(3),ZZLOGO varchar(3),ZZINITIALINTEREST numeric(14,2),ZZACTION_ID varchar(12),ZZ_ACTION_DATE date,ZZVKBUR 
varchar(12),ZZSRN_OLD varchar(30),ZZCD_CUST varchar(2),ZZCML_MNTH_INST numeric(14,2),ZZDATE_SCORE varchar(18),ZZSCORECARD_ID 
varchar(15),ZZBEHAVIOUR_SCORE varchar(9),ZZ_VAT_RATE numeric(6,2),ZZ_FS_INV_DOC_NR varchar(30),ZZMANDATEREFERENCE varchar(105)) 9 
1615459564 no_name 0 SQL (2) 
  10 1615459564 no_name 0 SQL (2) 
ODBC: COMMIT performed on connection 2. 11 1615459564 no_name 0 SQL (2) 
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: ERROR:  syntax error at or near "/" at character 
       3218;
ODBC: ROLLBACK performed on connection 2. 12 1615459564 no_name 0 SQL (2) 
ODBC: COMMIT performed on connection 2. 13 1615459564 no_name 0 SQL (2) 
WARNING: File deletion failed for PG_ECC.VDARL.DATA.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
167        quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: Due to ERROR(s) above, SAS set option OBS=0, enabling syntax check mode. 
      This prevents execution of subsequent data modification statements.
NOTE: PROCEDURE| _DISARM|         STOP| _DISARM| 2021-03-11T12:46:04,829+02:00| _DISARM| WorkspaceServer| _DISARM| SAS| _DISARM| | 
      _DISARM| 17739776| _DISARM| 13012992| _DISARM| 11| _DISARM| 11| _DISARM| 0| _DISARM| 320| _DISARM| 0.010000| _DISARM| 
      0.016280| _DISARM| 1931078764.813059| _DISARM| 1931078764.829339| _DISARM| 0.010000| _DISARM| | _ENDDISARM 
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              6609.18k
      OS Memory           17324.00k
      Timestamp           2021/03/11 12:46:04 PM
      Step Count                        3  Switch Count  0
      Page Faults                       0
      Page Reclaims                     1119
      Page Swaps                        0
      Voluntary Context Switches        25
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

@Kurt_Bremser I've included the log of the Proc SQL step.

[Kurt_Bremser]

I take it that a name like

/IZAZI/FACILITY

is not valid in the target DBMS. 

[Gweeks]

I've created tables with a name like that and no error was populated.

[Kurt_Bremser]

From the log it's clear that the forward slashes are the problem. Keep in mind that slashes are the symbol for a division, and are expected only between elements of a calculation.

[Tom · Accepted solution]

How did you define the libref?
SAS probably just needs to add quotes around those goofy names when it generates the SQL for you.

,"/IZAZI/FUND" varchar(39)

There might be a option you can add the LIBNAME statement that will tell it to do that.

[Gweeks]

Thank you. I had to include the PRESERVE_COL_NAMES= option to the libname statement as well as the DQUOTE= option to the proc sql statement.