DATA Step, Macro, Functions and more

proc freq with format to re-order output

Accepted Solution Solved
Reply
Super Contributor
Posts: 312
Accepted Solution

proc freq with format to re-order output

[ Edited ]

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;

 

 


Accepted Solutions
Solution
‎10-08-2016 09:37 PM
Super User
Posts: 17,840

Re: proc freq with format to re-order output

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;

View solution in original post


All Replies
Solution
‎10-08-2016 09:37 PM
Super User
Posts: 17,840

Re: proc freq with format to re-order output

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;
Super Contributor
Posts: 312

Re: proc freq with format to re-order output

[ Edited ]

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

not the actual doses.

 

Capture1.PNG

 

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

Super User
Posts: 17,840

Re: proc freq with format to re-order output

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

 

 

Super Contributor
Posts: 312

Re: proc freq with format to re-order output

Right. You are right
I did not run the format correctly.
Super Contributor
Posts: 312

Re: proc freq with format to re-order output

[ Edited ]

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? 

Super User
Posts: 17,840

Re: proc freq with format to re-order output

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. 

Super User
Posts: 10,500

Re: proc freq with format to re-order output

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.

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 381 views
  • 0 likes
  • 3 in conversation