Blank value coming in substring field

[librasonali]

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;

[Kurt_Bremser]

Your issue is not with the SUBSTR, it's with the INPUT function, so it happens here:

INPUT(REL_CD,3.) as rel_cd1

Your explicit pass-through does not deliver a column named REL_CD.

---

@librasonali wrote:

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.MBR_MOD_DT as mbr_fact_mod_dt,
              a.SUBSCRIBER_ID (FORMAT 'z(21)') (char(21)) as  SBSCR_ID,
              a.CUST_SEG_NUM  as cust_seg_nbr,
              a.OPT_SEG_ID  as optum_seg_id,
              b.MBR_ID (FORMAT 'z(21)') (char(21)) 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;

---

[librasonali]

I didn't mentioned the code complete. but i m passing REL_CD, i will update the query .

my issue is my subsr_ssn are coming blank but in source subsrciber_id is populated.

[Kurt_Bremser]

This

NOTE: Invalid argument to function INPUT. Missing values may be generated.

complains about the INPUT function, so this needs to be fixed as well.

Run your code without the SUBSTR function to see which values you actually get from the DB.

[librasonali]

I am getting below values in SBSCR_ID from table member_int_crs
Character format length 20
SBSCR_ID
2696942
488882983

and from teradata , BIGINT format length 19
SUBSCRIBER_ID
633735
201548
618887
// same type of values if in source its 633735 then in member_int_crs also its same.
and also how shd i fix this then?
NOTE: Invalid argument to function INPUT. Missing values may be generated.
for REL_CD i am getting correct value.. just the format is getting changed from char to Num 

[Kurt_Bremser]

So it looks like your cast in Teradata is incorrect, as the values are changed.

[librasonali]

no no .

am getting below values in SBSCR_ID from table member_int_crs
Character format length 20
SBSCR_ID
2696942
488882983

and from teradata , BIGINT format length 19
SUBSCRIBER_ID
2696942
488882983

// same type of values if in source its 633735 then in member_int_crs also its same..

issue is with sbscr_ssn , how its coming blank ? where as subs_id is coming fine .

[Kurt_Bremser · Accepted solution]

Use

substr(left(SBSCR_ID),3,9)

and see what happens.

Casts of numeric to character are usually right-aligned.

[librasonali]

now it's working !!
if SUBSCRIBER_ID is 633735 then subscr_ssn is 3735 , which was excepted.