1 The SAS System 09:34 Monday, June 26, 2017 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='Program'; 4 %LET _CLIENTPROJECTPATH=''; 5 %LET _CLIENTPROJECTNAME=''; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 ODS LISTING GPATH=&sasworklocation; 12 FILENAME EGHTML TEMP; 13 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=HtmlBlue 13 ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") 13 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v93/sasgraph.exe#version=9,3") NOGTITLE NOGFOOTNOTE 13 ! GPATH=&sasworklocation; NOTE: Writing HTML(EGHTML) Body file: EGHTML 14 FILENAME EGPDF TEMP; 15 ODS PDF(ID=EGPDF) FILE=EGPDF STYLE=printer SAS; WARNING: Unsupported device 'ACTIVEX' for PDF(EGPDF) destination. Using device 'ACTXIMG'. NOTE: Writing ODS PDF(EGPDF) output to DISK destination "EGPDF", printer "PDF". 16 FILENAME EGSR TEMP; 17 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR STYLE=HtmlBlue 17 ! STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/x86/SASEnterpriseGuide/5.1/Styles/HtmlBlue.css") NOGTITLE NOGFOOTNOTE 17 ! GPATH=&sasworklocation ENCODING=UTF8 options(rolap="on"); NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 18 19 GOPTIONS ACCESSIBLE; 20 LIBNAME CPS ORACLE USER = bb83666 PASSWORD = XXXXXXXX PATH='cpsprd_ro' SCHEMA = 'CPS_OWNER'; NOTE: Libref CPS was successfully assigned as follows: Engine: ORACLE Physical Name: cpsprd_ro 21 LIBNAME MIS ORACLE USER = bb83666 PASSWORD = XXXXXXXX PATH='cpsprd_ro' SCHEMA = 'MIS_OWNER'; NOTE: Libref MIS was successfully assigned as follows: Engine: ORACLE Physical Name: cpsprd_ro 22 23 24 proc sql; 25 connect to oracle (user=bb83666 password=XXXXXXXX path='cpsprd_ro' readbuff=100000); 26 create table want as select * 27 from connection to oracle 28 ( 29 30 31 select 32 33 base.dt, base.usr_id, base.id,base.USER_NAME,base.name, 34 35 COALESCE(GROSS_HOURS,0) as GROSS_HOURS, 36 37 COALESCE(FLEX_ADJ,0)as FLEX_ADJ, 38 39 COALESCE(OVERTIME,0) as OVERTIME, 40 41 COALESCE(BORROWED,0) as BORROWED, 2 The SAS System 09:34 Monday, June 26, 2017 42 43 COALESCE(ONLOAN,0) as ONLOAN, 44 45 COALESCE(HOLYDAYS,0) as HOLIDAYS, 46 47 COALESCE(SICKNESS,0) as SICKNESS, 48 49 COALESCE(SPECIAL_JOB,0) as SPECIAL_JOB, 50 51 COALESCE(MGNT_ADJ,0) as MGNT_ADJ, 52 53 COALESCE(TRAINING,0) as TRAINING, 54 55 COALESCE(HOURS_ADJUSTMENT,0)as HOURS_ADJ, 56 57 COALESCE(COMPLETED_HOURS,0)as COMPLETED_HOURS, 58 59 COALESCE(COMPLETED_FOLDERS,0)as COMPLETED_FOLDERS, 60 61 base.BRL_ID 62 63 64 65 66 67 FROM( 68 69 SELECT dt, t2.UGP_ID,t2.USR_ID,t2.GRP_ID,t2.NAME,t2.BRL_ID, t2.ID, t2.user_name 70 71 FROM ( 72 73 select TRUNC(SYSDATE,'y')-1+ROWNUM dt 74 75 from all_objects 76 77 where ROWNUM <= ADD_MONTHS(TRUNC(SYSDATE,'y'),12) - TRUNC(SYSDATE,'y') 78 79 )dtz 80 81 CROSS JOIN 82 83 ( 84 85 select UG.UGP_ID,UG.USR_ID,UG.GRP_ID,CG.NAME,CG.BRL_ID,USR.ID, USR.NAME as USER_NAME 86 87 from CPS.CPS_USER_GROUP UG 88 89 left join CPS.CPS_GROUP CG 90 91 on UG.GRP_ID = CG.GRP_ID 92 93 left join CPS.CPS_USER USR 94 95 on UG.USR_ID = USR.USR_ID 96 97 98 99 where CG.BRL_ID in (1,16) and CG.NAME in ('DOCUMENTATION','DOC MANAGEMENT','DISBURSALS TEAM', 3 The SAS System 09:34 Monday, June 26, 2017 100 101 'SUPPORT SERVICES TEAM','VERIFICATIONS','HOUSING LOANS VALIDATIONS','COMPLEX - SECURITIES','DOC 101 ! PREP','SETTLEMENTS', 102 103 'NATIONAL EXAMINATIONS','DISBURSALS','FBI','LEG-ENDS','PHONE TEAM','RECORDS MAN','THE UNTOUCHABLES', 104 105 'MAVERICKS','DREAM TEAM','NINJA TURTLES','TEAM ELITE','TOP GUNS','TRANSFORMERS','CMS NINJA TURTLES','DPS 105 ! DISCHARGE','DPS EXAMINATIONS')or CG.NAME like '%HLT%' 106 107 or CG.NAME like '%LEVEL 23%' 108 109 )t2 110 111 )base 112 113 LEFT JOIN MIS.MIS_USER_SUMMARY t1 114 115 on t1.USR_ID = base.USR_ID and t1.action_date = base.dt and t1.brl_id = base.brl_id 116 117 118 119 LEFT JOIN 120 121 ( 122 123 select adj.dt, adj.usr_id, 124 125 sum(adj.flex_adj)as FLEX_ADJ, 126 127 sum(adj.overtime)as OVERTIME, 128 129 sum(adj.BORROWED) as BORROWED, 130 131 sum(adj.ONLOAN) as ONLOAN, 132 133 sum(adj.HOLIDAYS) as HOLYDAYS, 134 135 sum(adj.SICKNESS) as SICKNESS, 136 137 sum(adj.SPECIAL_JOB) as SPECIAL_JOB, 138 139 sum(adj.MGNT_ADJ) as MGNT_ADJ, 140 141 sum(adj.TRAINING) as TRAINING 142 143 FROM( 144 145 select 146 147 t4.DAY as dt, 148 149 t4.USR_ID, 150 151 case when hat_id=1 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 151 ! FLEX_ADJ, 152 153 case when hat_id=2 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 153 ! OVERTIME, 4 The SAS System 09:34 Monday, June 26, 2017 154 155 case when hat_id=3 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 155 ! BORROWED, 156 157 case when hat_id=7 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 157 ! ONLOAN, 158 159 case when hat_id=4 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 159 ! HOLIDAYS, 160 161 case when hat_id=6 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 161 ! SICKNESS, 162 163 case when hat_id=8 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 163 ! SPECIAL_JOB, 164 165 case when hat_id=9 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 165 ! MGNT_ADJ, 166 167 case when hat_id=5 then to_number(substr(t4.adjustment,1,2))+(to_number(substr(t4.adjustment,4,2))/60) else 0 end as 167 ! TRAINING 168 169 from MIS.MIS_USER_WORK_ADJUSTMENTS t4 170 171 where t4.DAY>='25-JUL-2016' 172 173 ) adj 174 175 GROUP BY adj.dt, adj.usr_id 176 177 )adj1 178 179 on adj1.USR_ID = base.USR_ID and adj1.dt = base.dt 180 181 where base.dt >='19-JUN-2017' and base.dt