topic Re: Evaluate the field based on formats. in SAS Programming

Evaluate the field based on formats.

librasonali — Wed, 16 Feb 2022 06:56:43 GMT

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

Re: Evaluate the field based on formats.

Kurt_Bremser — Wed, 16 Feb 2022 08:54:43 GMT

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.

Re: Evaluate the field based on formats.

Tom — Wed, 16 Feb 2022 14:07:20 GMT

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