Many thanks for your brilliant solution. It was really helpful. Thanks. ... View more

Thanks so much! Great. ... View more

Hi  thanks for the tip! Could you please tell me how to add now an additional information of the frequency but in percentage ?  /* step1*/ DATA work.counts; set perfmoni.ptwopperfs_1_may20; length m3_topup_index_grp $ 30; format m3_topup_index_grp $char.; If m3_topup_index =0 THEN m3_topup_index_grp='a.Zero'; else if m3_topup_index > 0 and m3_topup_index<=0.5 THEN m3_topup_index_grp= 'b.0-0.5'; else if m3_topup_index > 0.5 and m3_topup_index<=1 THEN m3_topup_index_grp='c.0.5-1'; else if m3_topup_index > 1 and m3_topup_index<=1.5 THEN m3_topup_index_grp= 'd.1- 1.5'; else if m3_topup_index > 1.5 and m3_topup_index<=2.0 THEN m3_topup_index_grp= 'e.1.5-2.0'; else if m3_topup_index > 2.0 THEN m3_topup_index_grp= 'f.Above 2.0'; else m3_topup_index_grp= 'g.Other'; run; /* step 2*/ proc summary data=work.counts ; types m3_topup_index_grp ; class m3_topup_index_grp ; var subscr_id; output out=total_subscrid n(subscr_id)= / autoname autolabel; run; ... View more

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 ... View more

  HI   1200131 means 2020/01/31 1200101  means 2020/01/01    Yes I use these dates to run proc sql on SAS enterpise guide , I use these formats as tables on Teradata that I am checking has these formats.  ... View more