PRELOADFMT error with multilabel format in PROC TABULATE

Reply
Occasional Contributor
Posts: 17

PRELOADFMT error with multilabel format in PROC TABULATE

Hi Everyone,

I'm having trouble applying a multi-label picture format in proc tabulate. i know i need to use the option PRELOADFMT in my CLASS statement but when i do, i receive the following warning: WARNING: The format for variable var1 cannot be preloaded. Preload will have no effect. Nothing I've tried has been able to solve this error and I havent found much online about this error. Here's an idea of what I'm doing: (apologies for typos - I'm unable to copy from the actual code)

data dat1; input var1 $4.;

datalines;

15

30

9999

100

150

9999

9998

20

9997

; run;

proc format library=work;

picture testf (multilabel notsorted)

low-9996="    0000" (prefix="20/")

9997-9999="Worst"

9997="Worse1" (noedit)

9998="Worse2" (noedit);

run;

proc sort data=dat1; by var1; run;

proc tabulate data=dat1;

format var1 testf.;

class var1/mlf preloadfmt order=data;

table var1*(n pctn); run;

Any help would be greatly appreciated!

Super Contributor
Posts: 273

Re: PRELOADFMT error with multilabel format in PROC TABULATE

Posted in reply to ashlicole

This seems peculiar to the picture instruction

as in case of the use of preloadfmt+mlf in the case of a value instruction,

the warning   (not  an error)   is disappearing.

Call Sas technical support.

Andre

Occasional Contributor
Posts: 17

Re: PRELOADFMT error with multilabel format in PROC TABULATE

Here is my new understanding of the problem: It seems that PRELOADFMT cannot be used unless each value/range maps to a single format (thanks to data _null_ for pointing this out on a similar thread). Therefore my picture format for 0-9996="0000" is causing the warning (an error in my case since its not working! Smiley Happy). In this way, you are correct that a picture format cannot be preloaded, since by definition it will never map to a single format (bc the formatted value will change based on the data value - which is the whole point of a picture format in my case). I think this makes sense-- after all, how can a format be pre-loaded if the formatted value depends on the data? Please someone correct me if I'm understanding this incorrectly!

Any ideas on how I can make this multi-label format work for me with PROC TABULATE without using PRELOADFMT to get tables with subgroups?

Super User
Posts: 11,343

Re: PRELOADFMT error with multilabel format in PROC TABULATE

Posted in reply to ashlicole

Not the greatest solution but you could make an format using a CNTLIN data set that would get around the picture part.


Respected Advisor
Posts: 3,799

Re: PRELOADFMT error with multilabel format in PROC TABULATE

Yes this is the point.  Does the OP need a row for all the values from LOW to 9996?

Occasional Contributor
Posts: 17

Re: PRELOADFMT error with multilabel format in PROC TABULATE

Unfortunately, there are far too many value from 0-9996 to individually define. I think CNTLIN would work for formatting the numeric values to get the picture with my prefix (something like label=put("20/"||start, $6.) or similar) but since i need a multilabel for those categories i would have to duplicate some rows to apply multiple format labels, right? My inelegant solution for now is creating a character version of my numeric variable (using something similar to the put() above), then creating a multilabel format for the subgroups that does not include the values previously formatted as pictures (so that they just show up as is). Then i can apply the format in PROC TABULATE as /MLF and the output is as desired. Something like this:

proc format; value $testf (multilabel notsorted)

     "9997-9999"="Worse"

          "9997"="Worse1"

          "9998"="Worse2"; run;

proc tabulate data=test;

class var1 /mlf order=data;

table var1*(n colpctn="%");

format var1 $testf.; run; quit;

Here I don't even need PRELOADFMT... go figure :smileyplain:

Like I said, not as clean as i would like, but it's getting the job done for now without having to duplicate rows for those subgroups in a CNTLIN

Super User
Posts: 11,343

Re: PRELOADFMT error with multilabel format in PROC TABULATE

Posted in reply to ashlicole

PRELOADFMT is basically only when you want to insure the formatted category is included when there are no values for that category.

Which is why data_null keeps asking about do you really need to display:

2/0001

2/0002

2/0003

.

.

2/9996

Worst

   Worse1

   Worse2

since a roughly 10000 row table is very ugly to contemplate.

MLF without PRELOADFMT only displays the rows with data.

Occasional Contributor
Posts: 17

Re: PRELOADFMT error with multilabel format in PROC TABULATE

I believe PRELOADFMT is also used to preserve user-defined order for subgroups in PROC TABULATE. For my multi-label picture format, subgroups were not displayed correctly, even though all subgroups had nonmissing values. Eg, you can see below that the combined category "Worse" shows up fine, but Worse1 and Worse2 appear to be displayed as a single category without the correct formatted label. Maybe PRELOADFMT was not the issue?? I could not get this to work with the picture format any other way..

---------------------------------------------------------------

                                           |                                   |           All           |

                                           |                                   |-------------------------|

                                           |                                   |     N      |     %      |

                                           |-----------------------------------+------------+------------|

                                           |                         |            |            |

                                           |-----------------------------------|            |            |

                                           |20/15                              |        2.00|        0.25|

                                           |-----------------------------------+------------+------------|

                                           |20/20                              |       15.00|        1.87|

                                           |-----------------------------------+------------+------------|

                                           |20/25                              |       41.00|        5.11|

                                           |-----------------------------------+------------+------------|

                                           |20/30                              |       68.00|        8.48|

                                           |-----------------------------------+------------+------------|

                                           |20/40                              |       73.00|        9.10|

                                           |-----------------------------------+------------+------------|

                                           |20/50                              |       99.00|       12.34|

                                           |-----------------------------------+------------+------------|

                                           |20/60                              |       88.00|       10.97|

                                           |-----------------------------------+------------+------------|

                                           |20/70                              |       47.00|        5.86|

                                           |-----------------------------------+------------+------------|

                                           |20/80                              |       31.00|        3.87|

                                           |-----------------------------------+------------+------------|

                                           |20/100                             |       34.00|        4.24|

                                           |-----------------------------------+------------+------------|

                                           |20/125                             |        2.00|        0.25|

                                           |-----------------------------------+------------+------------|

                                           |20/150                             |       78.00|        9.73|

                                           |-----------------------------------+------------+------------|

                                           |20/200                             |       49.00|        6.11|

                                           |-----------------------------------+------------+------------|

                                           |Worse                  |      175.00|       21.82|

                                           |-----------------------------------+------------+------------|

                                           |   r   s  e                   |       66.00|        8.23|

                                           ---------------------------------------------------------------

Super User
Posts: 11,343

Re: PRELOADFMT error with multilabel format in PROC TABULATE

Posted in reply to ashlicole

The order displayed is a combination of how the format is defined and interactions with data order, preloadfmt and printmiss options.

Here is some example code to show the differences. Also see what happens with the NOTSORTED option is removed from the format definitions.

 

/* To demonstrate that the order of definition affects appearance in

multilabel formats. Also appearance options to show the spaces to

get the indent as desired and fix column widths.

*/

proc format library=work;

value accidentl (multilabel notsorted)

1-5 = 'Accidents'

1-3 = ' Transport accidents'

1 = ' Motor vehicle accidents'

2 = ' Water, air, and space'

3 = ' Other land transport accidents'

4-5 = ' Nontransport accidents'

5 = ' Fishing'

;

value accidentr (multilabel notsorted)

1-5 = 'Accidents'

1-3 = ' Transport accidents'

4-5 = ' Nontransport accidents'

1 = ' Motor vehicle accidents'

2 = ' Water, air, and space'

3 = ' Other land transport accidents'

5 = ' Fishing'

;

value accidents (multilabel notsorted)

1 = ' Motor vehicle accidents'

2 = ' Water, air, and space'

3 = ' Other land transport accidents'

5 = ' Fishing'

1-5 = 'Accidents'

1-3 = ' Transport accidents'

4-5 = ' Nontransport accidents'

;

value mf

1 = "Male"

2 = "Female"

;

run;

/* populate a dataset to display */

/* This specifically does NOT generate any data for FISHING above*/

/* to display the behavior of the options below in those cases. */

data junk;

do i=1 to 50;

type = round(4*ranuni(1234)+.5);

sex = round(2*ranuni(3455)+.5);

output;

end;

run;

 

/* Notice that before we get here the data is NOT sorted */

/* in any manner!!!! */

title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentl';

proc tabulate data=junk order=data ;

class type / mlf PRELOADFMT;

/*ASIS preserves the leading spaces in the format, Cellwidth here is optional

for this demo. These will ONLY affect ODS output such as HTML, RTF or PDF

not the OUTPUT window

*/

classlev type/ style=[asis=on cellwidth=1.5in];

class sex;

/* the formatted values of SEX normally take up different lengths, this fixes

the column widths to be the same for both headers. May cause interesting

appearances with large numbers of displayed digits if requested in formats*/

classlev sex/ style=[cellwidth=.5in];

/* to get the ALL to have the same width as SEX need to specify this way*/

table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.

/ printmiss misstext='0';

format type accidentl. sex mf.;

run;title;

title 'Option Order=Data Preloadfmt Printmiss Misstext=0 format accidentr';

proc tabulate data=junk order=data;

class type / mlf PRELOADFMT;

classlev type/ style=[asis=on cellwidth=1.5in];

class sex;

classlev sex/ style=[cellwidth=.5in];

table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.

/ printmiss misstext='0';

format type accidentr. sex mf.;

run;title;

title 'Option Order=Data Preloadfmt Printmiss Misstext="0" format accidents';

proc tabulate data=junk order=data;

class type / mlf PRELOADFMT;

classlev type/ style=[asis=on cellwidth=1.5in];

class sex;

classlev sex/ style=[cellwidth=.5in];

table type=' ', all='Total'*{style=[cellwidth=.5in]}*n=' '*f=comma9. sex=' '*n=' '*f=comma8.

/ printmiss misstext='0';

format type accidents. sex mf.;

run;title;

 

Valued Guide
Posts: 634

Re: PRELOADFMT error with multilabel format in PROC TABULATE

Posted in reply to ashlicole

Formats can call functions in 9.3 so the function could be used to add the 20/.  But apparently user defined functions cannot be used with with preloaded format either.  the following may be closer to a solution.

libname temp 'c:\temp';

proc fcmp outlib=temp.functions.Conversion;

   function div20(num) $;

      cval='20/'||put(num,z4.);

      return(cval);

   endsub;

run;

options cmplib=(temp.functions);

proc format library=work;

invalue testf (multilabel notsorted)

low-9996=[div20()]

9997-9999="Worst"

9997="Worse1"

9998="Worse2";

run;

Occasional Contributor
Posts: 17

Re: PRELOADFMT error with multilabel format in PROC TABULATE

I also tried the CNTLIN option and it actually works pretty nicely. Here is what I did:

data ftest; set dat1(rename=(var1=start));

hlo="M";

fmtname="mult";

label=cat("20/",start);

if start in (9997,9998,9999) then label="Worse";

output;

if start in (9997,9998) then do;

  if start=9997 then label="Worse1";

  else if start=9998 then label="Worse2";

output; end; run;

proc format cntlin=mult library=work; run;

proc tabulate data=dat1;

class var1 /mlf order=data preloadfmt;

table (var1 all)*(n colpctn="%") /misstext="0";

format var1 mult.; run;

Ask a Question
Discussion stats
  • 10 replies
  • 918 views
  • 6 likes
  • 5 in conversation