topic Re: PROC SORT does not sort as expected in SAS Programming

PROC SORT does not sort as expected

Zatere — Fri, 13 Mar 2020 20:13:58 GMT

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

Re: PROC SORT does not sort as expected

novinosrin — Fri, 13 Mar 2020 20:23:05 GMT

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

Re: PROC SORT does not sort as expected

Reeza — Fri, 13 Mar 2020 20:25:14 GMT

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

Re: PROC SORT does not sort as expected

ballardw — Fri, 13 Mar 2020 21:14:20 GMT

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;

Re: PROC SORT does not sort as expected

Kurt_Bremser — Sat, 14 Mar 2020 06:31:00 GMT

SUM_OF_SALE_VALUE = round(SUM_OF_SALE_VALUE,.01);

in a data step before you sort.

Re: PROC SORT does not sort as expected

ChrisNZ — Sat, 14 Mar 2020 10:51:33 GMT

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.