Sorry Reeza - I added BY MBL; Which gives me the two tables. Not sure where to add the Variable*Cluster All = This is the updat6ed code: proc tabulate data=Report.SSW_Answered format=comma12.; class event_dt cluster; var calls_answered_cnt; By MBL; table cluster=" " All = "Daily Totals" , event_dt=" "*calls_answered_cnt=" " All="Weekly Total"*calls_answered_cnt=" " / box="Cluster" ; keylabel sum= " "; Title "Answered Calls"; run; ... View more

Thanks Reeza - did the trick.   Is there any way that I can sort by MBL and not get two tables? Undwer MBL I have MBL or OTH and there are instances of a Cluster being in both (eg DSC). I would like the output to be in one table and an overall total at the bottom.   Required Ouput   Cluster Monday, November 28, 2016 Tuesday, November 29, 2016 Wednesday, November 30, 2016 Weekly Total DSC 7,530 6,457 6,594 20,581 EMS 7,969 7,599 7,437 23,005 FAP 21,769 23,002 20,057 64,828 RET 5,217 4,882 4,252 14,351 PST 3,360 3,711 3,131 10,202 YAS 4,724 4,422 5,430 14,576 ABS 618 591 538 1,747 CMC 3,719 3,119 2,884 9,722 CCP 18 43 36 97 DFC 1,034 979 796 2,809 DHS 5,184 4,612 4,453 14,249 DSC 117 119 138 374 DA 135 129 96 360 EARN 7,461 6,879 11,252 25,592 EMG 5 5 1 11 Daily Totals 68,860 66,549 67,095 202,504       Current Output   Answered Calls           MBL=MBL           Cluster Monday, November 28, 2016 Tuesday, November 29, 2016 Wednesday, November 30, 2016 Weekly Total DSC 7,530 6,457 6,594 20,581 EMS 7,969 7,599 7,437 23,005 FAP 21,769 23,002 20,057 64,828 RET 5,217 4,882 4,252 14,351 PST 3,360 3,711 3,131 10,202 YAS 4,724 4,422 5,430 14,576 Daily Totals 50,569 50,073 46,901 147,543                               Answered Calls           MBL=Oth           Cluster Monday, November 28, 2016 Tuesday, November 29, 2016 Wednesday, November 30, 2016 Weekly Total ABS 618 591 538 1,747 CMC 3,719 3,119 2,884 9,722 CCP 18 43 36 97 DFC 1,034 979 796 2,809 DHS 5,184 4,612 4,453 14,249 DSC 117 119 138 374 DA 135 129 96 360 EARN 7,461 6,879 11,252 25,592 EMG 5 5 1 11 ... View more

Hi Bruno,   Thanks for the help,   The code gives me a 'row' total - is there a way that I can get a column total as well?   I tried adding cluster=" "*calls_answered_cnt=" " All="Weekly Total"*calls_answered_cnt=" " but returned errors.     Data Report.SSW_Answered; Set Cartel.preagent_telephony; Keep EVENT_DT CLUSTER CALLS_ANSWERED_CNT MBL ; Where week_end_dt = '04Dec2016.'d and CALL_TYPE = 'External' and PROGRAMME_NM IN ("1.1 SSW"); IF Cluster_ID in ('1' , '6', '9', '13', '18', '27') and Queue_Type IN ('Smart Centres - Channel Operations', 'Smart Centres - Participation Solutions') THEN MBL = 'MBL'; ELSE MBL = 'Other'; format EVENT_DT WEEKDATE30.; Run; proc tabulate data=Report.SSW_Answered format=comma12.; class event_dt cluster; var calls_answered_cnt; table cluster=" " , event_dt=" "*calls_answered_cnt=" " All="Weekly Total"*calls_answered_cnt=" " / box="Cluster" ; keylabel sum= " "; Title "Answered Calls"; run;   Cheers ... View more

Thanks Bruno,   This helped a lot - just one more question - I need to sort based on the MBL column.   Can you help with this?   Cheers ... View more

Hi Ballardw,   I want to get a total column at the end of the table.   data example attached.   WEEK_END_DT EVENT_DT EVENT_TM CLUSTER_ID CLUSTER CALLS_ANSWERED_CNT MBL 27/11/2016 Monday, November 21, 2016 9:00:00 21 Reception Cluster 0 Oth 27/11/2016 Monday, November 21, 2016 9:00:00 22 Other 1 Oth 27/11/2016 Monday, November 21, 2016 10:30:00 23 Other 1 Oth 27/11/2016 Monday, November 21, 2016 11:00:00 24 DSC 0 MBL 27/11/2016 Monday, November 21, 2016 12:15:00 25 EMS 0 MBL 27/11/2016 Monday, November 21, 2016 13:30:00 26 FAP 0 MBL 27/11/2016 Monday, November 21, 2016 14:00:00 27 YAS 0 MBL 27/11/2016 Monday, November 21, 2016 15:00:00 28 RET 0 MBL 27/11/2016 Monday, November 21, 2016 15:15:00 29 Other 0 Oth 27/11/2016 Monday, November 21, 2016 14:15:00 30 Feedback & Complaints 0 Oth         Data Report.SSW_Answered; Set Cartel.preagent_telephony; */ Keep EVENT_DT CLUSTER CALLS_ANSWERED_CNT MBL ; Where week_end_dt = '27Nov2016.'d and CALL_TYPE = 'External' and PROGRAMME_NM IN ("SSW"); IF Cluster_ID in ('1' , '6', '9', '13', '18', '27') and Queue_Type IN (CHOPs', 'PST') THEN MBL = 'MBL'; ELSE MBL = 'Other'; format EVENT_DT WEEKDATE30.; Run; Proc sort data=Report.SSW_Answered; By EVENT_DT CLUSTER MBL; RUN; Proc Summary data=Report.SSW_Answered SUM NOPRINT; BY EVENT_DT CLUSTER MBL; VAR CALLS_ANSWERED_CNT; Output Out=Report.SSW_Answered_Sum (DROP=_TYPE_ _FREQ_) SUM=; RUN; Proc Sort data=report.SSW_ANSWERED_SUM; BY Cluster MBL; RUN; Proc Transpose data=report.SSW_ANSWERED_SUM out=report.SSW_ANSWERED_Trans (DROP= _NAME_ _LABEL_); BY Cluster MBL; ID EVENT_DT; VAR CALLS_ANSWERED_CNT; RUN; Proc Sort Data=report.SSW_ANSWERED_Trans; By MBL; RUN; data Work.SSW_ANSWERED_Final; Drop MBL; Set Report.SSW_ANSWERED_Trans; RUN; Proc Print data=Work.SSW_Answered_Final NOOBS; Run; this gives the following (the column in Blue is what I want to add to the report.               Hope this makes sense   Cheers ... View more

Thanks Kalind - works a treat ... View more

Thanks Patrick,     Right there in front of my eyes.   Works now.   Cheers ... View more

Thanks Reeza,   Added proc sql; create table PST_Unique_SQL as select EVENT_DT, EMPLOYMENT_TYPE, count(distinct(Logon_ID)) as count_ID from Report.PST_Unique group by EVENT_DT, EMPLOYMENT_TYPE; quit; and now get the Transpose to work.     Now how do I create a daily 'Total' column in the final Proc Print?  ... View more

Thanks Reeza - full log   1                                                          The SAS System                           10:04 Wednesday, August 24, 2016   1          ;*';*";*/;quit;run; 2          OPTIONS PAGENO=MIN; 3          %LET _CLIENTTASKLABEL='Program'; 4          %LET _CLIENTPROJECTPATH='R:\NAT\SMARTCENT\SMCTRCHOP\GCFTELE\Telephony Performance\SAS EG Files\PST_ASA_Data.egp'; 5          %LET _CLIENTPROJECTNAME='PST_ASA_Data.egp'; 6          %LET _SASPROGRAMFILE=; 7          8          ODS _ALL_ CLOSE; 9          OPTIONS DEV=ACTIVEX; 10         GOPTIONS XPIXELS=0 YPIXELS=0; 11         FILENAME EGSR TEMP; 12         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 13             STYLE=Ocean 14             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/Ocean.css") 15             NOGTITLE 16             NOGFOOTNOTE 17             GPATH=&sasworklocation 18             ENCODING=UTF8 19             options(rolap="on") 20         ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 21         22         GOPTIONS ACCESSIBLE; 23         LIBNAME data "/sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/Telephony" access=readonly; NOTE: Libref DATA refers to the same physical library as CARTEL. NOTE: Libref DATA was successfully assigned as follows:       Engine:        V9       Physical Name: /sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/Telephony 24         LIBNAME REPORT "/sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/misc/FAP_OA_Daily"; NOTE: Libref REPORT was successfully assigned as follows:       Engine:        V9       Physical Name: /sasdata/clk/dev/SERVPERFCO/SDPAGENUSER/BIS/Data/SASLib/0.Blended/CAR/misc/FAP_OA_Daily 25         Run; 26           27         Data Report.PST_Unique; 28          Set CARTEL.Employee_Telephony; 29          Keep  EVENT_DT EMPLOYMENT_TYPE Logon_ID; 30          Where EVENT_DT >= '01Jul2016'd 31           and CENTRE_TYPE = 'Smart Centres - Participation Solutions' 32           and CLUSTER = 'PST - Customer'; 33          format EVENT_DT WeekDate30.; 34         Run;   NOTE: There were 9295 observations read from the data set CARTEL.EMPLOYEE_TELEPHONY.       WHERE (EVENT_DT>='01JUL2016'D) and (CENTRE_TYPE='Smart Centres - Participation Solutions') and (CLUSTER='PST - Customer'); NOTE: The data set REPORT.PST_UNIQUE has 9295 observations and 3 variables. NOTE: DATA statement used (Total process time):       real time           1.90 seconds       cpu time            1.18 seconds         35         36         proc sql; 37          select EVENT_DT, EMPLOYMENT_TYPE, count(distinct(Logon_ID)) as count_ID 38           from Report.PST_Unique 39            group by EVENT_DT, EMPLOYMENT_TYPE; 2                                                          The SAS System                           10:04 Wednesday, August 24, 2016   40         quit; NOTE: PROCEDURE SQL used (Total process time):       real time           0.05 seconds       cpu time            0.06 seconds         41         42         Proc Transpose data=Report.PST_Unique out=work.PST_UNIQUE_FINAL (DROP=_NAME_); 43          By EVENT_DT; 44          ID EMPLOYMENT_TYPE; 45          VAR Count_ID; ERROR: Variable COUNT_ID not found. 46         run;   NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.PST_UNIQUE_FINAL may be incomplete.  When this step was stopped there were 0 observations and 0          variables. WARNING: Data set WORK.PST_UNIQUE_FINAL was not replaced because this step was stopped. NOTE: PROCEDURE TRANSPOSE used (Total process time):       real time           0.02 seconds       cpu time            0.00 seconds       47           48         Proc Print data=Report.PST_Unique NOOBS; 49         run;   NOTE: There were 9295 observations read from the data set REPORT.PST_UNIQUE. NOTE: PROCEDURE PRINT used (Total process time):       real time           3.97 seconds       cpu time            4.04 seconds         50         51         Proc SQL; 52          Drop Table Work.PST_UNIQUE_SUM; WARNING: File WORK.PST_UNIQUE_SUM.DATA does not exist. WARNING: Table WORK.PST_UNIQUE_SUM has not been dropped. 53          Drop table Report.PST_Unique; NOTE: Table REPORT.PST_UNIQUE has been dropped. 54         Run; NOTE: PROC SQL statements are executed immediately; The RUN statement has no effect. 55         56         GOPTIONS NOACCESSIBLE; 57         %LET _CLIENTTASKLABEL=; 58         %LET _CLIENTPROJECTPATH=; 59         %LET _CLIENTPROJECTNAME=; 60         %LET _SASPROGRAMFILE=; 61         62         ;*';*";*/;quit; NOTE: PROCEDURE SQL used (Total process time):       real time           0.00 seconds       cpu time            0.00 seconds       62       !                run;   63         ODS _ALL_ CLOSE; 3                                                          The SAS System                           10:04 Wednesday, August 24, 2016   64         65         66         QUIT; RUN; 67         ... View more

Hi RahulG,   The output after the SQL is right   Event Date Employment Type count_ID Friday, July 1, 2016 Ongoing - Full time 88 Friday, July 1, 2016 Ongoing - Part time 56 Monday, July 4, 2016 Ongoing - Full time 80 Monday, July 4, 2016 Ongoing - Part time 70 Tuesday, July 5, 2016 Ongoing - Full time 80 Tuesday, July 5, 2016 Ongoing - Part time 62 Wednesday, July 6, 2016 Ongoing - Full time 81 Wednesday, July 6, 2016 Ongoing - Part time 47 Thursday, July 7, 2016 Ongoing - Full time 84 Thursday, July 7, 2016 Ongoing - Part time 57   When the Proc Transpose runs that is when I get the error 😞 ... View more

Thanks RahulG    I have changed the name and still comes back with ERROR: Variable COUNT_ID not found.   Didn't realise that Count was a keyword so will keep away from using it as a name from now on.   Cheers ... View more