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.
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.
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: