topic Re: proc tabulate + preloadfmt: how to remove the warning in SAS Programming

proc tabulate + preloadfmt: how to remove the warning

xxformat_com — Sun, 14 Aug 2022 21:28:06 GMT

I'm generating code like this for all variables of all the datasets of a given library.

I cannot know beforehand waht format will be used, if any.

ods output Tabulate.Report.Table=SHOES_FREQ;

proc tabulate data=sashelp.SHOES;
    class Sales / preloadfmt missing;
    table Sales / printmiss;
run;

My original idea was to look at the format variable in the metadata
to decide if the preloadfmt was to be used in proc tabulate.

proc sql;
   select name, format
   from dictionary.columns
   where upcase(libname)='SASHELP' and
         upcase(memname)='SHOES';
quit;

But logically, it won't work for generic formats provided by the SAS system. It is fine for me.

Option 1 would be to find a way to identify if the format is relevant or not beforehand. Any idea how?
Option 2 would be to set a global option to nowarn to avoid the warning in that case. Does such solution exists?

Option 3?

Re: proc tabulate + preloadfmt: how to remove the warning

mkeintz — Mon, 15 Aug 2022 01:25:36 GMT

If you are using a generic SAS format, why do you need the PRELOADFMT option? I believe the two most common purposes (? the only purposes?) of that option is to either (1) force a re-ordering of the CLASS variables according to a user-specific format, as opposed to ordering by underlying value, or (2) accommodate a multilabel format. But if you are happy with a generic SAS format, you are probably not interested in re-ordering or multi-labelling, so why use PRELOADFMT at all?

Now if you had naming conventions for user specified formats that do one of the above (say a double leading __), you could run your proc sql to identify any CLASS variables having such formats do have the PRELOADFMT option applied. I suspect that would be the only situation in which you would need PRELOADFMT. I.e., make PRELOADFMT the exception, not the default.

Re: proc tabulate + preloadfmt: how to remove the warning

xxformat_com — Mon, 15 Aug 2022 06:43:00 GMT

I'm building a generic tool. Therefore I do not know in advance what will be the variables with formats and what kind of format will be used. So it is a format like $dollar12 as in the example I will exclude the option. But if it is a format like sex M=Male/F=Female I want to use preloadfmt.

Re: proc tabulate + preloadfmt: how to remove the warning

xxformat_com — Mon, 15 Aug 2022 06:59:45 GMT

I've just realised that I can at least ignore the format from the SAS system using dictionaries.

proc sql;
   select fmtname
   from dictionary.formats
   where fmttype='F' and source in ('B','U');
quit;

If you have any other alternative, feel free to share.

For the time being this solution should be sufficient for me.

Re: proc tabulate + preloadfmt: how to remove the warning

ballardw — Mon, 15 Aug 2022 20:41:01 GMT

Preloadfmt cannot use any "generic" format that does not provide a closed list of values. Sales, as a numeric value has literally an infinite number of possible values so the is no way to list "every value" which what Preloadfmt does for the output. Be glad it didn't even attempt it as your SAS session would be locked up trying to generate an output table with way more rows that I want to contemplate(you would run out of disk space, pretty much guaranteed)

Same with the $ format, even with a small value like $2. That is not a closed list of values.

You should not be attempting to use preloadfmt without knowing before hand the nature of the format. Or you might make sure that all of your custom formats create output data sets with the Cntlout option and then parse those files for the clues that might allow determination of the format details. SAS doesn't provide any closes list formats installed by default that I am aware of.

If you don't know the format ahead of time why are you even contemplating Preloadfmt???

You may also consider that Preloadfmt is not desirable for many reports. Consider a Zip code, or similar code with many values, that use with a format that reports the CountyName for the value. If I am doing a report that only includes a one region of the state then including the Preloadfmt version could well have 40+ counties reporting in multiple places where there is only data for four or five that are actually in the region. So the default assigned format, which I would use, but I would not use the Preloadfmt option as 80% of the cells in Proc Tabulate output would be empty without data.

If you have formats such as for ICD-10 medical diagnosis codes you may have 10,000 values in a format.

Re: proc tabulate + preloadfmt: how to remove the warning

xxformat_com — Tue, 16 Aug 2022 08:50:09 GMT

As written above, I don't know the name of the formats beforehand. My goal is not to use it when not relevant. My issue was to find out if the format was relevant or not. I've found a way (see the accepted solution). When I generate proc tabulate I exclude the option when the format is not relevant and I include it when it is relevant.