Desktop productivity for business analysts and programmers

Proc Report and suming rows and columns - SAS EG V7.11

Reply
Contributor
Posts: 48

Proc Report and suming rows and columns - SAS EG V7.11

[ Edited ]

HI I have cretaed a data that gives me the following output

 

CLUSTERMBL     Monday, November 21, 2016   Tuesday, November 22, 2016Wednesday, November 23, 2016
DSCMBL                                                     7,628                                                       7,239                                                             6,909
EMSMBL                                                     8,147                                                       7,625                                                             7,439
FAPMBL                                                   20,503                                                     23,668                                                           21,454
OAMBL                                                     5,336                                                       4,961                                                             4,505
PSTMBL                                                     3,650                                                       3,250                                                             3,110
YASMBL                                                     5,097                                                       4,738                                                             3,989
ABSOth                                                         684                                                           624                                                                 588

 

What I would like to see as the output is the full week as per the following;

 

CLUSTERMBL     Monday, November 21, 2016   Tuesday, November 22, 2016Wednesday, November 23, 2016Thursday, November 24, 2016Friday, November 25, 2016Saturday, November 26, 2016Sunday, November 27, 2016Week Total
DSCMBL                                                     7,628                                                       7,239                                                             6,909               21,776
EMSMBL                                                     8,147                                                       7,625                                                             7,439               23,211
FAPMBL                                                   20,503                                                     23,668                                                           21,454               65,625
OAMBL                                                     5,336                                                       4,961                                                             4,505               14,802
PSTMBL                                                     3,650                                                       3,250                                                             3,110               10,010
YASMBL                                                     5,097                                                       4,738                                                             3,989               13,824
ABSOth                                                         684                                                           624                                                                 588                 1,896
                                                     51,045                                                     52,105                                                           47,994             151,144

 

I need help in adding the remainder of the week - even though there is no data for those days yet and to sum both the column and the row as a daily total and a weekly total like

 

CLUSTERMBL     Monday, November 21, 2016   Tuesday, November 22, 2016Wednesday, November 23, 2016Thursday, November 24, 2016Friday, November 25, 2016Saturday, November 26, 2016Sunday, November 27, 2016Week Total
DSCMBL                                                     7,628                                                       7,239                                                             6,909               21,776
EMSMBL                                                     8,147                                                       7,625                                                             7,439               23,211
FAPMBL                                                   20,503                                                     23,668                                                           21,454               65,625
OAMBL                                                     5,336                                                       4,961                                                             4,505               14,802
PSTMBL                                                     3,650                                                       3,250                                                             3,110               10,010
YASMBL                                                     5,097                                                       4,738                                                             3,989               13,824
ABSOth                                                         684                                                           624                                                                 588                 1,896
Daily Total                                                    51,045                                                     52,105                                                           47,994             151,144

 

 

I can't get the Proc Report to work  and I think it is due to the data being transposed.

 

Any help appreciated.

 

Cheers

 

Data Report.SSW_Answered;
	Set Cartel.preagent_telephony;
	Keep EVENT_DT CLUSTER CALLS_ANSWERED_CNT MBL Weekday
	;
	Where week_end_dt = '27Nov2016.'d
		and CALL_TYPE = 'External'
		and PROGRAMME_NM IN ("1.1 Social Security & Welfare");

	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.;
	WeekDay = EVENT_DT;
	format WeekDay weekday2.;
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 Report Data=work.SSW_ANSWERED_Trans style(report)=[BORDERCOLOR=BLACK BACKGROUND=#FFFFFF]
	STYLE(HEADER)={BACKGROUND=#008080 COLOR=WHITE FONTWEIGHT=BOLD BORDERCOLOR=BLACK};
	Columns Cluster MBL  CALLS_ANSWERED_CNT;
	Define Cluster / DISPLAY 'Cluster' format=weekdate30.;
	Define MBL / NOPRINT;
	Define CALLS_ANSWERED_CNT / DISPLAY 'Calls Answered' analysis sum;
		rbreak after / summarize;

	title1 color=Black font=Arial BOLD "Answered calls";
RUN;

 

 

 

 

 

Ask a Question
Discussion stats
  • 0 replies
  • 254 views
  • 0 likes
  • 1 in conversation