Hi Team, I am not able to get the output as i am using of the table customer_css as I am using order by statement for cust_detail_ky which is the derived field how else I can do so that i am able to append the data ? Help will be appreciated  proc sql; connect to teradata (user="" password="" server=""); create table customer_int as select * from connection to teradata (select distinct cust_detail_ky, cust_x, data_source_cd, subgrp_1_cd, subgrp_2_cd, from table 1 where DATA_SOURCE_CD not in (1,2,6,8,14,16,18,21,40,41,43,44,45) order by cust_detail_ky); quit; proc sql; connect to teradata (user="" password="" server=""); create table customer_css as select * from connection to teradata (select distinct 'C' as int_ext_src, CUST as cust_x, ORIG_SRC_SYS_CD as data_source_cd, subgrp_1_cd, subgrp_2_cd, from table b) order by cust_detail_ky; quit; /************************************************************** * Now combine the data **************************************************************/ proc append data=customer_int base=customer_css; run;  ... View more

Hi,  I am not able to understand that why I am getting below error whereas my formats are fine from source ,  ERROR: Character expression requires a character format. Can anybody please suggest.  contents of interim.member_CRS2:     Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 4 CARRIER_NAME Char 50 $50.00 $50.00 CARRIER_NAME 3 CO_CD Char 3 $3.00 $3.00 CO_CD 14 DEPN_NBR Char 3 $3.00 $3.00 DEPN_NBR 1 EMP_STATUS Char 5 $5.00 $5.00 EMP_STATUS 17 FIRST_NAME Char 50 $50.00 $50.00 FIRST_NAME 12 INDV_ID Num 8 11 11 INDV_ID 18 LAST_NAME Char 100 $100.00 $100.00 LAST_NAME 30 PERSON_ID Num 8     PERSON_ID 7 PRDCT_CD Char 30 $30.00 $30.00 PRDCT_CD 8 SBSCR_ID Char 11 $11.00 $11.00 SBSCR_ID 11 clmnt_id Char 20 $20.00 $20.00 clmnt_id 29 cov_end_mth Num 4       28 cov_start_mth Num 4       27 cust_defined_key Num 8       20 cust_detail_ky_char Char 20       9 cust_seg_nbr Char 20 $20.00 $20.00 cust_seg_nbr 5 fund_arng_cd Char 1 $1.00 $1.00 fund_arng_cd 16 gender Char 1 $1.00 $1.00 gender 6 hmo_acct_div_nbr Num 8 6 6 hmo_acct_div_nbr 26 indus_prdct_cd Num 8       25 key2 Char 90       15 mbr_zip_cd Char 15 $15.00 $15.00 mbr_zip_cd 21 med_cov_ind Char 1       2 medicare_ind Char 1 $1.00 $1.00 medicare_ind 10 optum_seg_id Char 8 $8.00 $8.00 optum_seg_id 13 pat_dob Num 8 DATE9. DATE9. pat_dob 24 rel_cd Num 8       22 rx_cov_ind Char 1       23 sbscr_ssn Char 9       19 source Char 1             contents of from the source table :   Alphabetic List of Variables and Attributes # Variable Type Len Format Informat Label 5 CARRIER_NAME Char 50 $50.00 $50.00 CARRIER_NAME 4 CO_CD Char 3 $3.00 $3.00 CO_CD 15 DEPN_NBR Char 3 $3.00 $3.00 DEPN_NBR 2 EMP_STATUS Char 5 $5.00 $5.00 EMP_STATUS 18 FIRST_NAME Char 50 $50.00 $50.00 FIRST_NAME 13 INDV_ID Num 8 11 11 INDV_ID 19 LAST_NAME Char 100 $100.00 $100.00 LAST_NAME 1 MBR_MO Num 8 11 11 MBR_MO 8 PRDCT_CD Char 30 $30.00 $30.00 PRDCT_CD 9 SBSCR_ID Char 11 $11.00 $11.00 SBSCR_ID 12 clmnt_id Char 20 $20.00 $20.00 clmnt_id 21 cust_detail_ky_char Char 20       10 cust_seg_nbr Char 20 $20.00 $20.00 cust_seg_nbr 6 fund_arng_cd Char 1 $1.00 $1.00 fund_arng_cd 17 gender Char 1 $1.00 $1.00 gender 7 hmo_acct_div_nbr Num 8 6 6 hmo_acct_div_nbr 16 mbr_zip_cd Char 15 $15.00 $15.00 mbr_zip_cd 22 med_cov_ind Char 1       3 medicare_ind Char 1 $1.00 $1.00 medicare_ind 11 optum_seg_id Char 8 $8.00 $8.00 optum_seg_id 14 pat_dob Num 8 DATE9. DATE9. pat_dob 25 rel_cd Num 8       23 rx_cov_ind Char 1       24 sbscr_ssn Char 9       20 source Char 1       ... View more

Hi  ,   I am getting blank values in my sbcr_ssn field when i m caluclating it from subsr_id. Can you please help me with quick fix on the substr side?? thanks !! Also getting note in log :   NOTE: Invalid string. NOTE: Invalid argument to function INPUT. Missing values may be generated. NOTE: Table WORK.MEMBER_INT_CRS created, with 610173 rows and 25 columns.   FYI :Suscr_id in teradata was BIGINT i converted it into Char :   proc sql; connect to teradata (user="jhk" password="bhgh" server="UDWtest"); create table member_int_crs(drop=med rx mbr_fact_mod_dt REL_CD rename=( rel_cd1=rel_cd )) as select *, 'C' as source length=1, /* Indentify all records as CIRRUS Membership */ 'CIR'||trim(left(cust_seg_nbr)) as cust_detail_ky_char length=20, case when med = 1 then 'Y' else 'N' end as med_cov_ind length=1, case when rx = 1 then 'Y' else 'N' end as rx_cov_ind length=1, substr(SBSCR_ID,3,9) as sbscr_ssn length=9 , INPUT(REL_CD,3.) as rel_cd1 from connection to teradata (select a.mbr_mo, b.EMPMT_STS_CD as EMP_STATUS, b.REL_CD , a.MDCR_PRI_IND as medicare_ind, a.co_cd, CASE when a.MED_FLG_IND='Y' then 1 else 0 END as med , CASE when a.RX_FLG_IND='Y' then 1 else 0 END as rx , a.CARRIER_NAME, a.FINC_ARNG_CD as fund_arng_cd , 9999 as hmo_acct_div_nbr, a.PRDCT_CD, b.MBR_MOD_DT as mbr_fact_mod_dt, a.SUBSCRIBER_ID (FORMAT 'z(20)') (char(20)) as SBSCR_ID, a.CUST_SEG_NUM as cust_seg_nbr, a.OPT_SEG_ID as optum_seg_id, b.MBR_ID (FORMAT 'z(20)') (char(20)) as clmnt_id, b.Indv_id, b.dob as pat_dob, b.DEPN_NBR, b.ZIP_CD as mbr_zip_cd, b.GENDER_CD as gender, b.first_name, b.last_name from CRMINT.MEMBERSHIP_FACT a left join CRMINT.MEMBER_LU b on a.MBR_KEY = b.MBR_KEY where mbr_mo between &mendstrt. and &mendend. and a.SRC_ROW_STS_CD IN( 'A') and b.SRC_ROW_STS_CD IN( 'A') ) order by indv_id, mbr_mo, cust_seg_nbr; quit;     ... View more

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.      ... View more