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