proc tabulate multilabel and control order of rows

[Ronein]

Hello

I am using Proc format with multilabel option and the using proc tabulate to create a summary report.

I have some requests from the summary report:

1- Using overlapping ranges of formats (as you can see in the example)

2- Show also levels (of class var) with missing values and show them with value 0 (instead of null value)

3-Control the order of the rows such that they will appear in same order as in for format in left side!

When I look at the outcome of the code I see that the orders of the rows is not as I wanted.

'0-11' should be after '11' and before '12' .

proc format;
value ffmt (multilabel)
0='0'
1='1'
2='2'
3='3'
4='4'
5='5'
1-5='1-5'
6='6'
7='7'
8='8'
9='9'
10='10'
6-10='6-10'
11='11'
0-11='0-11'
12='12'
0-12='0-12'
;
run;

Data tbl;
input ID score wealth;
cards;
1 0 100
2 8 200
3 7 300
4 7 150
5 3 250
6 10 500
7 11 600
8 10 380
9 2 280
10 9 900
;
run;

proc tabulate data=tbl;
class score/mlf preloadfmt order=data;
VAR  id  wealth;
table score='' ,
ID=''*N='Number customers'
ID=''*PCTN='PCT of number customers'
wealth=''*SUM='SUM wealth'
wealth=''*PCTSUM='PCT of SUM wealth' /misstext='0';
format score ffmt.;
Run;

[Ronein]

I found the solution. Now I need to understand it please.
By using (multilabel notsorted) I get the correct order.
Does it mean that always need to use option notsorted???

I also see that levels with no values are not shown in the summary report and I want to show them with values 0

[ballardw]

The definition order in the format and the options have an affect.

I am attaching a reference that shows several interactions between multilabel definitions and proc tabulate code.

You likely want to use the PRELOADFMT option but depending on your exact Proc Tabulate code getting 0 for only some statistics well depend a bit on the actual code. Possibly the table option MISSTEXT='0' or a custom format that displays missing values as 0 attached to the specific statistic.

[Ronein]

I still cannot see level '12'  in the summary report.

I want to see it with values zero since there are no observations with score=12

[ballardw]

---

@Ronein wrote:

I still cannot see level '12'  in the summary report.

I want to see it with values zero since there are no observations with score=12

---

Your proc format code as posted does not run because multiple errors. So I am not sure what actual code you are running:

proc format;
value ffmt (multilabel)
0='0'
1='1'
2='2'
3='3'
4='4'
5='5'
1-5='1-5'
6='6'
7='7'
8='8'
9='9'
10='10'
'6-10'     <= missing something o
11='11'
0-11'=0-11'   <= quote before the = is an error
12='12'
0-12'='0-12' <= quote before the = and now unbalanced quotes
;
run;

If that was the actual format code then the format either does not exist OR the proc tabulate is using a different format that had completed previously.

But the proc tabulate as posted:

proc tabulate data=tbl;
class score/mlf preloadfmt order=data;
VAR  id  wealth;
table score='' ,
ID=''*N='Number customers'
ID=''*PCT='PCT number customers'  <= PCT is an invalid statistic, PCTSUM (or one of the variations)
wealth=''*SUM='wealth in $;  <= unbalanced quote And likely the ; doesn't belong
wealth=''PCTSUM='PCT wealth'  <= missing an * between the variable and statistic
;
format score ffmt.;
Run;

And from the PRELOADFMT documentation:

To include all ranges and values of the user-defined formats in the output, use the PRINTMISS option in the TABLE statement. Use care when you use PRELOADFMT with PRINTMISS. This feature creates all possible combinations of formatted class variables. Some of these combinations might not make sense.

So, show the log of the code actually submitted.

This runs and generates output but not in a form I would use:

proc format;
value ffmt (multilabel)
0='0'
1='1'
2='2'
3='3'
4='4'
5='5'
1-5='1-5'
6='6'
7='7'
8='8'
9='9'
10='10'
6-10 ='6-10'
11='11'
0-11='0-11'
12='12'
0-12='0-12'
;
run;

Data tbl;
input ID score wealth;
cards;
1 0
2 8
3 7
4 7
5 3
6 10
7 11
8 10
9 2
10 9
;
run;

proc tabulate data=tbl;
class score/mlf preloadfmt order=data;
VAR  id  wealth;
table score='' ,
   ID=''* (N='Number customers' PCTsum='PCT number customers')
   wealth=''* (SUM='wealth in $' PCTSUM='PCT wealth')
   / printmiss
;
format score ffmt.;
Run;

[Ronein]

proc format;
value ffmt (multilabel  notsorted)
0='0'
1='1'
2='2'
3='3'
4='4'
5='5'
1-5='1-5'
6='6'
7='7'
8='8'
9='9'
10='10'
6-10='6-10'
11='11'
0-11='0-11'
12='12'
0-12='0-12'
;
run;

Data tbl;
input ID score wealth;
cards;
1 0 100
2 8 200
3 7 300
4 7 150
5 3 250
6 10 500
7 11 600
8 10 380
9 2 280
10 9 900
;
run;

proc tabulate data=tbl   noseps;
class score/mlf   preloadfmt   order=data;
VAR  id  wealth;
table score='' ,
ID=''*N='Number customers'
ID=''*PCTN='PCT of number customers'
wealth=''*SUM='SUM wealth'
wealth=''*PCTSUM='PCT of SUM wealth' /printmiss  misstext='0'  box='Score';
format score ffmt.;
Run;