Data type causing issue in teradata to SAS

[librasonali]

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

2

BIGINT

rest of them are char or INT. 

[Kurt_Bremser · Accepted solution]

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.