PROC SORT does not sort as expected

[Zatere]

Hi I hope you all are well.

I am having the following issue.

I ve got a table that contains ProductID, Sale_Value, Quantity on which I take the sum of Sale_Value and Quantity on ProductID to create a table as below:

ProductID	Sum of Sale_Value	Sum of Quantity
1	5687.35	68
2	326.42	73
3	4478.55	29
4	2967.39	12
5	4478.55	88

Later I sort the data as follows:

PROC SORT DATA = have;
BY DESCENDING SUM_OF_SALE_VALUE DESCENDING SUM_OF_QUANTITY;
RUN: 

The expected output would the below:

ProductID	Sum of Sale_Value	Sum of Quantity
1	5687.35	68
5	4478.55	88
3	4478.55	29
4	2967.39	12
2	326.42	73

However, I am getting the below:

ProductID	Sum of Sale_Value	Sum of Quantity
1	5687.35	68
5	4478.55	29
3	4478.55	88
4	2967.39	12
2	326.42	73

I have checked the raw data and indeed the totals for ProductIDs 5 and 3 are as shown above, so I rule out the possibility of hidden rounding numbers.

For some reason that I cannot understand the PROC SORT does not give the desired output.

Any ideas please?

Thanks

[novinosrin]

The following test produces the expected output. Please check dataset you running proc sort against and the rounding errors once again -1. at raw data 2. SAS dataset 

data have;
input ProductID	SUM_OF_SALE_VALUE	SUM_OF_QUANTITY;
cards;
1	5687.35	68
2	326.42	73
3	4478.55	29
4	2967.39	12
5	4478.55	88
;

PROC SORT DATA = have out=want;
BY DESCENDING SUM_OF_SALE_VALUE DESCENDING SUM_OF_QUANTITY;
RUN;

proc print noobs;run;

ProductID	SUM_OF_SALE_VALUE	SUM_OF_QUANTITY
1	5687.35	68
5	4478.55	88
3	4478.55	29
4	2967.39	12
2	326.42	73

[Reeza]

Check your SORTSEQ option perhaps?

Otherwise, your code does work as expected on my machine so something else is the issue.

This should return a blank. 

proc options option=sortseq;
run;

---

@Zatere wrote:

Hi I hope you all are well.

I am having the following issue.

I ve got a table that contains ProductID, Sale_Value, Quantity on which I take the sum of Sale_Value and Quantity on ProductID to create a table as below:

ProductID	Sum of Sale_Value	Sum of Quantity
1	5687.35	68
2	326.42	73
3	4478.55	29
4	2967.39	12
5	4478.55	88

Later I sort the data as follows:

PROC SORT DATA = have;
BY DESCENDING SUM_OF_SALE_VALUE DESCENDING SUM_OF_QUANTITY;
RUN: 

The expected output would the below:

ProductID	Sum of Sale_Value	Sum of Quantity
1	5687.35	68
5	4478.55	88
3	4478.55	29
4	2967.39	12
2	326.42	73

However, I am getting the below:

ProductID	Sum of Sale_Value	Sum of Quantity
1	5687.35	68
5	4478.55	29
3	4478.55	88
4	2967.39	12
2	326.42	73

I have checked the raw data and indeed the totals for ProductIDs 5 and 3 are as shown above, so I rule out the possibility of hidden rounding numbers.

For some reason that I cannot understand the PROC SORT does not give the desired output.

Any ideas please?

Thanks

---

[ballardw]

Perhaps your value for SUM_OF_SALE_VALUE has a small decimal value larger than is being displayed. This is quite likely if your format is something like an F10.2 format and the value was calculated from a number of values.

You can test my hypothesis by printing the result using a format like BEST32.

data example;
   input x y ;
   format x 8.2;
datalines;
4478.5400001  29
5687.35   68
4478.54  88
;
proc sort data=example;
  by descending x descending y;
run;

proc print data=example;
title 'Default 8.2 format';
run;
proc print data=example;
title 'Best32. format';
format x best32.;
run;title;

If you discover this is the case then ROUND the variable in the data set before sorting. 

data tosort;
   set example;
   x=round(x, 0.01);
run;

[Kurt_Bremser]

Do 

SUM_OF_SALE_VALUE = round(SUM_OF_SALE_VALUE,.01);

in a data step before you sort.

[ChrisNZ]

Your data probably looks something like:

ProductID	Sum of Sale_Value	Sum of Quantity
1	5687.35	68
5	4478.550000001	29
3	4478.55	88
4	2967.39	12
2	326.42	73

Clean it by rounding it, as mentioned.