Hello everyone! I've spent the whole morning trying to figure this out, so im asking you for help:
The following code works and creates both tables WORK.VENDAS_INDV_MES and DB2VNOT.VENDAS_INDV_MES:
PROC SQL; CREATE TABLE WORK.VENDAS_INDV_MES AS SELECT DISTINCT t1.AAAAMM, t1.NR_UNCO_CTR_OPR, t1.NR_CTR_OPR, t1.CD_CLI, t1.CD_CPNT_RSTD, t1.CD_PRF_RSTD, t1.DT_APRC, t1.MES, t1.ANO, t1.CD_DIRETORIA, t1.NM_DIRETORIA, t1.SUPER, t1.NM_SUPER, t1.CD_GEREV, t1.NM_GEREV, t1.PREFIXO_FUNCI, t1.NM_PREFIXO_FUNCI, t1.CD_PRF_CTRA_CLI, t1.CD_TIP_CTRA_CLI, t1.MAT_FUNCI, t1.NM_FUNCI, t1.FUNCAO, t1.SEXO, t1.CD_ITCE_CNL_ATDT, t1.CD_PRD, t1.CD_MDLD, t1.NM_MDLD, t1.VL_CPNT_RSTD, t1.VL_ULT_SDO, t1.COMPONENTE FROM WORK.INCLUI_NM_MDLD t1 WHERE t1.AAAAMM = &AnoMes; QUIT; proc sql; drop table DB2VNOT.VENDAS_INDV_MES; quit; PROC SQL; CREATE TABLE DB2VNOT.VENDAS_INDV_MES AS SELECT DISTINCT /*t1.AAAAMM,*/ t1.NR_UNCO_CTR_OPR, /*t1.NR_CTR_OPR,*/ /*t1.CD_CLI,*/ t1.CD_CPNT_RSTD, /*t1.CD_PRF_RSTD,*/ t1.DT_APRC, t1.MES, t1.ANO, t1.CD_DIRETORIA, t1.NM_DIRETORIA, t1.SUPER, t1.NM_SUPER, t1.CD_GEREV, t1.NM_GEREV, t1.PREFIXO_FUNCI, t1.NM_PREFIXO_FUNCI, /*t1.CD_PRF_CTRA_CLI,*/ t1.CD_TIP_CTRA_CLI, t1.NM_FUNCI, t1.FUNCAO, t1.SEXO, t1.CD_ITCE_CNL_ATDT, t1.NM_MDLD, t1.VL_CPNT_RSTD, t1.VL_ULT_SDO, t1.COMPONENTE FROM WORK.VENDAS_INDV_MES t1; QUIT;
But, if i uncomment any of the commented columns above i get this error on DB2VNOT.VENDAS_INDV_MES :
ERROR: Error attempting to CREATE a DBMS table. ERROR: CLI execute error: [IBM][CLI Driver][DB2] SQL0104N An unexpected token "(" was found following "". Expected tokens may include: ", ) UNIQUE REFERENCES PRIMARY CONSTRAINT INLINE IMPLICITLY". SQLSTATE=42601 .
I've also tried:
data DB2VNOT.VENDAS_INDV_MES; set WORK.VENDAS_INDV_MES; run;
An got the same error.
None of those columns is suposed to have a UNIQUE Constraint
@Tom got an interesting idea with format 11. and integers but I can't see this really supported by the documentation LIBNAME Statement: Default DB2 Data Types for SAS Variable Formats
I would expect for any numerical SAS variable that hasn't a date, time or datetime format attached to convert to Decimal independent of the format.
Now... based on the error it could be that the conversion to DB2 type DECIMAL(m,n) gets wrongly created because the d component in the SAS format is "missing".
I still feel there is either something not as it should with the access engine or then you're not using a supported driver. That's something SAS Tech Support should be able to support you with.
As you already mentioned a workaround could be to just remove w.d formats. Below some sample code how you could do this programmatically.
data work.vendas_indv_mes;
format num_var1 date9. num_var2 11.;
num_var1=date();
num_var2=45.2;
run;
%let varlist=;
proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where
libname='WORK'
and memname='VENDAS_INDV_MES'
and type='num'
and anydigit(format)=1
;
quit;
data work.v_vendas_indv_mes/view=work.v_vendas_indv_mes;
set work.vendas_indv_mes;
format &varlist;
run;
/** and here the SQL that loads the table into DB2 **/
/** - to just load a whole table Proc Append is faster **/
proc contents data=work.v_vendas_indv_mes;
run;quit;
proc print data=work.v_vendas_indv_mes;
run;
SAS Notes include a few similar DB2 errors relating to unexpected tokens, like this one: https://support.sas.com/kb/50/686.html
The suggestion is that the underlying DDL is not well-formed. Adding this SAS option might provide more evidence: options sastrace = ',,,d' SASTRACELOC = SASLOG;
I am wondering if the "drop table" you attempt is actually removing metadata like constraints.
I also would not be surprised to see table created with Select Distinct to end up with a unique constraint as that is the effect of the Distinct. You don't mention which any of the connection details you use that might influence such a creation.
Do you get the same error if you create a brand new never been used table name in DB2VNOT?
I thought about this too and tested it by removing the DISTINCT. The result was the same. Sorry for not mentioning this on the original post
How are the variables you commented out different than the one you included?
Different TYPE? Different LENGTH? Do they have different FORMATs attached to them?
The problematic variables are all numeric and have format and informat 11. or 18. :Formats
For testing, i changed the code to remove all format:
data WORK.VENDAS_INDV_MES;
set WORK.VENDAS_INDV_MES;
FORMAT _all_;
INFORMAT _all_;
run;
proc sql;
drop table DB2VNOT.VENDAS_INDV_MES;
quit;
PROC SQL;
CREATE TABLE DB2VNOT.VENDAS_INDV_MES AS
SELECT DISTINCT t1.AAAAMM,
t1.NR_UNCO_CTR_OPR,
t1.NR_CTR_OPR,
t1.CD_CLI,
t1.CD_CPNT_RSTD,
t1.CD_PRF_RSTD,
t1.DT_APRC,
t1.MES,
t1.ANO,
t1.CD_DIRETORIA,
t1.NM_DIRETORIA,
t1.SUPER,
t1.NM_SUPER,
t1.CD_GEREV,
t1.NM_GEREV,
t1.PREFIXO_FUNCI,
t1.NM_PREFIXO_FUNCI,
t1.CD_PRF_CTRA_CLI,
t1.CD_TIP_CTRA_CLI,
t1.NM_FUNCI,
t1.FUNCAO,
t1.sexo,
t1.CD_ITCE_CNL_ATDT,
t1.NM_MDLD,
t1.VL_CPNT_RSTD,
t1.VL_ULT_SDO,
t1.COMPONENTE
FROM WORK.VENDAS_INDV_MES t1;
QUIT;.
And it sucessfully created the table on DB2VNOT.
Is there anything I can do to avoid being forced to change formats?
This starts to feel like something you should raise with SAS Tech Support.
SAS will normally use the format attached to a variable to guess what type to use when making a variable in a remote databases. Something with format like 11. that displays only integers might be converted to a DECIMAL() type.
You might check if any of the values of those variables are not actually integers. Say 10.25. Such a value could not be storing into a variable that only accepts integers.
Note that you can use the DBTYPE= dataset option to control the type that SAS uses to define the variable in the remote database.
@Tom got an interesting idea with format 11. and integers but I can't see this really supported by the documentation LIBNAME Statement: Default DB2 Data Types for SAS Variable Formats
I would expect for any numerical SAS variable that hasn't a date, time or datetime format attached to convert to Decimal independent of the format.
Now... based on the error it could be that the conversion to DB2 type DECIMAL(m,n) gets wrongly created because the d component in the SAS format is "missing".
I still feel there is either something not as it should with the access engine or then you're not using a supported driver. That's something SAS Tech Support should be able to support you with.
As you already mentioned a workaround could be to just remove w.d formats. Below some sample code how you could do this programmatically.
data work.vendas_indv_mes;
format num_var1 date9. num_var2 11.;
num_var1=date();
num_var2=45.2;
run;
%let varlist=;
proc sql noprint;
select name into :varlist separated by ' '
from dictionary.columns
where
libname='WORK'
and memname='VENDAS_INDV_MES'
and type='num'
and anydigit(format)=1
;
quit;
data work.v_vendas_indv_mes/view=work.v_vendas_indv_mes;
set work.vendas_indv_mes;
format &varlist;
run;
/** and here the SQL that loads the table into DB2 **/
/** - to just load a whole table Proc Append is faster **/
proc contents data=work.v_vendas_indv_mes;
run;quit;
proc print data=work.v_vendas_indv_mes;
run;
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.