topic PROC REPORT with default order option creates unwanted order in SAS Procedures

PROC REPORT with default order option creates unwanted order

Derek_Hu — Fri, 10 May 2024 07:32:15 GMT

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;

Re: PROC REPORT with default order option creates unwanted order

FreelanceReinh — Fri, 10 May 2024 08:02:27 GMT

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.

Re: PROC REPORT with default order option creates unwanted order

Derek_Hu — Fri, 10 May 2024 08:40:39 GMT

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.

Re: PROC REPORT with default order option creates unwanted order

FreelanceReinh — Fri, 10 May 2024 09:12:31 GMT

@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.

Re: PROC REPORT with default order option creates unwanted order

Derek_Hu — Fri, 10 May 2024 16:27:00 GMT

Thanks a lot.