Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for
Options
- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
Quartz | Level 8
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 02-17-2020 03:44 PM
(2399 views)
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.
1 ACCEPTED SOLUTION
Accepted Solutions
Fluorite | Level 6
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
5 REPLIES 5
Garnet | Level 18
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 ?!
Super User
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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.
Quartz | Level 8
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Super User
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
Fluorite | Level 6
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Upcoming Events
- PharmaSUG 2024 | 19-May-2024
- SAS Halifax Region User Group (SHRUG) Meeting | 23-May-2024
- What’s New in SAS Optimization? | 23-May-2024
- Montreal SAS User Group (MONSUG) Meeting | 29-May-2024
- Club des utilisateurs SAS de Québec | 30-May-2024
- Optimizing Logistics Operations Using Customized Solutions | 04-Jun-2024
- Toronto SAS Users' Community Luncheon | 04-Jun-2024
Mastering the WHERE Clause in PROC SQL
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.
Discussion stats
-
5 replies
-
02-17-2020 03:44 PM
-
2400 views
-
0 likes
-
4 in conversation
-
