I have found some pages about this problem,typically there is an idea about "adding type="c" ",however the problem still exists.
Could someone kindly tell me why and how to fix?The attachment is data for this question and column purc_amt is numeric while other are char type.
data mcc;
set mcc_code(rename=(mcc=start mcc_desc=label)) end=last;
retain start label;
fmtname="mccdesc";
type="c";
run;
proc sql noprint;
insert into mcc
set start="other"
,label=""
,fmtname="$mccdesc"
,type="c";
quit;
proc format cntlin=mcc;run;
Your specific problem is the data if you are attempting to create formats using that spreadsheet's values.
mcc mcc_desc 10 D37246 10 l42883 10 s91910
Sorting the data shows that MCC has multiple values of mcc_desc for some values, as shown above.
FORMAT will allow you to have multiple values assigned to the same format text but not a single value assigned to multiple values of text
unless you use a multilabel format which would then need different content to create a format. There would still need to be a single value as the top level of the multilabel for each repeated value and most procedures do not honor the multilabel for display. This holds regardless of whether the variable is numeric or character.
Hi @ccnky123,
If you want to apply the format to numeric values (and, indeed, the MCC values seem to be numeric), you should not force it to be a character format (by setting type="c"). To get the "other" specification into the definition use hlo='O' rather than start="other". (And fmtname would be "mccdesc" without a dollar sign even if it was a character format.)
The overlapping values are a bigger issue, though: Let's say, the format will be applied to value 22. Is the formatted value supposed to be "U48428" or "T81390" or "C11804" or ... (see dataset MCC_CODE)? It's possible to create a multilabel format (set hlo='M' and the error message will go away), but I'm skeptical that this is what you really want because only a couple of procedures can utilize multilabel formats.
Sorry, confusion was not intended. Okay, let's assume you really want to create a numeric multilabel format MCCDESC from dataset MCC_CODE.
/* Create simplified input dataset */
data mcc_code;
input mcc mcc_desc $;
cards;
85 Q81739
22 U48428
22 T81390
;
/* Create dataset containing format definition */
data mcc;
set mcc_code(rename=(mcc=start mcc_desc=label)) end=last;
fmtname='mccdesc';
hlo='M';
output;
if last then do;
hlo='O';
label=' ';
output;
end;
run;
/* Create numeric multilabel format MCCDESC */
proc format cntlin=mcc;
run;
/* Apply the format using the PUT statement
Result: Only the first of multiple labels is used. */
data test;
input mcc x;
put mcc mccdesc.;
cards;
22 1
50 2
85 3
;
/* Demonstrate possible use of multilabel format
Result: Multiple labels are used, one value (22) in two categories (T81390 and U48428) */
proc means data=test;
class mcc / mlf;
format mcc mccdesc.;
var x;
run;
Your specific problem is the data if you are attempting to create formats using that spreadsheet's values.
mcc mcc_desc 10 D37246 10 l42883 10 s91910
Sorting the data shows that MCC has multiple values of mcc_desc for some values, as shown above.
FORMAT will allow you to have multiple values assigned to the same format text but not a single value assigned to multiple values of text
unless you use a multilabel format which would then need different content to create a format. There would still need to be a single value as the top level of the multilabel for each repeated value and most procedures do not honor the multilabel for display. This holds regardless of whether the variable is numeric or character.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.