topic Re: insert SAS table into teradata in SAS Programming

insert SAS table into teradata

shuchidxt_gmail_com — Fri, 28 Feb 2020 21:20:52 GMT

Hi,

I am trying to load Teradata table from SAS wrkmrg.ava_prty_lt3 TABLE and getting below error but there is no value in the table which is missing. what is wrong here?

proc sql;
create table wrkmrg.ava_prty_lt3 as
   select distinct
input(compress(trim("&req_id.")),8.) length = 10 as request_id,

   'CAAPTest' length = 30 as USER_ID,           /*user */
   PARTY_ID1 format = 11.0 as WCIS_CLIENT_ID , /* client id*/
   'CAAPGroup' length = 50 as REQUEST_DEPT, /*department name */
   intnx('day',today(),0) format = yymmddd10. as REQUEST_DATE,   /* request date is current date*/
'NEW' length = 100 as STATUS

FROM wrkmrg.ava_prty_lt2
order by party_id1;

proc sql noprint ;
INSERT INTO MCLP.WCIS_CLNT_DISP_IN (multistmt = yes)
SELECT distinct
   request_id,
user_id,
   wcis_client_id,
   request_dept,
request_date,
status
   FROM wrkmrg.ava_prty_lt3;

ERROR: Teradata insert: Syntax error: Data Type "," does not match a Defined Type name.

Re: insert SAS table into teradata

s_lassen — Sat, 29 Feb 2020 06:51:05 GMT

I am not sure, but the problem may stem from the REQUEST_ID variable. LENGTH=10 is not applicable to numeric variables. Is the variable numeric on Teradata? If not, you should convert it to character. What exactly are you trying to do with that variable?

Other than that, if you have a problem with missing values, it may be that the Teradata table has more columns than the table you are trying to insert from. In which case you should specify the variables on the target table on the insert statement (good programming practice anyway), e.g.

insert into <teradata table>(multistmt=yes)(<target variables>)
  select <source variables>
  from <sas table>

Re: insert SAS table into teradata

shuchidxt_gmail_com — Tue, 03 Mar 2020 19:56:55 GMT

yes the variable is decimal 10.0 field in the Teradata. I have checked Teradata table and it has same fields/columns.

below is the ddl for the same.

CREATE MULTISET TABLE P_WDM_S_WCIS_UMD.WCIS_CLNT_DISP_IN ,NO FALLBACK ,

NO BEFORE JOURNAL,

NO AFTER JOURNAL,

CHECKSUM = DEFAULT,

DEFAULT MERGEBLOCKRATIO,

MAP = TD_MAP2

(

REQUEST_ID DECIMAL(10,0),

USER_ID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,

WCIS_CLIENT_ID DECIMAL(10,0),

REQUEST_DEPT VARCHAR(50) CHARACTER SET LATIN NOT CASESPECIFIC,

REQUEST_DATE DATE FORMAT 'YYYY-MM-DD',

STATUS VARCHAR(100) CHARACTER SET LATIN NOT CASESPECIFIC)

PRIMARY INDEX PI_WCIS_CLNT_DISP_IN ( REQUEST_ID ,USER_ID ,WCIS_CLIENT_ID ,

REQUEST_DEPT );

Re: insert SAS table into teradata

ChrisNZ — Tue, 03 Mar 2020 22:03:05 GMT

> the variable is decimal 10.0 field in the Teradata

In this case why not just use

&req_id. as request_id,

Also this should be enough as well, no?
today() format = yymmddd10. as REQUEST_DATE,

Re: insert SAS table into teradata

Tom — Tue, 03 Mar 2020 22:17:06 GMT

Show the actual log. Make sure to use the INSERT CODE button and paste the lines into the pop-up window so that formatting is preserved.

Remember that SAS only has two data types. Fixed length character strings and floating point numbers.

If you have defined the variable as 10 decimal places in Teradata then make sure that non of the values of your data are non integers

request_id=round(request_id,1);

and that the absolute value is always less then 10,000,000,000. Otherwise Teradata will not be able to store the value.

if abs(request_id) >= 10E10 then put 'ERROR: Magnitude too large ' request_id= ;

Re: insert SAS table into teradata

shuchidxt_gmail_com — Tue, 03 Mar 2020 23:10:22 GMT

I have tried that well but it did not work

Re: insert SAS table into teradata

shuchidxt_gmail_com — Tue, 03 Mar 2020 23:12:06 GMT

Here is the snippet of the log error:

287        proc sql noprint ;
288        INSERT INTO MCLP.WCIS_CLNT_DISP_IN(multistmt = yes)
289          SELECT distinct * FROM wrkmrg.ava_prty_lt3;
NOTE: 7860 rows were inserted into MCLP.WCIS_CLNT_DISP_IN.

ERROR: Teradata insert: Syntax error: Data Type "," does not match a Defined Type name. SQL statement was: USING (A0A
DECIMAL(10),A0B VARCHAR (30),A0C DECIMAL(10),A0D VARCHAR (50),A0E DATE,A0F VARCHAR (100),A0G D

Though there is no data having , and the input data looks like as below:

995	CAAPTest	51	CAAPGroup	02/28/2020	NEW
995	CAAPTest	83	CAAPGroup	02/28/2020	NEW
995	CAAPTest	238	CAAPGroup	02/28/2020	NEW
995	CAAPTest	2346	CAAPGroup	02/28/2020	NEW
995	CAAPTest	3428	CAAPGroup	02/28/2020	NEW
995	CAAPTest	5901	CAAPGroup	02/28/2020	NEW
995	CAAPTest	7361	CAAPGroup	02/28/2020	NEW
995	CAAPTest	10346	CAAPGroup	02/28/2020	NEW
995	CAAPTest	18842	CAAPGroup	02/28/2020	NEW
995	CAAPTest	23466	CAAPGroup	02/28/2020	NEW
995	CAAPTest	24245	CAAPGroup	02/28/2020	NEW
995	CAAPTest	25760	CAAPGroup	02/28/2020	NEW
995	CAAPTest	35979	CAAPGroup	02/28/2020	NEW

Re: insert SAS table into teradata

ChrisNZ — Wed, 04 Mar 2020 02:16:20 GMT

It looks like you may have non printable characters somewhere.

You could clean your code by pasting it into something like notepad and back.

You could use a function like NOTPRINT or NOTGRAPH to scan the data.

I know it may sound silly, but the silly takes place often.

Another option is reducing the size of the upload until a rogue row is identified.

Lastly proc append may be faster for this task.