BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
DME790
Pyrite | Level 9

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.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

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

12 REPLIES 12
ballardw
Super User

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
Pyrite | Level 9

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

Reeza
Super User

Saw this earlier, and assumes that ID is numeric. 

 

Total=Sum(of _numeric_) - ID;

BrunoMueller
SAS Super FREQ

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
Pyrite | Level 9

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
Super User

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
Pyrite | Level 9

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
Super User

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

 

	table cluster=" " ALL = 'Column Totals', .... ;
DME790
Pyrite | Level 9

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
Reeza
Super User

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
Pyrite | Level 9
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
Pyrite | Level 9

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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