proc format with Overlapping Ranges

[Ronein]

Hello

I want to create report  with  Overlapping Ranges.

I want to get report with  5 rows

row 1- Team a

row 2- Team b

row 3- Total of teams a and b

row 4- Team c

row5-Total of teams a,b,c

In the result I don't get my desired result.

May anyone help please?

Data rawdata;
input ID SUM_Y  Team $;
cards;
3935 10 a
5970 20 b
6120 30 c
6120 40 c
7123 50 b
7507 60 a
7551 70 a
7551 80 a
7580 90 a
7989 100 b
8789 110 b
9039 120 c
9957 130 b
11235 140 a
12897 150 c
14421 160 c
16305 170 b
16530 180 a
16547 190 b
18710 200 c
;
run;


proc format;
value $ffmt  (multilabel)
'a'='a'
'b'='b'
'a','b'='Total'
'c'='c'
'a','b','c'='Total';
run;


PROC TABULATE DATA=rawdata  FORMAT=comma12.;
	VAR ID SUM_Y ;
	CLASS Team  / mlf ;
	format Team $ffmt.;
	TABLE Team=''  ,
           ID="No_records"*N="" 
           SUM_Y="Total Y"*Sum="" /box='Team';
RUN;

[Reeza]

Post the log please. 

---

@Ronein wrote:

Hello

I want to create report  with  Overlapping Ranges.

I want to get report with  5 rows

row 1- Team a

row 2- Team b

row 3- Total of teams a and b

row 4- Team c

row5-Total of teams a,b,c

In the result I don't get my desired result.

May anyone help please?

Data rawdata;
input ID SUM_Y  Team $;
cards;
3935 10 a
5970 20 b
6120 30 c
6120 40 c
7123 50 b
7507 60 a
7551 70 a
7551 80 a
7580 90 a
7989 100 b
8789 110 b
9039 120 c
9957 130 b
11235 140 a
12897 150 c
14421 160 c
16305 170 b
16530 180 a
16547 190 b
18710 200 c
;
run;


proc format;
value $ffmt  (multilabel)
'a'='a'
'b'='b'
'a','b'='Total'
'c'='c'
'a','b','c'='Total';
run;


PROC TABULATE DATA=rawdata  FORMAT=comma12.;
	VAR ID SUM_Y ;
	CLASS Team  / mlf ;
	format Team $ffmt.;
	TABLE Team=''  ,
           ID="No_records"*N="" 
           SUM_Y="Total Y"*Sum="" /box='Team';
RUN;

---

[Ronein]

There is no error so no need to post the log.

The output that I get is not what I want.

As I wrote before I want to get in output 5 rows:(5 groups) and for each group to calculate total Y.

row 1- Team a

row 2- Team b

row 3- Total of teams a and b

row 4- Team c

row5-Total of teams a,b,c

[Kurt_Bremser]

---

@Ronein wrote:

There is no error so no need to post the log.

---

When someone with about a hundred times your SAS experience asks you to post the log, you better post the log, period. See Maxim 2.

Logs can reveal logic mistakes that do not result in ERROR or WARNING messages, so there's a reason for it.

[Ronein]

Okay, anyway I was out of home and didn't have access to SAS in order to run it.

I don't understand why should I send the log while I write the code and anyone can run the code and look at the Log.

There was no bad meaning so no need to be angry.

I appreciate this forum very much!

[Kurt_Bremser]

---

@Ronein wrote:

Okay, anyway I was out of home and didn't have access to SAS in order to run it.

I don't understand why should I send the log while I write the code and anyone can run the code and look at the Log.

There was no bad meaning so no need to be angry.

I appreciate this forum very much!

---

Very often problems can be caused by non-standard settings or environments, which settings will not be obvious from the code you post, but from the log.

There is a very big reason to place Maxim 2 so high up in the list. The log IS the #1 diagnostic tool for us code doctors.

[Kurt_Bremser]

I did not mean to come across as angry. But maybe like a stern teacher in school 😉

[ballardw]

---

@Ronein wrote:

There is no error so no need to post the log.

The output that I get is not what I want.

As I wrote before I want to get in output 5 rows:(5 groups) and for each group to calculate total Y.

row 1- Team a

row 2- Team b

row 3- Total of teams a and b

row 4- Team c

row5-Total of teams a,b,c

---

So, what is wrong with the output?

The order of definition coupled with options in proc tabulate will change the order of results. Please see the attached program example.

The example creates a small data set and a couple of different formats and then uses the formats with different proc tabulate options.

[Tom]

In the result I don't get my desired result.

What result do you want? What result are you getting. How is it different than what you want.

[Tom]

If you want the format to define 5 groups you need to define 5 groups instead of the 4 groups that you have defined now.

value $ffmt  (multilabel)
  'a'='a'
  'b'='b'
  'a','b'='Total AB'
  'c'='c'
  'a','b','c'='Total ABC'
;

[ghosh]

proc format;
value $ffmt  (multilabel) 
'a'='1a'
'b'='2b'
'a','b'='3Total ab'
'c'='4c'
'a','b','c'='5Total abc';
run;


proc tabulate DATA=rawdata FORMAT=comma12.  ;
class Team / mlf preloadfmt exclusive order=formatted;
	format Team $ffmt.;
VAR SUM_Y ;
table team, SUM_Y="Total Y"*Sum="" /box='Team';

run;

[Ronein]

Thank you very much.

As I see that names of the categories in proc format must be different of each other.

It means that I cannot call "Total"  to  multiple groups.

The only problem is how to keep the order of the rows as in the proc format.

The only solution that I found is to add numbers to the names.Is there a more clever way?It doesn't look so well in the output.

'a'='1.a'
'b'='2.b'
'a','b'='3.Total AB'
'c'='4.c'
'a','b','c'='5.Total ABC';

I also want to ask if all keywords mlf preloadfmt exclusive are essential and what is the meaning of each of them?

Data rawdata;
input ID SUM_Y  Team $;
cards;
3935 10 a
5970 20 b
6120 30 c
6120 40 c
7123 50 b
7507 60 a
7551 70 a
7551 80 a
7580 90 a
7989 100 b
8789 110 b
9039 120 c
9957 130 b
11235 140 a
12897 150 c
14421 160 c
16305 170 b
16530 180 a
16547 190 b
18710 200 c
;
run;



proc format;
value $ffmt  (multilabel)
  'a'='1.a'
  'b'='2.b'
  'a','b'='3.Total AB'
  'c'='4.c'
  'a','b','c'='5.Total ABC';
Run;


proc tabulate DATA=rawdata FORMAT=comma12.  ;
class Team / mlf preloadfmt exclusive order=formatted;
	format Team $ffmt.;
VAR ID SUM_Y ;
table team=' ', 
ID="No_records"*N=""
SUM_Y="Total Y"*Sum="" /box='Team';
run;

[ghosh]

You can label the sums as TOTAL and SUB-TOTAL

[Ronein]

Can you please send an example?

I didn't understand how to do it

Thanks

[ghosh]

/* Here is my updated code.  
You can now put anything as the format label.

Please see the following link for explanation 

https://blogs.sas.com/content/sgf/2016/12/16/creating-and-using-multilabel-formats/
*/



proc format;
 value $ffmt  (multilabel notsorted) 
	'a'='a'
	'b'='b'
	'a','b'='Sub Total ab'
	'c'='c'
	'a','b','c'='Total abc';
run;

proc tabulate DATA=rawdata FORMAT=comma12.  ;
	class Team / mlf preloadfmt order=data;
	format Team $ffmt.;
	VAR SUM_Y ;
	table team, SUM_Y="Total Y"*Sum="" /box='Team';
run;