PROC REPORT Order Variable: integer vs. non-integer

[davehalltwp]

Quick question.  I had a simple sort variable in a proc report variable that contained only integers.  But yesterday I had to insert new values, and since there were no integers available for the slot I wanted the new values in, I used 4.5 (it went between existing 4 and existing 5 categories).

PROC REPORT would not put them in the right order.  It was a simple solution, I just renumbered the entire list so that they were all integers again.

But is there any reason why PROC REPORT would not handle an ORDER = sortvariable when sortvariable contains a mix of integers and non-integers?  I feel like I have done that many times without issue.

Thanks.

...dave

[PaigeMiller]

Please show us the code you are using, and the output that is not correct.

[novinosrin]

Hi @davehalltwp  Like @PaigeMiller  Please show your code, part of the output etc. I am intrigued as Proc report generally uses formatted value as the default order. Therefore, I would recommend to use an explicit numeric format to get the desired. Example- 

/*Sample*/
data have;
 do i='a','b';
   do j=1 to 2 by .2;
    output;
   end;
 end;
run;
/*Rearrange in random order*/
proc sql;
 create table _have as
 select *
 from have
 order by i,rand('uniform');
quit;
/*Test the report*/
proc report data=_have;
 columns i j;
 define i/order;
 define j/order format=8.1;
run;

i	j
a	1.0
	1.2
	1.4
	1.6
	1.8
	2.0
b	1.0
	1.2
	1.4
	1.6
	1.8
	2.0

[ballardw]

What was the format assigned to the order variable?

And if you have multiple order variables there might be conflicts.

Which is just one reason to show the entire procedure code.

[davehalltwp]

Yes, these are all good points, and there could be other factors in play. I will supply code and output when I can.

But still, when I add the renumbering to integers-only immediately prior to the PROC REPORT, and then it works, it seems to me that the integer/non-integer mix is causing this. And I can't understand that.

Thanks all...

[davehalltwp]

I forgot to mention that there is no format associated with the sort variable. It's there strictly for ordering, and is a NOPRINT.

[ballardw]

---

@davehalltwp wrote:
I forgot to mention that there is no format associated with the sort variable. It's there strictly for ordering, and is a NOPRINT.

---

SAS variables ALWAYS have a format. If you don't assign one SAS does. For numeric values usually something like BEST12. but it is always better to check/ verify.

Formats can affect grouping behavior, is designed that way, and is a very powerful tool used correctly.

And example:

data example;
   input x;
datalines;
1.1
1.2
1.3
;

proc freq data=example;
   title "Default format";
   tables x;
run;

proc freq data=example;
   title "Overiding default format";
   tables x;
   format x f4.;
run;

So if your variable had a format assigned that typically doesn't display decimal values it would have the affect you describe.

You might also share your "renumbering" code.

[FreelanceReinh · Accepted solution]

---

@davehalltwp wrote:
I forgot to mention that there is no format associated with the sort variable. It's there strictly for ordering, and is a NOPRINT.

---

The documentation of the ORDER= option says that "FORMATTED" is the default and: "If no format has been assigned to a class variable, then the default format, BEST12., is used." Which explains why 4.5 is sorted after 5 (and many other values greater than 4.5):

1719  data _null_;
1720  do x=4 to 5 by .5;
1721   put x best12.;
1722  end;
1723  run;

           4
         4.5
           5

Using order=internal in the DEFINE statement would apply the intended numeric order instead.

[davehalltwp]

Wow, thank you, @FreelanceReinhard