Desktop productivity for business analysts and programmers

Rename an Observation

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

Rename an Observation

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.

 

 


Accepted Solutions
Solution
‎11-29-2016 04:53 PM
SAS Super FREQ
Posts: 706

Re: Rename an Observation

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

View solution in original post


All Replies
Super User
Posts: 11,134

Re: Rename an Observation

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.

 

 

Contributor
Posts: 70

Re: Rename an Observation

[ Edited ]

Hi Ballardw,

 

I want to get a total column at the end of the table.

 

data example attached.

 

WEEK_END_DTEVENT_DTEVENT_TMCLUSTER_IDCLUSTERCALLS_ANSWERED_CNTMBL
27/11/2016Monday, November 21, 20169:00:0021Reception Cluster0Oth
27/11/2016Monday, November 21, 20169:00:0022Other1Oth
27/11/2016Monday, November 21, 201610:30:0023Other1Oth
27/11/2016Monday, November 21, 201611:00:0024DSC0MBL
27/11/2016Monday, November 21, 201612:15:0025EMS0MBL
27/11/2016Monday, November 21, 201613:30:0026FAP0MBL
27/11/2016Monday, November 21, 201614:00:0027YAS0MBL
27/11/2016Monday, November 21, 201615:00:0028RET0MBL
27/11/2016Monday, November 21, 201615:15:0029Other0Oth
27/11/2016Monday, November 21, 201614:15:0030Feedback & Complaints0Oth

 

 

 

 

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.

 

 

Report.JPG

 

 

 

 

 

Hope this makes sense

 

Cheers

Super User
Posts: 19,157

Re: Rename an Observation

Saw this earlier, and assumes that ID is numeric. 

 

Total=Sum(of _numeric_) - ID;

Solution
‎11-29-2016 04:53 PM
SAS Super FREQ
Posts: 706

Re: Rename an Observation

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

Contributor
Posts: 70

Re: Rename an Observation

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

Super User
Posts: 11,134

Re: Rename an Observation

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.

Contributor
Posts: 70

Re: Rename an Observation

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

Super User
Posts: 19,157

Re: Rename an Observation

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

 

	table cluster=" " ALL = 'Column Totals', .... ;
Contributor
Posts: 70

Re: Rename an Observation

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

 

ClusterMonday, November 28, 2016Tuesday, November 29, 2016Wednesday, November 30, 2016Weekly Total
DSC7,5306,4576,59420,581
EMS7,9697,5997,43723,005
FAP21,76923,00220,05764,828
RET5,2174,8824,25214,351
PST3,3603,7113,13110,202
YAS4,7244,4225,43014,576
ABS6185915381,747
CMC3,7193,1192,8849,722
CCP18433697
DFC1,0349797962,809
DHS5,1844,6124,45314,249
DSC117119138374
DA13512996360
EARN7,4616,87911,25225,592
EMG55111
Daily Totals68,86066,54967,095202,504

 

 

 

Current Output

 

Answered Calls
     
MBL=MBL
     
ClusterMonday, November 28, 2016Tuesday, November 29, 2016Wednesday, November 30, 2016Weekly Total
DSC7,5306,4576,59420,581
EMS7,9697,5997,43723,005
FAP21,76923,00220,05764,828
RET5,2174,8824,25214,351
PST3,3603,7113,13110,202
YAS4,7244,4225,43014,576
Daily Totals50,56950,07346,901147,543
     
     
     
Answered Calls
     
MBL=Oth
     
ClusterMonday, November 28, 2016Tuesday, November 29, 2016Wednesday, November 30, 2016Weekly Total
ABS6185915381,747
CMC3,7193,1192,8849,722
CCP18433697
DFC1,0349797962,809
DHS5,1844,6124,45314,249
DSC117119138374
DA13512996360
EARN7,4616,87911,25225,592
EMG55111
Super User
Posts: 19,157

Re: Rename an Observation

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.

Contributor
Posts: 70

Re: Rename an Observation

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;
Contributor
Posts: 70

Re: Rename an Observation

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;

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 633 views
  • 3 likes
  • 4 in conversation