PROC REPORT with default order option creates unwanted order

[Derek_Hu]

Hi,
I can't figure out the reason for an unwanted result.

Here is my code and result. Why VSSEQ=0.1 is behind VSSEQ = 2 and 3 in the result? 

Thanks for your help!

data vitals;
input VSSEQ VSTEST $ VISIT;
datalines;
0.1 SBP 0
0.1 SBP 2
0.1 SBP 3
2 DBP 0
2 DBP 1
2 DBP 2
2 DBP 3
3 PULSE 0
3 PULSE 1
3 PULSE 2
run;

proc report data=vitals;
column VSSEQ vstest visit;
define VSSEQ /order ORDER=FORMATTED;
define vstest /order order=data;
define visit /order order=data;
run;

[FreelanceReinh · Accepted solution]

Hi @Derek_Hu,

The documentation of the ORDER= option explains it:

FORMATTED

orders values by their formatted (external) values. If no format has been assigned to a class variable, then the default format, BEST12., is used.

The BEST12. formatted values of 0.1, 2 and 3 are:

------------
         0.1
           2
           3
------------

So they are right-aligned in a 12-character field and the '0' (ASCII code 48) of '0.1' at character position 10 is sorted after the leading blank ' ' (ASCII code 32) at the same position for the integer values.

Use ORDER=INTERNAL to obtain the desired numerical sort order.

[Derek_Hu]

Thanks. That means ORDER=FORMATTED is equal to using the PUT function to convert the data into a specified format, instead of keeping the variable as numeric with the appearance of BEST12.. Please correct me if anything is wrong.

[FreelanceReinh]

---

@Derek_Hu wrote:
Thanks. That means ORDER=FORMATTED is equal to using the PUT function to convert the data into a specified format, instead of keeping the variable as numeric with the appearance of BEST12.. Please correct me if anything is wrong.

---

I wouldn't say it is "equal". Using a corresponding character variable created by the PUT function would result in the same sort order. However, this character variable could not be used, say, in arithmetic calculations in a COMPUTE block -- unlike the existing numeric variable, formatted or not.

[Derek_Hu]

Thanks a lot.