proc format doesn't display completely in nested tabulate

[orlyz2k]

Hi, I was trying to format a table in proc tabulate. However part of the table didn't show the complete format I wrote in the proc format,

proc format;
value $dosegroupl (notsorted)
'0.003' = '0.003 mg/kg'
'0.03' = '0.03  mg/kg'
'0.3' = '0.3 mg/kg'
'2.5' = '2.5 mg/kg'
'5.0' = '5.0 mg/kg'
'10.0' = '10.0 mg/kg'
'15.0' = '15.0 mg/kg';
run;

proc tabulate data=bor missing;
format exdosa1 $dosegroupl. bor $resl.;
class mhdiag / order=freq;
class exdosa1 bor / preloadfmt order=data; * preloadfmt forces the specific ordering defined in proc format;

The result display as this:

Not sure what's the problem is.

Can anyone help me? Many thanks.

[orlyz2k]

Sorry the webpage auto removed part of the code. 

[BrunoMueller]

Providing proc contents output of the data set bor would certainly help.

I created some test data and everything is just printed fine, see code below. There might be issues with length or type of variables.

proc format;
  value $dosegroupl (notsorted)
    '0.003' = '0.003 mg/kg'
    '0.03' = '0.03  mg/kg'
    '0.3' = '0.3 mg/kg'
    '2.5' = '2.5 mg/kg'
    '5.0' = '5.0 mg/kg'
    '10.0' = '10.0 mg/kg'
    '15.0' = '15.0 mg/kg'
  ;
run;

data bor;
  infile cards dlm=",";
  input
    mhdiag : $32.
  ;
  length exdosa1 $ 8;

  do exdosa1 = '0.003','0.03','0.3', '2.5', '5.0', '10.0','15.0';   
    output;
  end;

  cards;
BOR
PR (confirmed)
unconfirmed PR
;

proc tabulate data=bor missing;
  format exdosa1 $dosegroupl.;
  class mhdiag / order=freq;
  class exdosa1 / preloadfmt order=data;

  * preloadfmt forces the specific ordering defined in proc format;
  table mhdiag, exdosa1;
run;

[orlyz2k]

Thank you Bruno！I finally figured that it’s because there are 5 and 5.0 in my exdosa1 variable and so like the other ones didn’t display right. The tip is really helpful! Thanks a lot!

[ballardw · Accepted solution]

It would help to explicitly describe which values are not displaying correctly.

However the values you show in the column heading for Exdosa1 shows that you have values in the data that do not have rules in the format. Clearest example is the multiple appearance of 10 and 15. Which very likely means that you have different values in the data that appear in the table as the same. If the actual value is "10" you did not provide a rule for display so would be shown as 10. If the value is " 10", same no rule plus if that leading "space" is some other non-printable character means the value is different than "10" so gets its own column.

An example: The third row of data has a null character, ASCII 255, not a space.

data example;
 input x $5.;
datalines;
10.0
10
 10
;

proc format;
value $dosegroupl (notsorted)
'0.003' = '0.003 mg/kg'
'0.03' = '0.03  mg/kg'
'0.3' = '0.3 mg/kg'
'2.5' = '2.5 mg/kg'
'5.0' = '5.0 mg/kg'
'10.0' = '10.0 mg/kg'
'15.0' = '15.0 mg/kg';
run;

proc tabulate data=example;
   class x;
   format x $dosegroupl.;
   table n,x;
run;

I think that you may also want the PRINTMISS option in the table options .

[orlyz2k]

Hi Ballardw, Yeah i did find out it’s because of the different format of 5.0 and 5. I add the rule to proc format and the problem solved! Thanks a lot!