topic Re: proc apend in New SAS User

proc apend

jorquec — Mon, 17 Feb 2020 20:44:00 GMT

Hi all,

I have this code , at the end I don't know why is not allowing me to save my work table called work.lastmonth in a teradata sandbox called ICONSUMR.lm_lastmonth; using proc apend.

It is just the end of code where I put a comment /* claudia test - feb 2020*/

I have attached the code an the log file for this part with the error msg.

Could some please tell me how to fix this ?

Many thanks in advance.

																	
/*libname ipaygo teradata user= &td_usr. password = &td_pass. server = 'edwprod' database = 'insights_paygo'; 							
*/
							
								
								
								
								
/* Change Here */ 								
%let dateref =&sql_date2.; /* End date of month running */								
%let start =&sql_date6.; /* Three months prior */ /*changed: 180430: was set to&sql_date5. by mistake*/								
								
/* ########################################################## */								
								
/* Step 1 */								
      								
proc sql ;								
*drop table sm.sj_90day_active_base;								
*drop table sm.lm_base_4;								
								
drop table ipaygo.sj_90day_active_base;	
drop table irm.sj_90day_active_base;/*180516: moving to irm*/		
drop table iconsumr.lm_base_4;								
run;								
								
proc sql;								
connect to teradata								
(user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer' mode = teradata);								
EXECUTE (								
CREATE MULTISET VOLATILE TABLE base_90day AS								
(								
SELECT mpn,								
       MAX(conn_dte) AS conn_dte,								
       SUM(consec_days) AS total_days,								
       MAX(consec_days) AS max_consec,     								
       COUNT(mpn) AS consec_sessions,                      								
       MIN(first_call) AS first_call2,								
	   MAX(last_call) AS last_call2,							
	   first_call2 -  ((DATE&dateref.) - 89)  AS first_gap, 							
       CASE WHEN MAX(max_days) >= first_gap THEN MAX(max_days) ELSE first_gap END AS max_gap,								
	   consec_sessions -1 AS non_sess1,							
       CASE WHEN first_gap > 0 THEN non_sess1 +1 ELSE non_sess1 END AS non_sess2,								
       CASE WHEN last_call2 < DATE&dateref. THEN non_sess2 +1 ELSE non_sess2 END AS zu_sessions,								
       DATE&dateref. - last_call2 AS zero_usage, 								
	   90 - total_days AS total_zu_days,							
       CASE WHEN zu_sessions = 0 THEN 0 ELSE CAST(total_zu_days AS FLOAT) / CAST(zu_sessions AS FLOAT) END								
        AS avg_zu_per_session,								
       								
        CASE WHEN zero_usage = 0 THEN 'a: 0'								
	     WHEN ZERO_USAGE <= 6 THEN 'b: 1-6'							
	     WHEN ZERO_USAGE <= 13 THEN 'c: 7-13'							
	     WHEN ZERO_USAGE <= 20 THEN 'd: 14-20'							
	     WHEN ZERO_USAGE <= 27 THEN 'e: 21-27'							
	     WHEN ZERO_USAGE <= 34 THEN 'f: 28-34'							
	     WHEN ZERO_USAGE <= 41 THEN 'g: 36-41'							
	     WHEN ZERO_USAGE <= 48 THEN 'h:42-48'							
	     WHEN ZERO_USAGE <= 55 THEN 'i:49-55'							
	     WHEN ZERO_USAGE <= 62 THEN 'j:56-62'							
	     WHEN ZERO_USAGE <= 69 THEN 'k:63-69'							
	     WHEN ZERO_USAGE <= 76 THEN 'l:70-76'							
	     WHEN ZERO_USAGE <= 83 THEN 'm:77-83'							
	     WHEN ZERO_USAGE <= 90 THEN 'n:84-90'							
	    ELSE 'o: 91 + ' END AS days_since_last_use_band							
      FROM								
      (                               								
        SELECT mpn,								
               call_group,								
               MAX(conn_dte) AS conn_dte,								
               COUNT(mpn) AS consec_days,								
               MAX(days_between2) AS max_days,								
               MIN(calldate) AS first_call,								
               MAX(calldate) AS last_call								
        FROM								
		       (						
			   SELECT mpn,					
                      calldate,								
                      conn_dte,								
                      days_between,								
                      days_between2,								
                      ZEROIFNULL(SUM(days_between2) OVER (PARTITION BY mpn ORDER BY calldate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS call_group								
                                                                                                								
                      FROM								
                          (               								
                            SELECT mpn,								
                                   conn_dte,								
                                   calldate,								
                                   (calldate - DATE '1900-01-01') - SUM(calldate - DATE '1900-01-01') OVER (PARTITION BY mpn								
                                       ORDER BY calldate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) - 1 AS days_between,								
                                   CASE WHEN days_between IS NULL THEN 0 ELSE days_between END AS days_between2								
                                FROM								
                                       (SELECT chrgd_subscr_cd AS mpn ,calldate, MAX(connected_dt) AS conn_dte, MAX(1) AS usage_flag								
                          FROM 								
                                    ( SELECT								
		                               a.subscr_id, 						
                                       c.connected_dt,								
                                       chrgd_subscr_cd,								
                                       a.call_type_id, 								
                                       bill_start_dt AS calldate 								
               		                                   FROM nuc_user_view.PREPYD_CDR AS a 						
                     JOIN insights_view_sandbox.ACCT_SUBSCR_CONNECTION_DT AS c 								
					 ON a.subscr_id = c.subscr_id  			
                       WHERE bill_start_dt >= ((DATE&dateref.) - 89) AND bill_start_dt <= DATE&dateref. AND a.call_type_id IN(36,14,4,3,9)  								
 ) AS a 								
      GROUP BY 1,2								
	  ) AS d							
            ) AS e								
                ) AS f								
                    GROUP BY mpn, call_group								
                                             ) AS g      								
                                               GROUP BY mpn								
                                               having ZERO_USAGE <= 90 								
) WITH DATA PRIMARY INDEX(mpn) ON COMMIT PRESERVE ROWS) BY TERADATA;        								
								
								
EXECUTE(								
CREATE MULTISET VOLATILE TABLE base1 AS (
sel a.mpn,
    st.subscr_id,
    effective_from_dt,
    prepyd_ind,
    finance_active,
    O2Money_Ind,
    Tariff_Offering_Id,
    Needs_Based_Segment_Id,
    Device_Type_Id,
    case when st.insurance_product_ind = 'Y' then 1 else 0 end Insurance_Ind,
    dslu,
    CUST_ID
    from (sel MPN, zero_usage as dslu from base_90day) as a
				inner join  (sel Subscr_Num, prepyd_ind, subscr_id, effective_from_dt , Acquisition_Channel_Id, Prod_Id,Internal_Base_Ind as finance_active, O2Money_Ind,
        Tariff_Offering_Id,  Needs_Based_Segment_Id, Device_Type_Id, insurance_product_ind,cust_id,sales_channel_id
								from NUC_PL_USER_VIEW.SUBSCR_TRACK 
								WHERE date&dateref. between effective_from_DT and effective_TO_DT and
								prepyd_ind='Y' and subscr_type_id=197 and Mobile_Broadband_Ind='N' ) as st
        on a.mpn=st.Subscr_Num
        
        where st.prod_id not in ( '4619', '4696', '4926', '5041', '5060', '5070', '5086', '5166', '5275', '5275', '249543','249546', '250531', '251465', '251649', '252211',
'253681', '254408', '255441', '256156', '262562', '264809', '266583', '267281', '268647', '270313', '270451', '270490', '271578', '276332', '288338', '290921' )
 QUALIFY(ROW_NUMBER() OVER (PARTITION BY mpn  ORDER BY st.subscr_id desc, effective_from_dt DESC )) =1
 ) WITH DATA ON COMMIT PRESERVE ROWS )BY TERADATA;								
								
 /* modified table base_1 on 7th march 2018 (add variable sms_allowance ) */
EXECUTE(
CREATE  MULTISET VOLATILE TABLE base_1 AS (
   select a.*, 
		    coalesce(e.consumer_tariff_type_cd,e.bolton_type_cd) as allowance_group_name,
		  offering_name as allowance_name,
			CASE WHEN allowance_name = 'BIGBUNDLE5' THEN 200
				 WHEN allowance_name = 'BIGBUNDLE10' THEN 1000
				 WHEN allowance_name = 'BIGBUNDLE15' THEN 2000
				 WHEN allowance_name = 'BIGBUNDLE20' THEN 4000
				 WHEN allowance_name = 'BIGBUNDLE25' THEN 4000
				 WHEN allowance_name = 'BIGBUNDLE30' THEN 4000
				 WHEN allowance_name = 'BIGDATABUNDLE10' THEN 100
			   /* WHEN allowance_name = '10 Big Bundle Online' THEN 100 */
				 WHEN allowance_name = 'INTERNATIONALSIM' THEN 3000
				 WHEN allowance_name = 'O2UNLIMITEDONNET' THEN 3000
				 WHEN allowance_name = 'TEXTWEBFEB2010RT' THEN 500
				 WHEN allowance_name = 'PAYANDGOGOGO10UNLIMITED' THEN 5000
				 WHEN allowance_name = 'SIMPLYPAYASYOUGO' THEN 3000
	 ELSE 0 END AS sms_allowance
   from base1 a
		/* left join NUC_PL_USER_VIEW.ALLOWANCE as c
			on a.allowance_id=c.allowance_id
		left join NUC_PL_USER_VIEW.ALLOWANCE_GROUP as d
			on d.allowance_group_id=c.allowance_group_id --removed */ 
		left join (select * from  nuc_pl_user_view.offering_catalog where offering_billing_sys_name='prp') e
			on a.Tariff_Offering_Id = e.offering_id
) WITH DATA ON COMMIT PRESERVE ROWS)BY TERADATA;								
								
								
EXECUTE(								
CREATE  MULTISET VOLATILE TABLE  base_2 AS (								
   select a.*,Needs_Based_Segment_Type_Name as needs_segment_desc								
   from base_1 a								
   left join NUC_PL_USER_VIEW.NEEDS_BASED_SEGMENT_TYPE as e								
   on a.Needs_Based_Segment_Id=e.Needs_Based_Segment_type_Id								
) WITH DATA ON COMMIT PRESERVE ROWS)BY TERADATA;								
								
/* Modified table base_3 on 7th marh 2018 (add variable DEVICE_TYPE,IS_4G) */
EXECUTE(
CREATE MULTISET VOLATILE TABLE base_3 AS (
	  SELECT A.* ,G.MANUFACTURER AS VENDOR_DESC , SMART_PHONE_IND, MODEL_NUMBER,N.DEVICE_TYPE,N.IS_4G
	  FROM BASE_2 A
	  LEFT JOIN  NUC_PL_USER_VIEW.DEVICE_TYPE AS G
	  ON A.DEVICE_TYPE_ID=G.DEVICE_TYPE_ID
	  LEFT JOIN NUC_PL_USER_VIEW.DEVICE_CATALOG N 
	  ON G.DEVICE_TYPE_ID=N.DEVICE_TYPE_ID
	  
)WITH DATA ON COMMIT PRESERVE ROWS)BY TERADATA;								
								
/* Modified on table base_4 on 7th marh 2018 (add variable tenure_months ) */
EXECUTE(
CREATE MULTISET VOLATILE TABLE base_4 AS (
	select a.*,  
	CASE WHEN tenure_months =0 THEN 'zero'
						WHEN tenure_months >0 and tenure_months <=3  THEN 'up to 3 mth'
						WHEN tenure_months >3 and tenure_months <=6  THEN 'up to 6 mth'
						WHEN tenure_months >6 and tenure_months <=12  THEN 'up to 12 mth'
						WHEN tenure_months >12 and tenure_months <=18  THEN 'up to 18 mth'
						WHEN tenure_months >18 and tenure_months <=24  THEN 'up to 24 mth'
						WHEN tenure_months >24 and tenure_months <=36  THEN 'up to 36 mth'
						WHEN tenure_months >36 and tenure_months <=48  THEN 'up to 48 mth'
						WHEN tenure_months >48 and tenure_months <=60 THEN 'up to 60 mth'
						WHEN tenure_months >60 THEN 'over 60mths' ELSE 'missing' END AS tenure_desc,
	F.tenure_months
	from base_3 a
	left join  NUC_PL_USER_VIEW.SUBSCR_LIFE_EVENT as f
	on a.subscr_id=f.subscr_id
)WITH DATA ON COMMIT PRESERVE ROWS)BY TERADATA;								
								
/* --Modified table insights_consumer.lm_base_4 on 7th marh 2018 (add variable pcode_flag ) */
EXECUTE(
CREATE SET TABLE INSIGHTS_CONSUMER.LM_BASE_4 AS 
/*CREATE SET TABLE SASMOD_MANAGER.LM_BASE_4 AS */
	(
		SELECT B.*,
		CASE WHEN C.POST_CODE IS NOT NULL THEN 1 ELSE 0 END AS PCODE_FLAG 
				
		FROM BASE_4 B 
		LEFT JOIN NUC_PL_USER_VIEW.CUST_TRACK C 
		ON C.CUST_ID=B.CUST_ID
		and C.cust_id <>-1 
        AND &dateref.  BETWEEN C.eff_from_DT AND C.eff_TO_DT 
      
	) WITH DATA PRIMARY INDEX(SUBSCR_ID);)BY TERADATA;
QUIT;								
								
data base_4;	
set iconsumr.lm_base_4;	
/*set sm.lm_base_4;	*/							
run;								
								
proc sort data= base_4 ;								
by subscr_id  descending effective_from_dt;								
run;								
								
data base_4a;								
set base_4;								
by subscr_id;								
if first.subscr_id;								
run;								
								
proc sql ;								
*drop table sm.sj_90day_active_base;								
*drop table sm.lm_base_4;								
drop table ipaygo.sj_90day_active_base;	
drop table irm.sj_90day_active_base;	/*180516: moving to irm so need the two drop statements*/
drop table iconsumr.lm_base_4;								
run;								
	
data irm.sj_90day_active_base	/*180516: moving to irm */
/*data sm.sj_90day_active_base 	 */							
(dbcreate_table_opts="primary index (subscr_id)" bulkload=yes fastload=yes  sleep=5 tenacity=1);								
set base_4a;								
run;								
								
proc sql;								
connect to teradata (user=&td_usr. password=&td_pass.  server = 'edwprod' database = 'insights_consumer');								
create table last3months as select * from connection to teradata								
(select subscr_id,								
/* top level*/								
sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_actvty_duration END )/60 as VOICE_mins,								
sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_count END ) as VOICE_CALLs,								
								
/* need to be carful if using in sql - due to integers*/								
sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_actvty_duration END )/60 as VIDEO_mins,								
sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_count END ) as VIDEO_CALLs,								
sum(CASE WHEN prepay_Srvce_Type_Category='SMS' THEN  usg_count END ) as SMS,								
sum(CASE WHEN prepay_Srvce_Type_Category='MMS' THEN  usg_count END ) as MMS,								
								
/* note data wil include mbb - this will be excluded when join to base*/								
sum(CASE WHEN prepay_Srvce_Type_Category='DATA' THEN usg_data_size END) AS DATA_MB,								
								
/* split voice by different categories*/								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_actvty_duration END )/60 as VOICE_intl_mins,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_actvty_duration END )/60 as VOICE_other_mins,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_actvty_duration END )/60 as VOICE_ROAMING_MO_mins,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_actvty_duration END )/60 as VOICE_ROAMING_MT_mins,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_actvty_duration END )/60 as VOICE_UK_LANDLINE_mins,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_actvty_duration END )/60 as VOICE_UK_OFFNET_MOBILE_mins,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_actvty_duration END )/60 as VOICE_UK_ONNET_MOBILE_mins,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_actvty_duration END )/60 as VOICE_UK_VOICEMAIL_mins,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_count END ) as VOICE_intl_calls,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_count  END ) as VOICE_other_calls,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_count  END ) as VOICE_ROAMING_MO_calls,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_count  END ) as VOICE_ROAMING_MT_calls,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_count  END ) as VOICE_UK_LANDLINE_calls,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_count  END ) as VOICE_UK_OFFNET_MOBILE_calls,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_count  END ) as VOICE_UK_ONNET_MOBILE_calls,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_count END ) as VOICE_UK_VOICEMAIL_calls,								
        								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - INTL'  then  usg_count END ) as SMS_INTL,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MO'  then  usg_count END ) as SMS_PREMIUM_MO,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MT'  then  usg_count END ) as SMS_PREMIUM_MT,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - Roaming'  then  usg_count END ) as SMS_ROAMING,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK'  then  usg_count END ) as SMS_UK,								
        								
/* facebook and twitter*/								
sum(CASE WHEN Dialled_Digits_Id IN (36,37) then usg_count END ) as FACEBOOK_SHORTCODE,								
sum(CASE WHEN Dialled_Digits_Id =1 then usg_count END ) as TWITTER_SHORTCODE,								
                								
/* these are 3 monthe averages below*/								
sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_actvty_duration END )/60/3 as VOICE_mins_AV,								
sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_count END )/3 as VOICE_CALLs_AV,								
        								
sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_actvty_duration END )/60/3 as VIDEO_mins_AV,								
sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_count END )/3 as VIDEO_CALLs_AV,								
        								
sum(CASE WHEN prepay_Srvce_Type_Category='SMS' THEN  usg_count END )/3 as SMS_AV,								
        								
sum(CASE WHEN prepay_Srvce_Type_Category='MMS' THEN  usg_count END )/3 as MMS_AV,								
       								
sum(CASE WHEN prepay_Srvce_Type_Category='DATA' THEN usg_data_size END)/3 AS DATA_MB_AV,								
                								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_actvty_duration END )/60/3 as  V_intl_mins_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_actvty_duration END )/60/3 as V_other_mins_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_actvty_duration END )/60/3 as V_ROAMING_MO_mins_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_actvty_duration END )/60/3 as V_ROAMING_MT_mins_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_actvty_duration END )/60/3 as V_UK_LANDLINE_mins_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_actvty_duration END )/60/3 as V_UK_OFFNET_MOBILE_mins_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_actvty_duration END )/60/3 as V_UK_ONNET_MOBILE_mins_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_actvty_duration END )/60/3 as V_UK_VOICEMAIL_mins_AV,								
        								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_count END )/3 as V_intl_calls_Av,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_count  END )/3 as V_other_calls_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_count  END )/3 as V_ROAMING_MO_calls_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_count  END )/3 as V_ROAMING_MT_calls_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_count  END )/3 as V_UK_LANDLINE_calls_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_count  END )/3 as V_UK_OFFNET_MOBILE_calls_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_count  END )/3 as V_UK_ONNET_MOBILE_calls_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_count END )/3 as V_UK_VOICEMAIL_calls_AV,								
        								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - INTL'  then  usg_count END )/3 as SMS_INTL_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MO'  then  usg_count END )/3 as SMS_PREMIUM_MO_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MT'  then  usg_count END )/3 as SMS_PREMIUM_MT_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - Roaming'  then  usg_count END )/3 as SMS_ROAMING_AV,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK'  then  usg_count END )/3 as SMS_UK_AV,								
        								
sum(CASE WHEN Dialled_Digits_Id IN (36,37) then usg_count END )/3 as FACEBOOK_SC_AV,								
sum(CASE WHEN Dialled_Digits_Id =1 then usg_count END )/3 as TWITTER_SC_AV								
        								
/*sum(CASE WHEN prepay_Srvce_Type_Category='WAP' THEN usg_data_size END) AS WAP,								
sum(CASE WHEN prepay_Srvce_Type_Category='WIFI' THEN usg_data_size END) AS WIFI*/								
        from nuc_pl_user_view.SUBSCR_USAGE_SUMM_MONTHLY a								
			left join nuc_pl_user_view.PREPAY_SRVCE_TYPE b					
			on a.srvce_type_id=b.Prepay_Srvce_Type_Id					
			where  event_month_end_dt>=&start. and  event_month_end_dt<=&dateref.					
				group by  subscr_id);				
quit;								
								
								
proc sql;								
connect to teradata (user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_smecorp');								
create table lastmonth as select * from connection to teradata								
(select subscr_id,								
/* top level*/								
sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_actvty_duration END )/60 as VOICE_mins_m,								
sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_count END ) as VOICE_CALLs_m,								
        								
/* need to be carful if using in sql - due to integers*/								
sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_actvty_duration END )/60 as VIDEO_mins_m,								
sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_count END ) as VIDEO_CALLs_m,								
       								
sum(CASE WHEN prepay_Srvce_Type_Category='SMS' THEN  usg_count END ) as SMS_m,								
        								
sum(CASE WHEN prepay_Srvce_Type_Category='MMS' THEN  usg_count END ) as MMS_m,								
/* note data wil include mbb - this will be excluded when join to base*/								
sum(CASE WHEN prepay_Srvce_Type_Category='DATA' THEN usg_data_size END) AS DATA_MB_m,								
        								
/* split voice by different categories*/								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_actvty_duration END )/60 as VOICE_intl_mins_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_actvty_duration END )/60 as VOICE_other_mins_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_actvty_duration END )/60 as VOICE_ROAMING_MO_mins_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_actvty_duration END )/60 as VOICE_ROAMING_MT_mins_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_actvty_duration END )/60 as VOICE_UK_LANDLINE_mins_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_actvty_duration END )/60 as VOICE_UK_OFFNET_MOBILE_mins_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_actvty_duration END )/60 as VOICE_UK_ONNET_MOBILE_mins_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_actvty_duration END )/60 as VOICE_UK_VOICEMAIL_mins_m,								
								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_count END ) as VOICE_intl_calls_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_count  END ) as VOICE_other_calls_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_count  END ) as VOICE_ROAMING_MO_calls_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_count  END ) as VOICE_ROAMING_MT_calls_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_count  END ) as VOICE_UK_LANDLINE_calls_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_count  END ) as VOICE_UK_OFFNET_MOBILE_calls_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_count  END ) as VOICE_UK_ONNET_MOBILE_calls_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_count END ) as VOICE_UK_VOICEMAIL_calls_m,								
								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - INTL'  then  usg_count END ) as SMS_INTL_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MO'  then  usg_count END ) as SMS_PREMIUM_MO_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MT'  then  usg_count END ) as SMS_PREMIUM_MT_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - Roaming'  then  usg_count END ) as SMS_ROAMING_m,								
sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK'  then  usg_count END ) as SMS_UK_m,								
								
/* facebook and twitter*/								
sum(CASE WHEN Dialled_Digits_Id IN (36,37) then usg_count END ) as FACEBOOK_SHORTCODE_m,								
sum(CASE WHEN Dialled_Digits_Id =1 then usg_count END ) as TWITTER_SHORTCODE_m								
	from nuc_pl_user_view.subscr_usage_summ_monthly a							
		left join nuc_pl_user_view.prepay_srvce_type b						
			on a.srvce_type_id=b.Prepay_Srvce_Type_Id					
				where event_month_end_dt= &dateref.				
					group by  subscr_id);			
quit;								
 


/* claudia test   - feb 2020*/

proc sql;
     connect to teradata
           (user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer');
     execute (DELETE insights_consumer.lm_lastmonth; COMMIT;) by teradata;
quit;

proc append data=work.lastmonth
base=ICONSUMR.lm_lastmonth (bulkload=yes);
run;

	
proc sql;
     connect to teradata
           (user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer');
     execute (DELETE insights_rm.lm_threemnths; COMMIT;) by teradata;
quit;

proc append data=work.last3months
base=ICONSUMR.lm_threemnths (bulkload=yes);
run;				
								
/*LOGfile error for Part  /* claudia test   - feb 2020*/

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         
25         proc append data=work.lastmonth
26         base=ICONSUMR.lm_lastmonth (bulkload=yes);
27         run;

NOTE: Appending WORK.LASTMONTH to ICONSUMR.lm_lastmonth.
NOTE: There were 1 observations read from the data set WORK.LASTMONTH.
NOTE: 0 observations added.
NOTE: The data set ICONSUMR.lm_lastmonth has . observations and 31 variables.
ERROR: Teradata connection: Existing ERROR table(s) or Incorrect use of lm_lastmonth in  Fast Load operation. Correct error and 
       restart as an APPEND process with option TPT_RESTART=YES. Since no checkpoints were taken, if the previous run used 
       FIRSTOBS=n, use the same value in the restart.
NOTE: Statements not processed because of errors noted above.

Re: proc apend

Shmuel — Mon, 17 Feb 2020 21:22:40 GMT

Pay attention that the log contains a line

23         GOPTIONS ACCESSIBLE;

that does not appear in the code you sent.

Maybe the error does not belong to the code posted ?!

Re: proc apend

PerryNoble — Mon, 17 Feb 2020 23:10:30 GMT

Hello,

It's been about a year since working with Teradata, but I seem to recall needing to use MODE=ANSI in the connect line of the PROC SQL statement when using EXECUTE with appending records. Unfortunately, I don't have a TD environment at my current employer.

Perry Noble

Re: proc apend

Kurt_Bremser — Tue, 18 Feb 2020 07:43:19 GMT

@Shmuel wrote:

Pay attention that the log contains a line
23         GOPTIONS ACCESSIBLE;
that does not appear in the code you sent.

Maybe the error does not belong to the code posted ?!

That is just the last line of code that Enterprise Guide sends automatically before every submit.

You also see:

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

which is the result of the ODS statements sent by EG.

Re: proc apend

jorquec — Tue, 18 Feb 2020 08:00:03 GMT

I have copied all the log again

So maybe now it could be easier to understand.

Many thanks

1                                                          The SAS System                           07:43 Tuesday, February 18, 2020

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='C:\Users\jorquec.UK\Desktop\PAYG refress issue feb 2020.egp';
6          %LET _CLIENTPROJECTNAME='PAYG refress issue feb 2020.egp';
7          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=PNG;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/SAS94/software/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         																	
25         /*libname ipaygo teradata user= &td_usr. password = &td_pass. server = 'edwprod' database = 'insights_paygo'; 							
26         */
27         							
28         								
29         								
30         								
31         								
32         /* Change Here */ 								
33         %let dateref =&sql_date2.; /* End date of month running */								
34         %let start =&sql_date6.; /* Three months prior */ /*changed: 180430: was set to&sql_date5. by mistake*/								
35         								
36         /* ########################################################## */								
37         								
38         /* Step 1 */								
39               								
40         proc sql ;
40       !           								
41         *drop table sm.sj_90day_active_base;								
42         *drop table sm.lm_base_4;								
43         								
44         drop table ipaygo.sj_90day_active_base;
WARNING: File IPAYGO.sj_90day_active_base.DATA does not exist.
WARNING: Table IPAYGO.sj_90day_active_base has not been dropped.
44       !                                        	
45         drop table irm.sj_90day_active_base;
WARNING: File IRM.sj_90day_active_base.DATA does not exist.
WARNING: Table IRM.sj_90day_active_base has not been dropped.
45       !                                     /*180516: moving to irm*/		
46         drop table iconsumr.lm_base_4;
WARNING: File ICONSUMR.lm_base_4.DATA does not exist.
WARNING: Table ICONSUMR.lm_base_4 has not been dropped.
46       !                               								
47         run;								
2                                                          The SAS System                           07:43 Tuesday, February 18, 2020

NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
48         								
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.46 seconds
      user cpu time       0.05 seconds
      system cpu time     0.01 seconds
      memory              226.81k
      OS Memory           22688.00k
      Timestamp           18/02/2020 07:44:51 AM
      Step Count                        16  Switch Count  0
      Page Faults                       0
      Page Reclaims                     75
      Page Swaps                        0
      Voluntary Context Switches        24
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           8
      
49         proc sql;								

50         connect to teradata								
51         (user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer' mode = teradata);
51       !                                                                                                      								
52         EXECUTE (								
53         CREATE MULTISET VOLATILE TABLE base_90day AS								
54         (								
55         SELECT mpn,								
56                MAX(conn_dte) AS conn_dte,								
57                SUM(consec_days) AS total_days,								
58                MAX(consec_days) AS max_consec,     								
59                COUNT(mpn) AS consec_sessions,                      								
60                MIN(first_call) AS first_call2,								
61         	   MAX(last_call) AS last_call2,							
62         	   first_call2 -  ((DATE&dateref.) - 89)  AS first_gap, 							
63                CASE WHEN MAX(max_days) >= first_gap THEN MAX(max_days) ELSE first_gap END AS max_gap,								
64         	   consec_sessions -1 AS non_sess1,							
65                CASE WHEN first_gap > 0 THEN non_sess1 +1 ELSE non_sess1 END AS non_sess2,								
66                CASE WHEN last_call2 < DATE&dateref. THEN non_sess2 +1 ELSE non_sess2 END AS zu_sessions,								
67                DATE&dateref. - last_call2 AS zero_usage, 								
68         	   90 - total_days AS total_zu_days,							
69                CASE WHEN zu_sessions = 0 THEN 0 ELSE CAST(total_zu_days AS FLOAT) / CAST(zu_sessions AS FLOAT) END								
70                 AS avg_zu_per_session,								
71                								
72                 CASE WHEN zero_usage = 0 THEN 'a: 0'								
73         	     WHEN ZERO_USAGE <= 6 THEN 'b: 1-6'							
74         	     WHEN ZERO_USAGE <= 13 THEN 'c: 7-13'							
75         	     WHEN ZERO_USAGE <= 20 THEN 'd: 14-20'							
76         	     WHEN ZERO_USAGE <= 27 THEN 'e: 21-27'							
77         	     WHEN ZERO_USAGE <= 34 THEN 'f: 28-34'							
78         	     WHEN ZERO_USAGE <= 41 THEN 'g: 36-41'							
79         	     WHEN ZERO_USAGE <= 48 THEN 'h:42-48'							
80         	     WHEN ZERO_USAGE <= 55 THEN 'i:49-55'							
81         	     WHEN ZERO_USAGE <= 62 THEN 'j:56-62'							
82         	     WHEN ZERO_USAGE <= 69 THEN 'k:63-69'							
83         	     WHEN ZERO_USAGE <= 76 THEN 'l:70-76'							
84         	     WHEN ZERO_USAGE <= 83 THEN 'm:77-83'							
85         	     WHEN ZERO_USAGE <= 90 THEN 'n:84-90'							
86         	    ELSE 'o: 91 + ' END AS days_since_last_use_band							
3                                                          The SAS System                           07:43 Tuesday, February 18, 2020

87               FROM								
88               (                               								
89                 SELECT mpn,								
90                        call_group,								
91                        MAX(conn_dte) AS conn_dte,								
92                        COUNT(mpn) AS consec_days,								
93                        MAX(days_between2) AS max_days,								
94                        MIN(calldate) AS first_call,								
95                        MAX(calldate) AS last_call								
96                 FROM								
97         		       (						
98         			   SELECT mpn,					
99                               calldate,								
100                              conn_dte,								
101                              days_between,								
102                              days_between2,								
103                              ZEROIFNULL(SUM(days_between2) OVER (PARTITION BY mpn ORDER BY calldate ROWS BETWEEN UNBOUNDED
103      ! PRECEDING AND CURRENT ROW)) AS call_group								
104                                                                                                        								
105                              FROM								
106                                  (               								
107                                    SELECT mpn,								
108                                           conn_dte,								
109                                           calldate,								
110                                           (calldate - DATE '1900-01-01') - SUM(calldate - DATE '1900-01-01') OVER (PARTITION BY
110      ! mpn								
111                                               ORDER BY calldate ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) - 1 AS
111      ! days_between,								
112                                           CASE WHEN days_between IS NULL THEN 0 ELSE days_between END AS days_between2								
113                                        FROM								
114                                               (SELECT chrgd_subscr_cd AS mpn ,calldate, MAX(connected_dt) AS conn_dte, MAX(1) AS
114      !  usage_flag								
115                                  FROM 								
116                                            ( SELECT								
117        		                               a.subscr_id, 						
118                                               c.connected_dt,								
119                                               chrgd_subscr_cd,								
120                                               a.call_type_id, 								
121                                               bill_start_dt AS calldate 								
122                       		                                   FROM nuc_user_view.PREPYD_CDR AS a 						
123                             JOIN insights_view_sandbox.ACCT_SUBSCR_CONNECTION_DT AS c 								
124        					 ON a.subscr_id = c.subscr_id  			
125                               WHERE bill_start_dt >= ((DATE&dateref.) - 89) AND bill_start_dt <= DATE&dateref. AND
125      ! a.call_type_id IN(36,14,4,3,9)  								
126         ) AS a 								
127              GROUP BY 1,2								
128        	  ) AS d							
129                    ) AS e								
130                        ) AS f								
131                            GROUP BY mpn, call_group								
132                                                     ) AS g      								
133                                                       GROUP BY mpn								
134                                                       having ZERO_USAGE <= 90 								
135        ) WITH DATA PRIMARY INDEX(mpn) ON COMMIT PRESERVE ROWS) BY TERADATA;
135      !                                                                             								
136        								
137        								
138        EXECUTE(								
4                                                          The SAS System                           07:43 Tuesday, February 18, 2020

139        CREATE MULTISET VOLATILE TABLE base1 AS (
140        sel a.mpn,
141            st.subscr_id,
142            effective_from_dt,
143            prepyd_ind,
144            finance_active,
145            O2Money_Ind,
146            Tariff_Offering_Id,
147            Needs_Based_Segment_Id,
148            Device_Type_Id,
149            case when st.insurance_product_ind = 'Y' then 1 else 0 end Insurance_Ind,
150            dslu,
151            CUST_ID
152            from (sel MPN, zero_usage as dslu from base_90day) as a
153        				inner join  (sel Subscr_Num, prepyd_ind, subscr_id, effective_from_dt , Acquisition_Channel_Id,
153      ! Prod_Id,Internal_Base_Ind as finance_active, O2Money_Ind,
154                Tariff_Offering_Id,  Needs_Based_Segment_Id, Device_Type_Id, insurance_product_ind,cust_id,sales_channel_id
155        								from NUC_PL_USER_VIEW.SUBSCR_TRACK
156        								WHERE date&dateref. between effective_from_DT and effective_TO_DT and
157        								prepyd_ind='Y' and subscr_type_id=197 and Mobile_Broadband_Ind='N' ) as st
158                on a.mpn=st.Subscr_Num
159        
160                where st.prod_id not in ( '4619', '4696', '4926', '5041', '5060', '5070', '5086', '5166', '5275', '5275',
160      ! '249543','249546', '250531', '251465', '251649', '252211',
161        '253681', '254408', '255441', '256156', '262562', '264809', '266583', '267281', '268647', '270313', '270451', '270490',
161      ! '271578', '276332', '288338', '290921' )
162         QUALIFY(ROW_NUMBER() OVER (PARTITION BY mpn  ORDER BY st.subscr_id desc, effective_from_dt DESC )) =1
163         ) WITH DATA ON COMMIT PRESERVE ROWS )BY TERADATA;
163      !                                                   								
164        								
165         /* modified table base_1 on 7th march 2018 (add variable sms_allowance ) */
166        EXECUTE(
167        CREATE  MULTISET VOLATILE TABLE base_1 AS (
168           select a.*,
169        		    coalesce(e.consumer_tariff_type_cd,e.bolton_type_cd) as allowance_group_name,
170        		  offering_name as allowance_name,
171        			CASE WHEN allowance_name = 'BIGBUNDLE5' THEN 200
172        				 WHEN allowance_name = 'BIGBUNDLE10' THEN 1000
173        				 WHEN allowance_name = 'BIGBUNDLE15' THEN 2000
174        				 WHEN allowance_name = 'BIGBUNDLE20' THEN 4000
175        				 WHEN allowance_name = 'BIGBUNDLE25' THEN 4000
176        				 WHEN allowance_name = 'BIGBUNDLE30' THEN 4000
177        				 WHEN allowance_name = 'BIGDATABUNDLE10' THEN 100
178        			   /* WHEN allowance_name = '10 Big Bundle Online' THEN 100 */
179        				 WHEN allowance_name = 'INTERNATIONALSIM' THEN 3000
180        				 WHEN allowance_name = 'O2UNLIMITEDONNET' THEN 3000
181        				 WHEN allowance_name = 'TEXTWEBFEB2010RT' THEN 500
182        				 WHEN allowance_name = 'PAYANDGOGOGO10UNLIMITED' THEN 5000
183        				 WHEN allowance_name = 'SIMPLYPAYASYOUGO' THEN 3000
184        	 ELSE 0 END AS sms_allowance
185           from base1 a
186        		/* left join NUC_PL_USER_VIEW.ALLOWANCE as c
187        			on a.allowance_id=c.allowance_id
188        		left join NUC_PL_USER_VIEW.ALLOWANCE_GROUP as d
189        			on d.allowance_group_id=c.allowance_group_id --removed */
190        		left join (select * from  nuc_pl_user_view.offering_catalog where offering_billing_sys_name='prp') e
191        			on a.Tariff_Offering_Id = e.offering_id
192        ) WITH DATA ON COMMIT PRESERVE ROWS)BY TERADATA;
5                                                          The SAS System                           07:43 Tuesday, February 18, 2020

192      !                                                 								
193        								
194        								
195        EXECUTE(								
196        CREATE  MULTISET VOLATILE TABLE  base_2 AS (								
197           select a.*,Needs_Based_Segment_Type_Name as needs_segment_desc								
198           from base_1 a								
199           left join NUC_PL_USER_VIEW.NEEDS_BASED_SEGMENT_TYPE as e								
200           on a.Needs_Based_Segment_Id=e.Needs_Based_Segment_type_Id								
201        ) WITH DATA ON COMMIT PRESERVE ROWS)BY TERADATA;
201      !                                                 								
202        								
203        /* Modified table base_3 on 7th marh 2018 (add variable DEVICE_TYPE,IS_4G) */
204        EXECUTE(
205        CREATE MULTISET VOLATILE TABLE base_3 AS (
206        	  SELECT A.* ,G.MANUFACTURER AS VENDOR_DESC , SMART_PHONE_IND, MODEL_NUMBER,N.DEVICE_TYPE,N.IS_4G
207        	  FROM BASE_2 A
208        	  LEFT JOIN  NUC_PL_USER_VIEW.DEVICE_TYPE AS G
209        	  ON A.DEVICE_TYPE_ID=G.DEVICE_TYPE_ID
210        	  LEFT JOIN NUC_PL_USER_VIEW.DEVICE_CATALOG N
211        	  ON G.DEVICE_TYPE_ID=N.DEVICE_TYPE_ID
212        	
213        )WITH DATA ON COMMIT PRESERVE ROWS)BY TERADATA;
213      !                                                								
214        								
215        /* Modified on table base_4 on 7th marh 2018 (add variable tenure_months ) */
216        EXECUTE(
217        CREATE MULTISET VOLATILE TABLE base_4 AS (
218        	select a.*,
219        	CASE WHEN tenure_months =0 THEN 'zero'
220        						WHEN tenure_months >0 and tenure_months <=3  THEN 'up to 3 mth'
221        						WHEN tenure_months >3 and tenure_months <=6  THEN 'up to 6 mth'
222        						WHEN tenure_months >6 and tenure_months <=12  THEN 'up to 12 mth'
223        						WHEN tenure_months >12 and tenure_months <=18  THEN 'up to 18 mth'
224        						WHEN tenure_months >18 and tenure_months <=24  THEN 'up to 24 mth'
225        						WHEN tenure_months >24 and tenure_months <=36  THEN 'up to 36 mth'
226        						WHEN tenure_months >36 and tenure_months <=48  THEN 'up to 48 mth'
227        						WHEN tenure_months >48 and tenure_months <=60 THEN 'up to 60 mth'
228        						WHEN tenure_months >60 THEN 'over 60mths' ELSE 'missing' END AS tenure_desc,
229        	F.tenure_months
230        	from base_3 a
231        	left join  NUC_PL_USER_VIEW.SUBSCR_LIFE_EVENT as f
232        	on a.subscr_id=f.subscr_id
233        )WITH DATA ON COMMIT PRESERVE ROWS)BY TERADATA;
233      !                                                								
234        								
235        /* --Modified table insights_consumer.lm_base_4 on 7th marh 2018 (add variable pcode_flag ) */
236        EXECUTE(
237        CREATE SET TABLE INSIGHTS_CONSUMER.LM_BASE_4 AS
238        /*CREATE SET TABLE SASMOD_MANAGER.LM_BASE_4 AS */
239        	(
240        		SELECT B.*,
241        		CASE WHEN C.POST_CODE IS NOT NULL THEN 1 ELSE 0 END AS PCODE_FLAG
242        				
243        		FROM BASE_4 B
244        		LEFT JOIN NUC_PL_USER_VIEW.CUST_TRACK C
245        		ON C.CUST_ID=B.CUST_ID
246        		and C.cust_id <>-1
6                                                          The SAS System                           07:43 Tuesday, February 18, 2020

247                AND &dateref.  BETWEEN C.eff_from_DT AND C.eff_TO_DT
248        
249        	) WITH DATA PRIMARY INDEX(SUBSCR_ID);)BY TERADATA;
250        QUIT;
NOTE: PROCEDURE SQL used (Total process time):
      real time           3:39.52
      user cpu time       0.03 seconds
      system cpu time     0.00 seconds
      memory              377.12k
      OS Memory           22944.00k
      Timestamp           18/02/2020 07:48:30 AM
      Step Count                        17  Switch Count  0
      Page Faults                       0
      Page Reclaims                     76
      Page Swaps                        0
      Voluntary Context Switches        46
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           56
      
250      !      								

251        								
252        data base_4;	
253        set iconsumr.lm_base_4;	
254        /*set sm.lm_base_4;	*/							
255        run;

NOTE: There were 3056343 observations read from the data set ICONSUMR.lm_base_4.
NOTE: The data set WORK.BASE_4 has 3056343 observations and 24 variables.
NOTE: DATA statement used (Total process time):
      real time           10.18 seconds
      user cpu time       4.77 seconds
      system cpu time     5.98 seconds
      memory              5434.68k
      OS Memory           30888.00k
      Timestamp           18/02/2020 07:48:40 AM
      Step Count                        18  Switch Count  4105
      Page Faults                       0
      Page Reclaims                     1256
      Page Swaps                        0
      Voluntary Context Switches        27332
      Involuntary Context Switches      131
      Block Input Operations            368
      Block Output Operations           11678224
      
255      !     								

256        								
257        proc sort data= base_4 ;								
258        by subscr_id  descending effective_from_dt;								
259        run;

NOTE: There were 3056343 observations read from the data set WORK.BASE_4.
NOTE: The data set WORK.BASE_4 has 3056343 observations and 24 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           10.27 seconds
      user cpu time       7.51 seconds
7                                                          The SAS System                           07:43 Tuesday, February 18, 2020

      system cpu time     8.39 seconds
      memory              6026232.51k
      OS Memory           6051692.00k
      Timestamp           18/02/2020 07:48:51 AM
      Step Count                        19  Switch Count  9
      Page Faults                       0
      Page Reclaims                     56999
      Page Swaps                        0
      Voluntary Context Switches        6237
      Involuntary Context Switches      191
      Block Input Operations            376
      Block Output Operations           11678216
      
259      !     								

260        								
261        data base_4a;								
262        set base_4;								
263        by subscr_id;								
264        if first.subscr_id;								
265        run;

NOTE: There were 3056343 observations read from the data set WORK.BASE_4.
NOTE: The data set WORK.BASE_4A has 3056343 observations and 24 variables.
NOTE: DATA statement used (Total process time):
      real time           8.63 seconds
      user cpu time       0.77 seconds
      system cpu time     6.88 seconds
      memory              9886.78k
      OS Memory           39592.00k
      Timestamp           18/02/2020 07:48:59 AM
      Step Count                        20  Switch Count  9
      Page Faults                       0
      Page Reclaims                     2032
      Page Swaps                        0
      Voluntary Context Switches        88
      Involuntary Context Switches      22
      Block Input Operations            360
      Block Output Operations           11678216
      
265      !     								

266        								
267        proc sql ;
267      !           								
268        *drop table sm.sj_90day_active_base;								
269        *drop table sm.lm_base_4;								
270        drop table ipaygo.sj_90day_active_base;
WARNING: File IPAYGO.sj_90day_active_base.DATA does not exist.
WARNING: Table IPAYGO.sj_90day_active_base has not been dropped.
270      !                                        	
271        drop table irm.sj_90day_active_base;
WARNING: File IRM.sj_90day_active_base.DATA does not exist.
WARNING: Table IRM.sj_90day_active_base has not been dropped.
271      !                                     	/*180516: moving to irm so need the two drop statements*/
272        drop table iconsumr.lm_base_4;
NOTE: Table ICONSUMR.lm_base_4 has been dropped.
272      !                               								
8                                                          The SAS System                           07:43 Tuesday, February 18, 2020

273        run;								
NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect.
274        	
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.18 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              147.96k
      OS Memory           25504.00k
      Timestamp           18/02/2020 07:48:59 AM
      Step Count                        21  Switch Count  1
      Page Faults                       0
      Page Reclaims                     15
      Page Swaps                        0
      Voluntary Context Switches        13
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           8
      
275        data irm.sj_90day_active_base	/*180516: moving to irm */

276        /*data sm.sj_90day_active_base 	 */							
277        (dbcreate_table_opts="primary index (subscr_id)" bulkload=yes fastload=yes  sleep=5 tenacity=1);								
278        set base_4a;								
279        run;

NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 3056343 observations read from the data set WORK.BASE_4A.
NOTE: The data set IRM.sj_90day_active_base has 3056343 observations and 24 variables.
NOTE: Teradata connection: TPT Fastload has inserted 3056343 row(s).
NOTE: DATA statement used (Total process time):
      real time           1:15.93
      user cpu time       13.52 seconds
      system cpu time     12.90 seconds
      memory              5235.50k
      OS Memory           31652.00k
      Timestamp           18/02/2020 07:50:15 AM
      Step Count                        22  Switch Count  105
      Page Faults                       0
      Page Reclaims                     4682
      Page Swaps                        0
      Voluntary Context Switches        46639
      Involuntary Context Switches      349
      Block Input Operations            0
      Block Output Operations           24
      
279      !     								

280        								
281        proc sql;
281      !          								
282        connect to teradata (user=&td_usr. password=&td_pass.  server = 'edwprod' database = 'insights_consumer');
282      !                                                                                                           								
283        create table last3months as select * from connection to teradata								
284        (select subscr_id,								
285        /* top level*/								
286        sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_actvty_duration END )/60 as VOICE_mins,								
287        sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_count END ) as VOICE_CALLs,								
9                                                          The SAS System                           07:43 Tuesday, February 18, 2020

288        								
289        /* need to be carful if using in sql - due to integers*/								
290        sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_actvty_duration END )/60 as VIDEO_mins,								
291        sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_count END ) as VIDEO_CALLs,								
292        sum(CASE WHEN prepay_Srvce_Type_Category='SMS' THEN  usg_count END ) as SMS,								
293        sum(CASE WHEN prepay_Srvce_Type_Category='MMS' THEN  usg_count END ) as MMS,								
294        								
295        /* note data wil include mbb - this will be excluded when join to base*/								
296        sum(CASE WHEN prepay_Srvce_Type_Category='DATA' THEN usg_data_size END) AS DATA_MB,								
297        								
298        /* split voice by different categories*/								
299        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_actvty_duration END )/60 as VOICE_intl_mins,								
300        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_actvty_duration END )/60 as
300      ! VOICE_other_mins,								
301        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_actvty_duration END )/60 as
301      ! VOICE_ROAMING_MO_mins,								
302        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_actvty_duration END )/60 as
302      ! VOICE_ROAMING_MT_mins,								
303        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_actvty_duration END )/60 as
303      ! VOICE_UK_LANDLINE_mins,								
304        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_actvty_duration END )/60 as
304      ! VOICE_UK_OFFNET_MOBILE_mins,								
305        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_actvty_duration END )/60 as
305      ! VOICE_UK_ONNET_MOBILE_mins,								
306        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_actvty_duration END )/60 as
306      ! VOICE_UK_VOICEMAIL_mins,								
307        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_count END ) as VOICE_intl_calls,								
308        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_count  END ) as VOICE_other_calls,								
309        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_count  END ) as VOICE_ROAMING_MO_calls,								
310        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_count  END ) as VOICE_ROAMING_MT_calls,								
311        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_count  END ) as VOICE_UK_LANDLINE_calls,								
312        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_count  END ) as
312      ! VOICE_UK_OFFNET_MOBILE_calls,								
313        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_count  END ) as
313      ! VOICE_UK_ONNET_MOBILE_calls,								
314        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_count END ) as
314      ! VOICE_UK_VOICEMAIL_calls,								
315                								
316        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - INTL'  then  usg_count END ) as SMS_INTL,								
317        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MO'  then  usg_count END ) as SMS_PREMIUM_MO,								
318        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MT'  then  usg_count END ) as SMS_PREMIUM_MT,								
319        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - Roaming'  then  usg_count END ) as SMS_ROAMING,								
320        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK'  then  usg_count END ) as SMS_UK,								
321                								
322        /* facebook and twitter*/								
323        sum(CASE WHEN Dialled_Digits_Id IN (36,37) then usg_count END ) as FACEBOOK_SHORTCODE,								
324        sum(CASE WHEN Dialled_Digits_Id =1 then usg_count END ) as TWITTER_SHORTCODE,								
325                        								
326        /* these are 3 monthe averages below*/								
327        sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_actvty_duration END )/60/3 as VOICE_mins_AV,								
328        sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_count END )/3 as VOICE_CALLs_AV,								
329                								
330        sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_actvty_duration END )/60/3 as VIDEO_mins_AV,								
331        sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_count END )/3 as VIDEO_CALLs_AV,								
332                								
333        sum(CASE WHEN prepay_Srvce_Type_Category='SMS' THEN  usg_count END )/3 as SMS_AV,								
334                								
335        sum(CASE WHEN prepay_Srvce_Type_Category='MMS' THEN  usg_count END )/3 as MMS_AV,								
10                                                         The SAS System                           07:43 Tuesday, February 18, 2020

336               								
337        sum(CASE WHEN prepay_Srvce_Type_Category='DATA' THEN usg_data_size END)/3 AS DATA_MB_AV,								
338                        								
339        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_actvty_duration END )/60/3 as
339      ! V_intl_mins_AV,								
340        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_actvty_duration END )/60/3 as
340      ! V_other_mins_AV,								
341        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_actvty_duration END )/60/3 as
341      ! V_ROAMING_MO_mins_AV,								
342        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_actvty_duration END )/60/3 as
342      ! V_ROAMING_MT_mins_AV,								
343        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_actvty_duration END )/60/3 as
343      ! V_UK_LANDLINE_mins_AV,								
344        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_actvty_duration END )/60/3 as
344      ! V_UK_OFFNET_MOBILE_mins_AV,								
345        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_actvty_duration END )/60/3 as
345      ! V_UK_ONNET_MOBILE_mins_AV,								
346        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_actvty_duration END )/60/3 as
346      ! V_UK_VOICEMAIL_mins_AV,								
347                								
348        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_count END )/3 as V_intl_calls_Av,								
349        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_count  END )/3 as V_other_calls_AV,								
350        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_count  END )/3 as V_ROAMING_MO_calls_AV,								
351        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_count  END )/3 as V_ROAMING_MT_calls_AV,								
352        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_count  END )/3 as
352      ! V_UK_LANDLINE_calls_AV,								
353        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_count  END )/3 as
353      ! V_UK_OFFNET_MOBILE_calls_AV,								
354        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_count  END )/3 as
354      ! V_UK_ONNET_MOBILE_calls_AV,								
355        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_count END )/3 as
355      ! V_UK_VOICEMAIL_calls_AV,								
356                								
357        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - INTL'  then  usg_count END )/3 as SMS_INTL_AV,								
358        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MO'  then  usg_count END )/3 as SMS_PREMIUM_MO_AV,								
359        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MT'  then  usg_count END )/3 as SMS_PREMIUM_MT_AV,								
360        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - Roaming'  then  usg_count END )/3 as SMS_ROAMING_AV,								
361        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK'  then  usg_count END )/3 as SMS_UK_AV,								
362                								
363        sum(CASE WHEN Dialled_Digits_Id IN (36,37) then usg_count END )/3 as FACEBOOK_SC_AV,								
364        sum(CASE WHEN Dialled_Digits_Id =1 then usg_count END )/3 as TWITTER_SC_AV								
365                								
366        /*sum(CASE WHEN prepay_Srvce_Type_Category='WAP' THEN usg_data_size END) AS WAP,								
367        sum(CASE WHEN prepay_Srvce_Type_Category='WIFI' THEN usg_data_size END) AS WIFI*/								
368                from nuc_pl_user_view.SUBSCR_USAGE_SUMM_MONTHLY a								
369        			left join nuc_pl_user_view.PREPAY_SRVCE_TYPE b					
370        			on a.srvce_type_id=b.Prepay_Srvce_Type_Id					
371        			where  event_month_end_dt>=&start. and  event_month_end_dt<=&dateref.					
372        				group by  subscr_id);
NOTE: Table WORK.LAST3MONTHS created, with 18047720 rows and 61 columns.

372      !                          				
373        quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           4:03.87
      user cpu time       58.21 seconds
      system cpu time     35.44 seconds
      memory              6035.50k
11                                                         The SAS System                           07:43 Tuesday, February 18, 2020

      OS Memory           31140.00k
      Timestamp           18/02/2020 07:54:19 AM
      Step Count                        23  Switch Count  130519
      Page Faults                       0
      Page Reclaims                     540
      Page Swaps                        0
      Voluntary Context Switches        796771
      Involuntary Context Switches      13892
      Block Input Operations            168
      Block Output Operations           17239864
      
373      !      								

374        								
375        								
376        proc sql;
376      !          								
377        connect to teradata (user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_smecorp');
377      !                                                                                                         								
378        create table lastmonth as select * from connection to teradata								
379        (select subscr_id,								
380        /* top level*/								
381        sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_actvty_duration END )/60 as VOICE_mins_m,								
382        sum(CASE WHEN prepay_Srvce_Type_Category='VOICE' THEN  usg_count END ) as VOICE_CALLs_m,								
383                								
384        /* need to be carful if using in sql - due to integers*/								
385        sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_actvty_duration END )/60 as VIDEO_mins_m,								
386        sum(CASE WHEN prepay_Srvce_Type_Category='VIDEO' THEN  usg_count END ) as VIDEO_CALLs_m,								
387               								
388        sum(CASE WHEN prepay_Srvce_Type_Category='SMS' THEN  usg_count END ) as SMS_m,								
389                								
390        sum(CASE WHEN prepay_Srvce_Type_Category='MMS' THEN  usg_count END ) as MMS_m,								
391        /* note data wil include mbb - this will be excluded when join to base*/								
392        sum(CASE WHEN prepay_Srvce_Type_Category='DATA' THEN usg_data_size END) AS DATA_MB_m,								
393                								
394        /* split voice by different categories*/								
395        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_actvty_duration END )/60 as
395      ! VOICE_intl_mins_m,								
396        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_actvty_duration END )/60 as
396      ! VOICE_other_mins_m,								
397        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_actvty_duration END )/60 as
397      ! VOICE_ROAMING_MO_mins_m,								
398        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_actvty_duration END )/60 as
398      ! VOICE_ROAMING_MT_mins_m,								
399        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_actvty_duration END )/60 as
399      ! VOICE_UK_LANDLINE_mins_m,								
400        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_actvty_duration END )/60 as
400      ! VOICE_UK_OFFNET_MOBILE_mins_m,								
401        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_actvty_duration END )/60 as
401      ! VOICE_UK_ONNET_MOBILE_mins_m,								
402        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_actvty_duration END )/60 as
402      ! VOICE_UK_VOICEMAIL_mins_m,								
403        								
404        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - INTL'  then  usg_count END ) as VOICE_intl_calls_m,								
405        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OTHER'  then  usg_count  END ) as VOICE_other_calls_m,								
406        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MO'  then  usg_count  END ) as VOICE_ROAMING_MO_calls_m,								
407        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - Roaming - MT'  then  usg_count  END ) as VOICE_ROAMING_MT_calls_m,								
408        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - LANDLINE'  then  usg_count  END ) as
12                                                         The SAS System                           07:43 Tuesday, February 18, 2020

408      ! VOICE_UK_LANDLINE_calls_m,								
409        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - OFFNET MOBILE'  then  usg_count  END ) as
409      ! VOICE_UK_OFFNET_MOBILE_calls_m,								
410        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - ONNET MOBILE'  then  usg_count  END ) as
410      ! VOICE_UK_ONNET_MOBILE_calls_m,								
411        sum(CASE WHEN Prepay_Srvce_Type_Group='VOICE - UK - VOICEMAIL'  then  usg_count END ) as
411      ! VOICE_UK_VOICEMAIL_calls_m,								
412        								
413        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - INTL'  then  usg_count END ) as SMS_INTL_m,								
414        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MO'  then  usg_count END ) as SMS_PREMIUM_MO_m,								
415        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK - PREMIUM MT'  then  usg_count END ) as SMS_PREMIUM_MT_m,								
416        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - Roaming'  then  usg_count END ) as SMS_ROAMING_m,								
417        sum(CASE WHEN Prepay_Srvce_Type_Group='SMS - UK'  then  usg_count END ) as SMS_UK_m,								
418        								
419        /* facebook and twitter*/								
420        sum(CASE WHEN Dialled_Digits_Id IN (36,37) then usg_count END ) as FACEBOOK_SHORTCODE_m,								
421        sum(CASE WHEN Dialled_Digits_Id =1 then usg_count END ) as TWITTER_SHORTCODE_m								
422        	from nuc_pl_user_view.subscr_usage_summ_monthly a							
423        		left join nuc_pl_user_view.prepay_srvce_type b						
424        			on a.srvce_type_id=b.Prepay_Srvce_Type_Id					
425        				where event_month_end_dt= &dateref.				
426        					group by  subscr_id);
NOTE: Table WORK.LASTMONTH created, with 16288720 rows and 31 columns.

426      !                           			
427        quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           1:50.49
      user cpu time       28.48 seconds
      system cpu time     14.71 seconds
      memory              5455.78k
      OS Memory           31396.00k
      Timestamp           18/02/2020 07:56:10 AM
      Step Count                        24  Switch Count  60644
      Page Faults                       0
      Page Reclaims                     345
      Page Swaps                        0
      Voluntary Context Switches        421438
      Involuntary Context Switches      4915
      Block Input Operations            248
      Block Output Operations           7927856
      
427      !      								

428         /* CLAUDIA								
429         								
430        proc sql ;								
431        drop table iconsumr.lm_lastmonth;								
432        drop table irm.lm_threemnths;								
433        *drop table sm.lm_lastmonth;								
434        *drop table sm.lm_threemnths;								
435        run;		
436        */						
437        
438        /*data iconsumr.lm_lastmonth*/
439        /*data sm.lm_lastmonth*/
440        /*( dbcreate_table_opts="primary index (subscr_id)" bulkload=yes fastload = yes);		*/
441        /*( dbcreate_table_opts="primary index (subscr_id)" bulkload=yes);								*/
13                                                         The SAS System                           07:43 Tuesday, February 18, 2020

442        /*set lastmonth;								*/
443        /*run;		*/
444        
445        	
446        
447        /*data irm.lm_threemnths	*/
448        /*data sm.lm_threemnths	*/							
449        /*( dbcreate_table_opts="primary index (subscr_id)" bulkload=yes fastload = yes);		*/
450        /*( dbcreate_table_opts="primary index (subscr_id)" bulkload=yes);									*/
451        /*set last3months;								*/
452        /*run;			*/
453        
454        
455        /* claudia test   - feb 2020*/
456        
457        proc sql;
458             connect to teradata
459                   (user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer');
460             execute (DELETE insights_consumer.lm_lastmonth; COMMIT;) by teradata;
461        quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.30 seconds
      user cpu time       0.01 seconds
      system cpu time     0.01 seconds
      memory              100.06k
      OS Memory           26272.00k
      Timestamp           18/02/2020 07:56:10 AM
      Step Count                        25  Switch Count  0
      Page Faults                       0
      Page Reclaims                     15
      Page Swaps                        0
      Voluntary Context Switches        8
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

462        
463        proc append data=work.lastmonth
464        base=ICONSUMR.lm_lastmonth (bulkload=yes);
465        run;

NOTE: Appending WORK.LASTMONTH to ICONSUMR.lm_lastmonth.
NOTE: There were 1 observations read from the data set WORK.LASTMONTH.
NOTE: 0 observations added.
NOTE: The data set ICONSUMR.lm_lastmonth has . observations and 31 variables.
ERROR: Teradata connection: Existing ERROR table(s) or Incorrect use of lm_lastmonth in  Fast Load operation. Correct error and 
       restart as an APPEND process with option TPT_RESTART=YES. Since no checkpoints were taken, if the previous run used 
       FIRSTOBS=n, use the same value in the restart.
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
      real time           1.25 seconds
      user cpu time       0.12 seconds
      system cpu time     0.02 seconds
      memory              2777.56k
      OS Memory           27556.00k
      Timestamp           18/02/2020 07:56:11 AM
      Step Count                        26  Switch Count  0
14                                                         The SAS System                           07:43 Tuesday, February 18, 2020

      Page Faults                       0
      Page Reclaims                     811
      Page Swaps                        0
      Voluntary Context Switches        61
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           16
      
NOTE: The SAS System stopped processing this step because of errors.
466        
467        	


468        proc sql;
469             connect to teradata
470                   (user=&td_usr. password=&td_pass. server = 'edwprod' database = 'insights_consumer');
471             execute (DELETE insights_rm.lm_threemnths; COMMIT;) by teradata;
472        quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.27 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              100.06k
      OS Memory           26272.00k
      Timestamp           18/02/2020 07:56:12 AM
      Step Count                        27  Switch Count  0
      Page Faults                       0
      Page Reclaims                     15
      Page Swaps                        0
      Voluntary Context Switches        11
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

473        
474        proc append data=work.last3months
475        base=ICONSUMR.lm_threemnths (bulkload=yes);
476        run;

NOTE: Appending WORK.LAST3MONTHS to ICONSUMR.lm_threemnths.
NOTE: BASE data set does not exist. DATA file is being copied to BASE file.
NOTE: SAS variable labels, formats, and lengths are not written to DBMS tables.
NOTE: There were 18047720 observations read from the data set WORK.LAST3MONTHS.
NOTE: The data set ICONSUMR.lm_threemnths has 18047720 observations and 61 variables.
NOTE: Teradata connection: TPT Fastload has inserted 18047720 row(s).
NOTE: PROCEDURE APPEND used (Total process time):
      real time           1:48.63
      user cpu time       29.39 seconds
      system cpu time     17.16 seconds
      memory              2901.43k
      OS Memory           27556.00k
      Timestamp           18/02/2020 07:58:00 AM
      Step Count                        28  Switch Count  153
      Page Faults                       1
      Page Reclaims                     883
      Page Swaps                        0
      Voluntary Context Switches        75152
15                                                         The SAS System                           07:43 Tuesday, February 18, 2020

      Involuntary Context Switches      243
      Block Input Operations            200
      Block Output Operations           24
      
476      !     				

477        								
478        /* Run Step 2 - All data step in teradata		
479        
480        GOPTIONS NOACCESSIBLE;
481        %LET _CLIENTTASKLABEL=;
482        %LET _CLIENTPROCESSFLOWNAME=;
483        %LET _CLIENTPROJECTPATH=;
484        %LET _CLIENTPROJECTNAME=;
485        %LET _SASPROGRAMFILE=;
486        
487        ;*';*";*/;quit;run;
488        ODS _ALL_ CLOSE;
489        
490        
491        QUIT; RUN;
492

Re: proc apend

Kurt_Bremser — Wed, 19 Feb 2020 09:58:26 GMT

ERROR: Teradata connection: Existing ERROR table(s) or Incorrect use of lm_lastmonth in  Fast Load operation.

Have that checked by your Teradata experts. That is IMO a message originating from Teradata and just relayed to you by SAS.

Edit: fixed simple typo.