Sort According to Character Format

[tburus]

I have seen various helps on this, but none of them seems to work in my case. I am trying to sort a dataset by the formatted values of a character variable. No matter what I try, the best result I get is alphabetical order, which is not what I want (I want the order they're listed in my format definition).

Code:

proc format;
	value $ dxname 
		"Strain/Sprain" = "Strain/Sprain"
   		"Contusion" = "Contusion"
 		"Fracture" = "Fracture"
   		"Concussion" = "Concussion"
   		"Other/Unknown" = "Other";
run; 

data viz_dx_pct;
	set mfb_dx_pct;
	length interest $ 30;
	interest = diag;
	format interest $dxname. wgt_pct mypcta.;
run;

data viz_dx_pct2;
	set viz_dx_pct;
	format interest $dxname.;
	val_fmt = put(interest, $dxname.);
run;

proc sort data=viz_dx_pct2; by val_fmt; run;

Result:

[Reeza]

For a data set or report? I think reports/print will do it correctly but not sure about within a data step you may need to do a conversion unfortunately and then sort. You already have two variables so that should work. 

data viz_dx_pct;
	set mfb_dx_pct;
	length interest $ 30;
	interest = put( diag, $dxname.);
	format  wgt_pct mypcta.;
run;

---

@tburus wrote:

I have seen various helps on this, but none of them seems to work in my case. I am trying to sort a dataset by the formatted values of a character variable. No matter what I try, the best result I get is alphabetical order, which is not what I want (I want the order they're listed in my format definition).

Code:

proc format;
	value $ dxname 
		"Strain/Sprain" = "Strain/Sprain"
   		"Contusion" = "Contusion"
 		"Fracture" = "Fracture"
   		"Concussion" = "Concussion"
   		"Other/Unknown" = "Other";
run; 

data viz_dx_pct;
	set mfb_dx_pct;
	length interest $ 30;
	interest = diag;
	format interest $dxname. wgt_pct mypcta.;
run;

data viz_dx_pct2;
	set viz_dx_pct;
	format interest $dxname.;
	val_fmt = put(interest, $dxname.);
run;

proc sort data=viz_dx_pct2; by val_fmt; run;

Result:

---

[Tom · Accepted solution]

I don't understand what you want.  You just created a new character variable and sorted by that variable.

If you want the new values to sort then make them have values that sort.

proc format;
value $dxname_order
  "Strain/Sprain" = "001"
  "Contusion"     = "002"
  "Fracture"      = "003"
  "Concussion"    = "004"
  "Other/Unknown" = "005"
;
run; 

data viz_dx_pct2;
  set viz_dx_pct;
  val_fmt = put(interest, $dxname_order.);
run;

[Reeza]

I think OP is looking for something like the GROUPFORMAT option which would allow the variable to take the correct order based on the format. but it doesn't apply to any PROC other than a data step and not even in CAS.

[Kurt_Bremser]

Your format does nothing except shortening "Other/Unknown" to "Other". Why do you expect a different order when the values are the same?

If you want to force a different order, create an informat and a format and convert to a numeric value:

proc format;
invalue dxname 
  "Strain/Sprain" = 1
  "Contusion" = 2
  "Fracture" = 3
  "Concussion" = 4
  "Other/Unknown" = 99
;
value dxname 
  1 = "Strain/Sprain"
  2 = "Contusion"
  3 = "Fracture"
  4 = "Concussion"
  99 = "Other/Unknown"
;
run; 

data have;
input diag $30.;
datalines; 
Concussion
Fracture
Concussion
Other/Unknown
Strain/Sprain
Contusion
;

data want;
set have;
n_diag = input(diag,dxname.);
format n_diag dxname.;
run;

proc sort data=want;
by n_diag;
run;

[tburus]

Thanks, everyone. Yes, I've been all sorts of confused on this. I was trying to get the specified order from the format and thought that was possible. Hacking it with the sortable numbering works. I need it for PROC SGRENDER and was also running into the issue that it was automatically recognizing the label "Other" and doing weird things with it, even when I said OTHERSLICE=FALSE.

[Tom]

If you want the format to remember the order you need to use NOTSORTED option when defining it.  Some procedures, like PROC TABULATE, can use the MLF option on the CLASS statement to allow them to access the order of the values in the format definition.

https://support.sas.com/kb/12/904.html

[ballardw]

---

@Tom wrote:

If you want the format to remember the order you need to use NOTSORTED option when defining it.  Some procedures, like PROC TABULATE, can use the MLF option on the CLASS statement to allow them to access the order of the values in the format definition.

https://support.sas.com/kb/12/904.html

---

I think you meant PRELOADFMT instead of MLF.

[Ksharp]

Try padding some blank before format.

proc format;
	value $ dxname 
		"Strain/Sprain" = "        Strain/Sprain"
   		"Contusion" = "     Contusion"
 		"Fracture" = "     Fracture"
   		"Concussion" = "  Concussion"
   		"Other/Unknown" = "Other";
run;