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;