topic Re: proc format with Overlapping Ranges in SAS Programming

proc format with Overlapping Ranges

Ronein — Sun, 23 Jun 2019 12:43:28 GMT

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;

Re: proc format with Overlapping Ranges

Reeza — Sun, 23 Jun 2019 19:45:16 GMT

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;

Re: proc format with Overlapping Ranges

Ronein — Sun, 23 Jun 2019 20:11:27 GMT

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

Re: proc format with Overlapping Ranges

Tom — Sun, 23 Jun 2019 20:30:28 GMT

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.

Re: proc format with Overlapping Ranges

Tom — Sun, 23 Jun 2019 20:33:28 GMT

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'
;

Re: proc format with Overlapping Ranges

ghosh — Sun, 23 Jun 2019 20:38:12 GMT

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;

Re: proc format with Overlapping Ranges

Ronein — Mon, 24 Jun 2019 03:30:53 GMT

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;

Re: proc format with Overlapping Ranges

ghosh — Mon, 24 Jun 2019 03:54:01 GMT

You can label the sums as TOTAL and SUB-TOTAL

Re: proc format with Overlapping Ranges

Ronein — Mon, 24 Jun 2019 04:10:04 GMT

Can you please send an example?

I didn't understand how to do it

Thanks

Re: proc format with Overlapping Ranges

Kurt_Bremser — Mon, 24 Jun 2019 10:07:30 GMT

@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.

Re: proc format with Overlapping Ranges

Ronein — Mon, 24 Jun 2019 10:15:41 GMT

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!

Re: proc format with Overlapping Ranges

Kurt_Bremser — Mon, 24 Jun 2019 10:29:31 GMT

@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.

Re: proc format with Overlapping Ranges

Kurt_Bremser — Mon, 24 Jun 2019 10:41:20 GMT

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

Re: proc format with Overlapping Ranges

ghosh — Mon, 24 Jun 2019 13:51:56 GMT

/* 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;

Re: proc format with Overlapping Ranges

ballardw — Mon, 24 Jun 2019 15:02:07 GMT

@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.