Evaluate the field based on formats.

[librasonali]

hi ,

Formatted CL_ID as z20 instead of z21 for possible overlap of the EC data from the CR data.
how can  CL_ID I may  evaluate ? 

the below is just a sample code :

proc sql;
     CONNECT TO TERADATA (user="&TDUser" password="&TDPass" server="&TDServer");
     create table tableA  as  
     select *,
          clmnt_bth_dt as BIRTH_DT length=4 format=mmddyy10. 
          from connection to teradata
          (SELECT
         		 a.cl_id (FORMAT 'z(21)') (char(21)) AS CL_ID, 
         		b.sbscr_ssn
				from EC.claim a
                   LEFT JOIN
               EC.subscriber b
          on (a.sbscr_id=b.sbscr_id))
;
quit;


proc sql;
   CONNECT TO TERADATA (user="&TDUser" password="&TDPass" server="&TDServer");
   create table tableB 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 cl_id, 
             
              b.sbscr_nbr as SBSCR_ID 
       from CR.MEMBER a
        LEFT JOIN 
                   from CR.MEMBERSHIP_FACT b
         on (a.mbr_ky = b.mbr_ky)
       );
   quit;

/**************************************************************
* Now combine 
**************************************************************/
proc append data=tableB base=tableA; run;

sample data format :

EC data
CL_ID
9000055081302001
10698794602001
9000746632802002
10515343302001
mbr id as cl_id -- from CR
mbr_id
549041585390
116199788665
116199788732
116199794904
116199794902

[Kurt_Bremser]

These ID's are most likely stored as character in Teradata, so you should fetch them as-is without reformatting.

Once you have sample data in SAS, convert them to a data step with datalines and post that here, so we can see what is exactly in there.

I also do not think that your SQL queries will work at all, as the explicit pass-through queries do not deliver a variable clmnt_bth_dt.

[Tom]

Can you explain what you are trying to do?  That teradata SQL looks very strange.

How is the variable defined in TERADATA?  If it is a number of some type (unlike SAS in TERADATA there are multiple types of numbers) and it is longer than 15 digits then CAST it as a string to avoid loss of precision.  SAS stores all numbers as 64 bit binary floating point.  So the maximum contiguous integer value that can be stored has only 16 digits. 

662   data _null_;
663     max=constant('exactint');
664     put max= comma30.;
665   run;

max=9,007,199,254,740,992