format multilabel all values

[ciro]

xcen > i

proc format;

value $clfmt (multilabel)

'a','b','c','d','e'='Normal'

other='other'

all='Total'

;

t

hi,

I would like to build a multilabel format with one category that contains all the possible values.

In other terms I'm trying to code something like this

proc format;

value $clfmt (multilabel)

'a','b','c','d','e'='Normal'

other='other'

all='Total'

;

however this seems not to work properly

data one;

case='a'; output;

case='b'; output;

case='c'; output;

case='d'; output;

case='e'; output;

case='z'; output;

case='x'; output;

case='a'; output;

run;

data two;

set one;

count=1;

run;

proc format;

value $clfmt (multilabel)

'a','b','c','d','e'='Normal'

other='other'

all='Total'

;

proc tabulate data=two;

class case /mlf;

var count;

tables case,count*sum='';

format case $clfmt.;

run;

thank you in advance for any suggestions

[Reeza]

Try using the all in proc tabulate...

proc tabulate data=two;

class case /mlf;

var count;

tables case all='Total',count*sum='';

format case $clfmt.;

run;

-------------------------------------------- Edit: I'm not sure the "ALL" is part of proc format, I've only ever seen the other used.

[ciro]

xcen > it

thank you very much reeza for your answer, but I would like to find a multilabel format solution, as I need to output different subtotals. Actually it seems that "all" is not acceptable in proc format

I have also tried the following

proc format;

value $clfmt (multilabel)

'a','b','c','d','e'='Normal'

other='other'

'a','b','c','d','e',other='Total'

;

but sas gives me the following error:

The OTHER= clause can only appear once in a format definition.

the problem is I don't know in advance which other values the variable, in my real situation, may assume.

any other suggestions from the community?

[Keith]

Just put LOW - HIGH = TOTAL to include all values.

[ciro]

thanks keith. I simply didn't think to low-high.

there is one more problem though as when I tabulate with the following format

proc format;

value $clfmt (multilabel)

'a','b','c','d','e'='Normal'

other='Odd'

low-high='Total'

;

proc tabulate data=two;

class case /mlf;

var count;

tables case,count*sum='';

format case $clfmt.;

run;

it does not produce the category "Odd".

Is anything wrong with my code or it is simply not possible to use the Other expression with multilabel?

any workaround?

[Ksharp]

Yes. I also notice that SAS LOG complain the range other cann't reach .

There is a workaround is firstly hold these other values in a macro variable.

data one;
_case='a'; output;
_case='b'; output;
_case='c'; output;
_case='d'; output;
_case='e'; output;
_case='z'; output;
_case='x'; output;
_case='a'; output;
run;

 

data two;
set one;
count=1;
run;
proc sql noprint;
 select distinct quote(strip(_case)) into : other separated by ',' 
  from one
   where _case not in ('a','b','c','d','e');
quit;


proc format;
value $clfmt (multilabel)
'a','b','c','d','e'='Normal'
&other ='Other'
low-high='Total'
;
run;

proc tabulate data=two;
class _case /mlf;
var count;
tables _case,count*sum='';
format _case $clfmt.;
run;

Ksharp

[Keith]

I believe the problem arises because SAS treats 'Other' as the final category of the format, irrespective of where it is placed.  Therefore low-high will be processed beforehand, resulting in no possible values for 'Other'.  You therefore need a workaround such as that suggested by @Ksharp.

[Alpay]

The below code might be another workaround.

Pre-specified values are excluded from low-high range and put into 'Other' bucket.

Zafer

data one;

retain count 1;

case='a'; output;

case='b'; output;

case='c'; output;

case='d'; output;

case='e'; output;

case='z'; output;

case='x'; output;

case='a'; output;

run;

proc format;

value $cfmt (multilabel)

'a','b','c','d','e'='Normal'

low -< 'a',

'a' <-< 'b',

'b' <-< 'c',

'c' <-< 'd',

'd' <-< 'e',

'e' <-high= 'Other'

low-high = 'Total';

run;

proc tabulate data=one;

class case /mlf ;

var count;

tables case,count*sum='';

format case $cfmt.;

run;

[ciro]

thank you to all fro the answers. I've learned something from each of them