proc freq with format to re-order output

[fengyuwuzu]

I want to list the output with 7.5 dose as the first one, but using the following code I still got 7.5 dose as the last one. What is wrong here? 

PROC FORMAT ;
   VALUE $ trtfmt
        'A'='7.5 mg/26-37Kg  ' 
        'B'='10.0 mg/38-50Kg '
        'C'='12.5 mg/51-62Kg ' 
		'D'='15.0 mg/63-75Kg '
		'E'='17.5 mg/76-87Kg '
		'F'='20.0 mg/88-111Kg';
RUN;

proc freq data=mydata ORDER=FORMATTED;
tables trtcat1 * AGEGRP /nocol ;
format trtcat1 $trtfmt.;
run;

[Reeza · Accepted solution]

I can't replicate your issue. You have something wrong somewhere else. 

Check your raw values.

PROC FORMAT ;
   VALUE $ trtfmt
        'A'='7.5 mg/26-37Kg  ' 
        'B'='10.0 mg/38-50Kg '
        'C'='12.5 mg/51-62Kg ' 
		'D'='15.0 mg/63-75Kg '
		'E'='17.5 mg/76-87Kg '
		'F'='20.0 mg/88-111Kg';
RUN;

data test;
str='ABCDEF';
do i=1 to 30;
x=floor(rand('uniform')*6)+1;
y=char(str, x);
output;
end;
run;

proc freq data=test;
table y;
format y $trtfmt.;
run;

[fengyuwuzu]

I used your code, which works. But in the output it is the "A"...."D" that are listed in order,

not the actual doses.

Is there a way to replace the "A" to "D" with the actual doses? Thanks

[Reeza]

No it doesn't. 

The output is below. Review your code and run my exact code. Then look for the differences.

The FREQ Procedure

y	Frequency	Percent	Cumulative Frequency	Cumulative Percent
7.5 mg/26-37Kg	10	33.33	10	33.33
10.0 mg/38-50Kg	3	10.00	13	43.33
12.5 mg/51-62Kg	6	20.00	19	63.33
15.0 mg/63-75Kg	4	13.33	23	76.67
17.5 mg/76-87Kg	2	6.67	25	83.33
20.0 mg/88-111Kg	5	16.67	30	100.00

[fengyuwuzu]

Right. You are right
I did not run the format correctly.

[fengyuwuzu]

Hi Reeza

I have another question. 

in your code, the values of Y are "A", ... "F". 

In my data, the values of the doses are "7.5 mg/26-37Kg", etc. 

So, do I have to first convert my data to a format with Doses values of "A", "B", ... "F" to use this format to control output order? 

[Reeza]

I have no idea you mean, based on your initial question this was how your data is structured. If it's not you need to clearly state how it is structured. New question with sample input data that accurately reflects your problem. 

[ballardw]

You have two options, remove the Order=Formatted and let the internal order work or

Change

'A'='7.5 mg/26-37Kg '

to

'A'=' 7.5 mg/26-37Kg'

the leading space will become part of the value and spaces sort befor digits.