SAS DAte conversion to numeric

[jorquec]

Hi  all,

I am struggling in some date conversion. 

Is there a way of conversion for this subtraction as Tenure_length_months results in a numeric number in the same format as Teradata  output. Despite this code works on SAS,  the final result for column Tenure_lenght_month does not manky any sense on my model with SAS results.

When I run on Teradata column Tenure_length_months give me numbers like 125,31,48 which is absolutely fine however SAS gives me results like 6278800, 6252333 that could be right on a SAS approach but is not what I would like to see  as an output for this column.

Example: 

Considering today as 2020-04-31 I would have  a customer that is with us in 10 months 

Teradata results 

ten_date

Start_Month_End

tenure_mth

Tenure_length_months

Tenure_Months

02/07/2019

31/07/2019

201,907.00

10.98

10

Considering today as 2020-04-31 I would have  a customer that is with us the same 10 months but SAs calculate in another way so gives me results like 627880.

SAS results

ten_date

Start_Month_End

tenure_mth

Tenure_length_months

Tenure_Months

02JUL2019  31JUL2019     201907      627880.40  627880

How I should write to see the same results as Teradata please?

proc delete
   lib= smm  data=payg_base_1;
run;

proc sql;                                                        
connect to teradata                                                     
(user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer' mode = teradata);                                                         
EXECUTE (
                                                          
CREATE SET TABLE sasmod_manager.payg_base_1 AS                                                       
(                                                         

select  a.*,

bb.Effective_From_Dt as ten_date ,
ADD_MONTHS(ten_date - EXTRACT(DAY FROM ten_date) + 1, 1) - 1 AS Start_Month_End,
cast(trim(extract (year from  ten_date ))  || trim(extract (month from  ten_date  ) (format '99')) as float) as tenure_mth,
((  '2020-04-31' - ten_date)/ 30.42) AS Tenure_length_months,

Cast(( Tenure_length_months ) AS INT)  AS Tenure_Months

from

(  select       subscr_id,  
  subscr_num,
Tariff_Offering_Id,    
 Device_Type_Id   ,
sales_channel_id

/*from NUC_PL_USER_VIEW.SUBSCR_TRACK WHERE DATE    between effective_from_dt  and  effective_to_dt */
from NUC_PL_USER_VIEW.SUBSCR_TRACK WHERE 1200431  between effective_from_dt  and  effective_to_dt 
AND PREPYD_IND = 'Y'  
AND pandl_segment_id = 1 
AND mobile_broadband_ind = 'N' 
AND TELEMATIC_IND='N' 
AND (  Internal_BASE_IND=1 ) 
 )  a  

         left join
( select     subscr_id,    Effective_From_Dt    from     NUC_PL_USER_VIEW.SUBSCR_TRACK 
   WHERE    
   PREPYD_IND = 'Y'    AND pandl_segment_id = 1   AND mobile_broadband_ind = 'N'   AND TELEMATIC_IND='N'  AND Internal_BASE_IND=1    /* START DATE*/
  qualify rank () over (  partition by  subscr_id    order by  Effective_From_Dt asc )  =1     )  bb
   on a.Subscr_id=bb.Subscr_id 
 

) WITH DATA PRIMARY INDEX(subscr_id);)BY TERADATA;                      
quit; 

[ballardw]

You several problems in your example. If 2020-04-31 is supposed to be 31 April 2020 then that date is not a valid date to begin with.

If you want SAS to modify date values then you should look at the INTNX function.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

Since you are apparently passing this to Teradata then there is strong likelihood that somewhere you have a data value that is not handled correctly as well.  I have no idea what a date like

 1200431

is supposed to represent, so I am assuming that is some sort of Teradata convention.

A basic date change example in SAS:

data example;
   thisdate = '30APR2020'd ;
   otherdate = intnx('month',thisdate,10,'s');
   format thisdate otherdate date9.;
run;

The last parameter of the INTNX function is an alignment, you can have a date returned that is the begining of the period, use 'B', the end of the period, use 'E' or the same point in the target period, use 'S'. Since the example is using the last data of a month the 'same' is the last day of the target month.

[Tom · Accepted solution]

SAME will only yield the same result as END with the starting date is the 31st day of the month.

1541  data example;
1542     thisdate = '30APR2020'd ;
1543     samedate = intnx('month',thisdate,1,'s');
1544     lastdate = intnx('month',thisdate,1,'e');
1545     format _all_ date9.;
1546     put (_all_) (=/);
1547  run;


thisdate=30APR2020
samedate=30MAY2020
lastdate=31MAY2020