Hi Ballard,
When I am Joining on the tables we need to Extract the Schedule Date from the staging.ads_daily_techtrac_faults_open.I need your help.
Proc SQL;
Create table SROin_2 as
Select
/* Before = Faults */
Coalesce(before.closeddate,open.schedule_date) as faultdate,
before.urn,
before.Close_Date,
before.Close_Date as dayt,
before.dayt as fault_checked_date, /** 07/12/2010 - JW - Added this date in to statement **/
before.TECH_no1,
before.WORKORDER,
before.finding_desc,
before.solution_desc,
before.source_system_code,
before.franchise_Code,
before.account_no,
before.trucked_fault,
/* After = SROs */
/* PR-added COMPLETIONDATE (P0002373164)*/
after.ENTRYDATE,
after.ENTRYDATE as dayt,
after.COMPLETIONDATE,
after.workorder as SRO_WO,
after.SERVICE_CODE,
after.TECH_NO1 as A_Tech_No1
from SROs as SRO
left join
Closed_Faults as before on sro.urn=before.urn
and SRO.Opendate lt (before.dayt+1)
left join
Open as Open ON SRO.URN = OPEN.URN
and SRO.Opendate lt (OPEN.SCHEDULE_DATE+1)
where Before.URN IS NOT NULL OR OPEN.URN IS NOT NULL
order by before.dayt,urn;
QUIT;
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
MACROS :
%macro grab_faults(op_dset=,
open_closed=,
start_dat=,
end_dat=,
add_counter=,
keep_vars=,
sort_by=,
include_xicms=N,
format_xicms=N,
Include_BC=N,
BC_Only=N,
cache=YES
) / parmbuff;
%put Running grab_faults_v2_1_2;
* NS (26/8/04) - Insert a block of code to set up the fault formats for the ;
* Finding and solution codes. ;
proc format ;
value $regionw
'UD'='Scotland'
'ED'='Scotland'
'GL'='Scotland'
'SC'='Scotland'
'NE'='North East'
'NW'='North West'
'YK'='Yorkshire'
'AV'='South West'
'CW'='South West'
'SW'='South West'
'MI'='Midlands'
'MD'='Midlands'
'BG'='Birmingham'
'BM'='Birmingham'
'DE'='Eurobell South West'
'DV'='Eurobell South West'
'EL'='East London'
'SE'='East London'
'LS'='South London'
'SL'='South London'
'WL'='West London'
'CL'='North London'
'NL'='North London'
'CR'='Eurobell South East'
'KE'='Eurobell South East'
'WK'='Eurobell South East'
/* 'TV'='Thames Valley'*/
'AN'='Central Midlands'
'EM'='East Midlands'
'NN'='Northern England West'
'NO'='London North West'
'NS'='London North East'
'NT'='North East NTL'
'SN'='London South NTL'
'SO'='Southern England'
'YO'='Northern England East'
'NY'='Nynex'
'VC'='Videotron Cable Ltd'
'BL'='Bell Cable Ltd'
'GW'='Glasgow'
'LU'='Luton'
'RM'='Belfast'
'WA'='Wales'
'MK'='Milton Keynes'
'MT'='Mid Tees'
'CB'='Cambridge'
;
* import the finding table into a reporting dataset - ignore the first ;
* row as this contains the column headers. ;
%include "/sas/data/access_mi/mitdata/server_sas/includes/create_formats.sas";
proc import datafile="/sas/data/access_mi/mitdata/server_sas/csv_files/finding.csv"
dbms=csv replace
out=finding_table
;quit;
data finding_table;
length finding_code $8. finding_desc $50.;
set finding_table;
attrib finding_code label='Finding Code' ;
attrib finding_desc label='Finding Code Descriptor' ;
if length(finding_code)=1 then
finding_code='0'||finding_code;
run;
/*data finding_table;
infile "&f_table." dsd firstobs=2 termstr=crlf;
attrib finding_code label='Finding Code' length=$8;
attrib finding_desc label='Finding Code Descriptor' length=$50;
input finding_code finding_desc ;
if length(finding_code)=1 then
finding_code='0'||finding_code;
/* if ((substr(finding_code,1,3)=substr(finding_desc,1,3)) or
(substr(finding_desc,1,3)='VM ')) then
finding_desc=left(substr(finding_desc,4,46));
else if (substr(finding_desc,1,4)='CRW ') then
finding_desc=left(substr(finding_desc,5,45)); */*/
run;*/
* set up an 'Other' default of 'None' for the format ;
data finding_table;
set finding_table end=atend;
output;
if (atend) then
do;
hlo='O';
finding_code='00';
finding_desc='None';
output;
end;
run;
* sort the table and lose any duplicates ;
proc sort data=finding_table nodupkey; by finding_code; run;
* use our pre-defined macro to set up the formats for the finding code descriptor ;
%create_fmt(fmtsource=finding_table,
fmtname=findin,
fmttype=C,
startvar=finding_code,
labelvar=finding_desc );
* .. now do the same for solution codes - import the solution table into a reporting ;
* dataset - ignore the first row as this contains the column headers. ;
proc import datafile="/sas/data/access_mi/mitdata/server_sas/csv_files/solution.csv"
dbms=csv replace
out=solution_table
;quit;
data solution_table;
length solution_code $8. solution_desc $50.;
set solution_table;
attrib solution_code label='solution Code' ;
attrib solution_desc label='solution Code Descriptor' ;
if length(solution_code)=1 then
solution_code='0'||solution_code;
solution_desc=translate(solution_desc,' ','0D'x);
run;
/*
data solution_table;
infile "&s_table." dsd firstobs=2 termstr=crlf;
attrib solution_code label='Solution Code' length=$8;
attrib solution_desc label='Solution Code Descriptor' length=$50;
input solution_code solution_desc ;
if length(solution_code)=1 then
solution_code='0'||solution_code;
/* if (substr(solution_code,1,3)=substr(solution_desc,1,3)) then
solution_desc=left(substr(solution_desc,4,46));
else if (substr(solution_desc,2,1)=' ') then
solution_desc=left(substr(solution_desc,3,47)); */*/
solution_desc=translate(solution_desc,' ','0D'x); * 09/04/2009 found a Carriage Return had crept into solution_desc
on the unix version....RFC;
/*run;*/
* set up an 'Other' default of 'None' for the format ;
data solution_table;
set solution_table end=atend;
output;
if (atend) then
do;
hlo='O';
solution_code='00';
solution_desc='None';
output;
end;
run;
* sort the table and lose any duplicates ;
proc sort data=solution_table nodupkey; by solution_code; run;
* use our pre-defined macro to set up the formats for the finding code descriptor ;
%create_fmt(fmtsource=solution_table,
fmtname=soltio,
fmttype=C,
startvar=solution_code,
labelvar=solution_desc );
* (NS 6/9/04) - add an equivalent for problem codes ;
* .. now do the same for solution codes - import the solution table into a reporting ;
* dataset - ignore the first row as this contains the column headers. ;
proc import datafile="/sas/data/access_mi/mitdata/server_sas/csv_files/problem.csv"
dbms=csv replace
out=problem_table
;quit;
data problem_table;
length problem_code $8. problem_desc $50.;
set problem_table;
attrib problem_code label='problem Code' ;
attrib problem_desc label='problem Code Descriptor' ;
if length(problem_code)=1 then
problem_code='0'||problem_code;
run;
/*data problem_table;
infile "&p_table." dsd firstobs=2 termstr=crlf;
attrib problem_code label='Problem Code' length=$8;
attrib problem_desc label='Problem Code Descriptor' length=$50;
input problem_code problem_desc ;
if length(problem_code)=1 then
problem_code='0'||problem_code;
/* problem_desc=left(substr(problem_desc,length(trim(problem_code))+1,
50-(length(trim(problem_code))+1))); */*/
run;*/
* set up an 'Other' default of 'None' for the format ;
data problem_table;
set problem_table end=atend;
output;
if (atend) then
do;
hlo='O';
problem_code='00';
problem_desc='None';
output;
end;
run;
* sort the table and lose any duplicates ;
proc sort data=problem_table nodupkey; by problem_code; run;
* use our pre-defined macro to set up the formats for the finding code descriptor ;
%create_fmt(fmtsource=problem_table,
fmtname=proble,
fmttype=C,
startvar=problem_code,
labelvar=problem_desc );
%let start_time = %sysfunc(time());
%let mrc_calling_macro = &sysmacroname;
%let xicms_codes = 'VC' 'BL' 'NY' 'LU' 'WA' 'RM' 'MK' 'GW' 'CB' 'MT';
/* jwalsh - ensure that the flag is Y or set to N */
%if &include_xicms. ne Y %then %let include_xicms = N;
%if &format_xicms. ne Y %then %let format_xicms = N;
%if %symexist(srvcon) %then
%do;
%if &srvcon=0 and &SYSSCP=WIN %then %let rflag=YES;
%else %let rflag=NO;
%put &rflag;
%end;
%else %let rflag=NO;
%if &rflag=YES %then
%do;
%syslput parms=&syspbuff;
* send the values of our controlling macro variables to the remote session ;
%syslput op_dset=&op_dset.;
%syslput open_closed=&open_closed.;
%syslput start_dat=&start_dat.;
%syslput end_dat=&end_dat.;
%syslput add_counter=&add_counter.;
%syslput keep_vars=&keep_vars.;
%syslput sort_by=&sort_by.;
%syslput include_xicms=&include_xicms.;
%syslput format_xicms=&format_xicms.;
%syslput Include_BC=&Include_BC.;
%syslput BC_Only=&BC_Only.;
%syslput cache = &cache;
%syslput mrc_calling_macro=&mrc_calling_macro;
%syslput xicms_codes = &xicms_codes.;
%end;
%if &rflag=YES %then
%do;
rsubmit;
%end;
/* See if this macro has been run before using these parameters. */
%if &cache = YES %then
%do;
%inc macpath(mrc_check_cache);
%mrc_check_cache(macro_name = &mrc_calling_macro,
%if &rflag=YES %then
params = %str(&parms),;
%else
params = %str(&syspbuff),;
ignore_params = 1,
output_dset1 = &op_dset
);
%if &mrc_macro_called_before = YES %then
%goto The_End;
%end;
/*
data _NULL_;
call symput('start_dat',&start_dat);
call symput('end_dat',&end_dat);
run;
*/
/* If xICMS faults are included, then import the CSV file to format the xICMS regions.*/
/* jwalsh - updated code to only import if the format_xicms flag is Y */
/* took the import to a seperate file and used an include statement to */
/* import the data. Thus centralising the import to one location for ease. */
%if &include_xicms.=Y and &format_xicms.=Y %then
%do;
/* %include 'V:\sascode\mi caps\ap\includes\Import_xNTL_Franchise_List.sas'; */
data xICMS_Fran (drop=siteid);
*infile 'v:\SASCODE\MI CAPs\ap\Robbie_Sas_Code\Macro\CSV Files\franchise.csv' delimiter = ',' MISSOVER DSD firstobs=2;
infile '/sas/data/mitdata/server_sas/csv_files/franchise.csv' delimiter = ',' MISSOVER DSD firstobs=2;
informat FRANCHISE_CODE best32.;
informat Company $4.;
informat SiteID best32.;
informat Franchise $50.;
informat System $5.;
informat Source_System_Code $2.;
format FRANCHISE_CODE best32.;
format Company $4.;
format SiteID best32.;
format Franchise $50.;
format System $5.;
format Source_System_Code $2.;
input
FRANCHISE_CODE
Company $
SiteID
Franchise $
System $
Source_System_Code $
;
run;
%end;
* Set the main processing to run remotely because of the file sizes involved ;
/* rsubmit; */
%Macro Excludes_SSC();
%if &BC_Only.=N %then
%do;
%if &include_xicms.=N %then
%do;
and source_system_code NOT IN (&xicms_codes.)
%end;
%if &Include_BC.=N %then
%do;
and source_system_code ne 'NC'
%end;
%end;
%Else
%do;
and source_system_code = 'NC'
%end;
%mend Excludes_SSC;
* Build up a dataset of all the required faults for the specified timeframe. Apply ;
* the standard filters, add a counter if requested and then keep the variables ;
* that we need. ;
data &op_dset.;
length franchise $23
problem_desc $40
finding_desc $40
solution_desc $40
category $21
product $8
truck_type $7
wasted_truck_type $20
account_number $12
fault_number $12;
format category $21.;
* start off by selecting the datasets based upon the open/closed macro var ;
set
%if (%quote(&open_closed.) = %quote(O)) %then
%do;
staging.ads_daily_techtrac_faults_open (in=a
where=(source_system_code ne ""
%Excludes_SSC
)
)
%end;
%else %if (%quote(&open_closed.) = %quote(C)) %then
%do;
staging.ads_daily_techtrac_faults_closed (in=b rename=(category=main_category)
where=(source_system_code ne ""
%Excludes_SSC
)
)
%if &start_dat. < %sysfunc(intnx(Month,%sysfunc(today()),0)) %then
%do;
staging.ads_mthly_trac_faults_closed (in=b rename=(category=main_category)
where=(datepart(_loadtm) gt '01JAN2011'd
%Excludes_SSC
));
%end;
%end;
%else %if (%quote(&open_closed.) = %quote(CC)) %then
%do;
staging.ads_daily_techtrac_faults_closed (in=b rename=(category=main_category)
where=(source_system_code ne ""
%Excludes_SSC
)
)
%end;
%else %if (%quote(&open_closed.) = %quote(B)) %then
%do;
staging.ads_daily_techtrac_faults_closed (in=b rename=(category=main_category)
where=(source_system_code ne ""
%Excludes_SSC
)
)
staging.ads_mthly_trac_faults_closed (in=b rename=(category=main_category)
where=(datepart(_loadtm) gt '01JAN2011'd
%Excludes_SSC
))
staging.ads_daily_techtrac_faults_open (in=a
where=(source_system_code ne ""
%Excludes_SSC
)
)
%end;
%else %if (%quote(&open_closed.) = %quote(E)) %then
%do;
staging.ads_daily_techtrac_faults_closed (in=b rename=(category=main_category)
where=(source_system_code ne ""
%Excludes_SSC
)
)
%if &start_dat. < %sysfunc(intnx(Month,%sysfunc(today()),0)) %then
%do;
staging.ads_mthly_trac_faults_closed (in=b rename=(category=main_category)
where=(datepart(_loadtm) gt '01JAN2011'd
%Excludes_SSC
))
%end;
staging.ads_daily_techtrac_faults_open (in=a
where=(source_system_code ne ""
%Excludes_SSC
)
)
%end;
;
* apply the standard filters (exclusions) for status and finding code ;
* (NS 23/9/04) - V1.1 - Modify the status filter to exclude 'CN' and ;
* 'ND' codes rather than trying to keep the valid ones otherwise ;
* we`ll have problems retaining open faults. ;
* NS (6/12/04) - V1.4 - Change the exclusion logic to allow 'CN','ND' ;
* status jobs to be retained for entered faults ;
* NS (7/03/05) - V1.10 - Remove the blanket filter of all `ZM` closed ;
* faults - introduce some date-based logic further down. ;
%if ((%quote(&open_closed.) ne %quote(O)) and
(%quote(&open_closed.) ne %quote(E))) %then
%do;
%if &BC_Only.=Y %then
%do;
if (upcase(trim(status)) not in ('ND'));
%end;
%else
%do;
%if &Include_BC.=Y %then
%do;
if Source_system_code = "NC" then
do;
if (upcase(trim(status)) not in ('ND'));
end;
else
do;
if (upcase(trim(status)) not in ('CN' 'ND'));
end;
%end;
%else
%do;
if (upcase(trim(status)) not in ('CN' 'ND'));
%end;
%end;
%end;
* add a franchise definition `cos this will be widely used ;
franchise=put(source_system_code,$regionw.);
*franchise=source_system_code;
*format franchise $regionw.;
* NS (31/5/05) - allow for Eurobell test jobs ;
if (upcase(source_system_code) ne 'EU');
* NS (8/12/04) - V1.5 - also add in definitions for the main codes ;
finding_desc=put(finding_code,$findin.);
solution_desc=put(solution_code,$soltio.);
problem_desc=put(problem_code,$proble.);
*finding_desc=finding_code;
*solution_desc=solution_code;
*problem_desc=problem_code;
*format finding_desc $findin. solution_desc $soltio. problem_desc $proble.;
* NS (15/12/04) - V1.6 - Create the category variable from a new format ;
* that we`ve created - note that we need to create a new variable that ;
* is the work_order_class and finding code combined in order to use the ;
* format - drop the new var after we`ve used it. ;
format_key=trim(upcase(work_order_class))||trim(upcase(finding_code));
category=put(format_key,$catgory.);
* NS (28/106/05) - V1.13 - Use the new OOS format to set up the ysnoos variable ;
if (put(format_key,$oos_fmt.)='Y') then
ysnoos=1;
else
ysnoos=0;
* lose the format key that we no longer need ;
drop format_key;
* NS (22/12/04) - V1.7 - add in the product definition too ;
*product=put(work_order_class,$fltcls.);
product=put(work_order_class,$vtp.);
* define common account number and fault number fields ;
account_number=left(account_no);
fault_number=left(workorder);
* filter down to specific dates if required ;
* (NS 23/9/04) - V1.1 - Choose the appropriate date based upon faults requested ;
dayt_char = checked_date_time;
if ((checked_date_time=' ') or ("&open_closed."='E')) then
dayt_char=entry_date_time;
dayt=input(trim(scan(dayt_char,1,' ')),ddmmyy10.);
%if ((%quote(&start_dat.) ne %quote()) and
(%quote(&end_dat.) ne %quote())) %then
%do;
if ((dayt ge &start_dat.) and (dayt le &end_dat.));
%end;
* NS (7/03/05) - V1.10 - Control the inclusion of any `ZM` closed faults using ;
* a specific controlling date. ;
%if ((%quote(&open_closed.) ne %quote(O)) and
(%quote(&open_closed.) ne %quote(E))) %then
%do;
if (dayt lt '01MAR2005'd) then
do;
if (upcase(trim(finding_code)) ne 'ZM');
end;
%end;
* upcase the last_change_user value in case we`re doing G2Plus joins later ;
last_change_user=upcase(last_change_user);
/* New Truck Roll critera added 11MAY2007 - RG */
if office_only = 'N' and timeslot not in ('','99', ' ') then
do;
trucked_fault = 'Y';
new_trucked_fault='Y';
truck_type="SERVICE";
end;
else
do;
new_trucked_fault='N';
trucked_fault = 'N';
end;
* NS (6/4/5) - V1.11 - Add in the wasted truck logic ;
wasted_truck='N';
wasted_truck_type='N/A';
if (trucked_fault='Y') then
do;
if (upcase(finding_code) in ( 'AK' 'FX' /* 'FY' 'FZ'*/ 'AO' 'BR' 'AG' 'OJ')) then
do;
wasted_truck='Y';
select (upcase(finding_code));
when ('AK') wasted_truck_type='CPE';
when ('FX') wasted_truck_type='Right When Tested';
/* when ('FY') wasted_truck_type='No Access';*/
/* when ('FZ') wasted_truck_type='No Access';*/
when ('AO') wasted_truck_type='Data Fill';
when ('OJ') wasted_truck_type='Data Fill';
when ('AO') wasted_truck_type='Data Fill';
when ('BR') wasted_truck_type='Channels/Packages';
when ('AG') wasted_truck_type='Channels/Packages';
end;
end;
else if (upcase(solution_code) in ('28' '31' '35' '84' '17' '32' '66' '80' '21' '72')) then
do;
wasted_truck='Y';
select (upcase(solution_code));
when ('28') wasted_truck_type='Mains Reset';
when ('31') wasted_truck_type='Hit Sent';
when ('35') wasted_truck_type='Mains Reset';
when ('84') wasted_truck_type='Customer Education';
when ('17') wasted_truck_type='Channels/Packages';
when ('32') wasted_truck_type='Channels/Packages';
when ('66') wasted_truck_type='Channels/Packages';
when ('80') wasted_truck_type='Channels/Packages';
when ('21') wasted_truck_type='Cancelled';
when ('72') wasted_truck_type='Cancelled';
end;
end;
end;
* set up a default counter flag ;
counter=1;
* RG - 10/07/08 ;
* Added Where Also to remove Off Net Code. ;
* where also SOURCE_SYSTEM_CODE ne "NC";
* if keep vars have been specified, issue the keep command - keep the counter ;
* regardless if we`ve been asked to generate one. ;
%if (%quote(&keep_vars.) ne %quote()) %then
%do;
keep &keep_vars. urn workorder FRANCHISE_CODE
%if (%quote(&add_counter.) = %quote(Y)) %then
%do;
counter
%end;
;
%end;
run;
/* If xICMS Faults are included then split the dataset into xTW and xNTL then merge the
xNTL data with the CSV sheet to get the Source_System_Code and the Franchise. */
%if &include_xicms.=Y %then
%do;
%if &format_xicms.=Y %then
%do;
Data xTW xNTL;
Set &op_dset.;
If Source_System_Code in (&xicms_codes.) Then
do;
Output xNTL;
end;
else
do;
Output xTW;
end;
run;
Data xNTL;
Set xNTL(rename=( Franchise=Franchise_Old
Source_System_Code=Source_system_code_old
URN=URN_OLD));
run;
Proc Sort Data=xICMS_Fran; By FRANCHISE_CODE;run;
Proc Sort Data=xNTL; By FRANCHISE_CODE;run;
Data Merged_xNTL;
Merge xNTL(in=n)
xICMS_Fran(in=f);
By FRANCHISE_CODE;
if n;
run;
Data Merged_xNTL;
Set Merged_xNTL;
URN=trim(left(source_System_Code))||Trim(Left(substr(URN_OLD,3)));
Drop SiteID;
If source_system_code = "" then
do;
Source_system_code = "UK";
Franchise = "Unknown";
end;
run;
Data xTW;
Set xTW;
Franchise_Old=Franchise;
Source_system_code_old=Source_system_code;
URN_OLD=URN;
Company = "xTW";
System = "ICOMS";
run;
Data &op_dset.;
Length Company $4;
set xTW
Merged_xNTL;
format franchisecode $7.;
franchisecode = compress(franchise_code);
run;
%end;
%end;
* make sure that we lose any duplicates based upon the urn and workorder number ;
proc sort data=&op_dset. nodupkey; by urn workorder; run;
* if requested, sort the output dataset into the specified order ;
%if (%quote(&sort_by.) ne %quote()) %then
%do;
proc sort data=&op_dset.; by &sort_by.; run;
%end;
* close the remote processing ;
/* endrsubmit; */
/* Could only have got this far if this macro has not been
run with these parameters before. Therefore, save this data. */
%if &cache = YES %then
%do;
%inc macpath(mrc_insert_into_cache);
%mrc_insert_into_cache(macro_name = &mrc_calling_macro,
%if &rflag=YES %then
params = %str(&parms),;
%else
params = %str(&syspbuff),;
ignore_params = 1,
output_dset1 = &op_dset
);
%end;
/* Macro jumps here if it has already been run with these parameters. */
%The_End:
%if &rflag=YES %then
%do;
endrsubmit;
%end;
%let finish_time = %sysfunc(time());
data _null_;
start_time = &start_time;
finish_time = &finish_time;
runtime = finish_time - start_time;
put "==============================";
put " &sysmacroname finished";
put " Started at " start_time time.;
put " Finished at " finish_time time.;
put " R U N T I M E " runtime time.;
put "==============================";
run;
%mend grab_faults;
/*
* sample usage ;
%let start_dat=%eval(%sysfunc(today())-1);
%let end_dat=%eval(%sysfunc(today())-1);*/
/*%grab_faults(op_dset=Raw_Data,
open_closed=C,
start_dat='01mar2016'd,
end_dat='10mar2016'd,
add_counter=Y,
keep_vars=,
sort_by=,
include_xicms=Y,
format_xicms=N
);*/
