topic Re: ERROR: For format $MCCDESC, this range is repeated, or values overlap: 26-26. in SAS Programming

ERROR: For format $MCCDESC, this range is repeated, or values overlap: 26-26.

ccnky123 — Thu, 20 Sep 2018 09:06:52 GMT

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;

Re: ERROR: For format $MCCDESC, this range is repeated, or values overlap: 26-26.

FreelanceReinh — Thu, 20 Sep 2018 09:58:40 GMT

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.

Re: ERROR: For format $MCCDESC, this range is repeated, or values overlap: 26-26.

ccnky123 — Thu, 20 Sep 2018 10:04:44 GMT

I am confused with your words..Would you please kindly tell me your exact code?

Re: ERROR: For format $MCCDESC, this range is repeated, or values overlap: 26-26.

FreelanceReinh — Thu, 20 Sep 2018 10:37:07 GMT

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;

Re: ERROR: For format $MCCDESC, this range is repeated, or values overlap: 26-26.

ballardw — Mon, 24 Sep 2018 20:05:44 GMT

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.

Re: ERROR: For format $MCCDESC, this range is repeated, or values overlap: 26-26.

ccnky123 — Sun, 23 Sep 2018 01:20:47 GMT

thank you for your attenion.ballardw might tell more about the essence of this question and solve my confuse..thank you both!