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
@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.
Please show us the code you are using, and the output that is not correct.
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 |
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 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.
@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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.