Rename an Observation

[DME790]

Morning,

I need to change a date value form an obsveration to text:

Currently the output is:

WEEK_END_DT = 2016-11-27

I need the output to say

Week_END_DT = Weekly_Total.

[ballardw]

Please provide some context as to exactly what you are attempting such as structure of your dataset and what the final overall result would look like, example data is often very helpful as well as code you are currently using.. A SAS data type may only be numeric or character so what you are specifically requesting cannot be done within a data set.

However it appears that you are placing a summary record inside data, which has potentially many problems, but if the sole purpose is to print a report then one of the report procedures is very likely able to do what you want.

[DME790]

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

[Reeza]

Saw this earlier, and assumes that ID is numeric. 

Total=Sum(of _numeric_) - ID;

[BrunoMueller · Accepted solution]

hi

It might be worthwhile to have a look at Proc TABULATE, it can aggregate the data as well as do the layout of a report.

Find below a simplified example using generated data based on your data (not all the columns).

data have;
  do cluster = 1 to 10;
    do i = 1 to Ceil(rand("uniform")*2000);
      event_dt = floor(rand("uniform")* 6 ) + intnx("week.2", today(), 0);
      CALLS_ANSWERED_CNT = 1;
      output;
    end;
  end;

  format event_dt WEEKDATE30.;
run;

proc tabulate data=have format=comma12.;
  class event_dt cluster;
  var calls_answered_cnt;

  table 
    cluster=" "
    , event_dt=" "*calls_answered_cnt=" " all="Total"*calls_answered_cnt=" "
    / box="Cluster"
  ;
  keylabel sum= " ";
run;

Bruno

[DME790]

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

[ballardw]

Proc tabulate sorts rows by the values of the class variables from left to right as they appear in the table statement when nested,

But you may need to decide are you grouping by cluster or MBL. Your example desired table did not show anything related to MBL. So you may want to post an example of what you are thinking of.

[DME790]

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

[Reeza]

I think it's just an ALL with the CLUSTER.

	table cluster=" " ALL = 'Column Totals', .... ;

[DME790]

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

[Reeza]

That's not based on the code you posted above, is it?

You can add it in as a column ahead of the CLUSTER Variable.

Variable*Cluster ALL = 

To control the order of the variable look into the ORDER= options on the PROC TABULATE statement.

[DME790]

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;

[DME790]

Thanks heaps for all your help Reeza - I'm slowly getting the hang of SAS.

I now have the table showing the way I need it - just had to to think about the layout I needed. I ended up with this as my code:

proc tabulate data=Report.SSW_Answered format=comma12.;
 class event_dt MBL cluster;
 var calls_answered_cnt;
 

 table
  MBL*cluster=" " All = "Daily Totals"
  , event_dt=" "*calls_answered_cnt=" " All="Weekly Total"*calls_answered_cnt=" "
  / box="Cluster"
 ;
  keylabel sum= " ";
  Title "Answered Calls";
run;