topic Re: proc freq with format to re-order output in SAS Programming

proc freq with format to re-order output

fengyuwuzu — Fri, 30 Sep 2016 20:05:13 GMT

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;

Re: proc freq with format to re-order output

Reeza — Fri, 30 Sep 2016 20:21:34 GMT

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;

Re: proc freq with format to re-order output

ballardw — Fri, 30 Sep 2016 20:28:45 GMT

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

Change

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

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

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

Re: proc freq with format to re-order output

fengyuwuzu — Sun, 09 Oct 2016 00:51:52 GMT

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

Re: proc freq with format to re-order output

Reeza — Sun, 09 Oct 2016 01:22:29 GMT

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

Re: proc freq with format to re-order output

fengyuwuzu — Sun, 09 Oct 2016 01:38:27 GMT

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

Re: proc freq with format to re-order output

fengyuwuzu — Sun, 09 Oct 2016 02:49:37 GMT

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?

Re: proc freq with format to re-order output

Reeza — Sun, 09 Oct 2016 03:11:58 GMT

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.