BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ccnky123
Calcite | Level 5

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;

  

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

5 REPLIES 5
FreelanceReinh
Jade | Level 19

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.

 

 

ccnky123
Calcite | Level 5
I am confused with your words..Would you please kindly tell me your exact code?
FreelanceReinh
Jade | Level 19

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;
ccnky123
Calcite | Level 5
thank you for your attenion.ballardw might tell more about the essence of this question and solve my confuse..thank you both!
ballardw
Super User

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4878 views
  • 1 like
  • 3 in conversation