Data type causing issue in teradata to SAS

librasonali — Wed, 23 Feb 2022 07:21:18 GMT

Hi,

I am trying to do below query but

  Proc sql;
   CONNECT TO TERADATA (user="cdff" password="xdyt" server="test");
   create table interim.int_claimant (drop=clmnt_bth_dt) as  
   select *,
          clmnt_bth_dt as BIRTH_DT length=4 format=mmddyy10. 
   from connection to teradata
(SELECT a.mbr_id (FORMAT 'z(20)') (char(20)) as clmnt_id, 
       a.indv_id,
       a.depn_nbr   as depn_nbr ,
       a.FIRST_NAME as first_name,
       a.LAST_NAME  as last_name,
       a.DOB        as clmnt_bth_dt,
       a.GENDER_CD as gender,
       a.pers_ssn   as sbscr_ssn,
       b.SUBSCRIBER_ID as SBSCR_ID
from crmint.member_lu a
 LEFT JOIN (select MBR_KEY,
                   MBR_MO,
                   SUBSCRIBER_ID,
                   RANK() OVER (PARTITION BY MBR_KEY ORDER BY MBR_MO DESC ) AS RANKING
            from CRMINT.MEMBERSHIP_FACT
            where SRC_ROW_STS_CD  IN( 'A')
            group by MBR_KEY,
                     MBR_MO,
                     SUBSCRIBER_ID
            QUALIFY RANK() OVER (PARTITION BY MBR_KEY ORDER BY MBR_MO DESC ) = 1) b
  on (a.mbr_key = b.mbr_key)
where a.SRC_ROW_STS_CD  IN( 'A') 
       );
   quit;

ERROR: At least one of the columns in this DBMS table has a datatype that is not supported by this engine.

here the MBR_KEY has BIG int data type which is not supported in SAS 9.4 , so initially we are changing it to char but now we want multiple rows per member. So to handle this 1 to many we pull taking the most recent SUBSCRIBER_ID for a member to get a 1 to 1 so used rank.

how can i resolve this error?
below fields have length 19

Column Name	#	Data Type
MBR_KEY	2	BIGINT
MBR_MO	20	INTEGER
SUBSCRIBER_ID	5	BIGINT

MBR_ID

BIGINT

rest of them are char or INT.

Re: Data type causing issue in teradata to SAS

Kurt_Bremser — Wed, 23 Feb 2022 09:04:21 GMT

I would cast all "numbers" (which are in fact codes and never used for calculations) to character in the Teradata pass-through, so you can handle them as character on the SAS side.

topic Data type causing issue in teradata to SAS in SAS Programming

Data type causing issue in teradata to SAS

Re: Data type causing issue in teradata to SAS