BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
davehalltwp
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
FreelanceReinh
Jade | Level 19

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

View solution in original post

8 REPLIES 8
PaigeMiller
Diamond | Level 26

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

--
Paige Miller
novinosrin
Tourmaline | Level 20

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
Super User

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
Quartz | Level 8
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
Quartz | Level 8
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
Super User

@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
Jade | Level 19

@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
Quartz | Level 8
Wow, thank you, @FreelanceReinhard

SAS Innovate 2025: Register Now

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!

What is Bayesian Analysis?

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 8 replies
  • 1419 views
  • 5 likes
  • 5 in conversation