Proc Rank

[David_Billa]

I'd like to understand why I got the two different rank for the same SHIPPED_YTD_GSV. I want the same rank 2247 to be displayed for both the records. 

MATERIAL	SHIPPED_YTD_GSV	SHIPPED_YTD_GSV_RANK
3800020398	31147.2	2247
3800025512	31147.2	2248

Code which I used is,

proc sql;
create table work.tst_ytd_gsv as
select         MATERIAL,
            sum(SHIPPED_GSV) as SHIPPED_YTD_GSV
from        work.all_data
where        FISC_YR = 2021
group by    MATERIAL;
quit;

proc rank data=work.tst_ytd_gsv out=work.tst_ytd_gsv_rank ties=dense descending;
    var SHIPPED_YTD_GSV;
    ranks SHIPPED_YTD_GSV_RANK;
run;

I did applied the format 12.2 to the variable SHIPPED_YTD_GSV but still the same issue.

[PeterClemmensen]

Because SHIPPED_YTD_GSV are not exactly equal. It is not enough to apply some format to the values, the internal 8 byte value is still the same.

One way is to round the SHIPPED_YTD_GSV value to 2 integers using the Round Function.

[David_Billa]

@PeterClemmensen @JosvanderVelden  There is no format or length has been applied to the variable SHIPPED_YTD_GSV. Then how the values will differ internally?

Even I did checked the source variable which creates SHIPPED_YTD_GSV and there also no format applied.

How can we see the actual value of SHIPPED_YTD_GSV. without any rounding? I want to see the difference between the two values as I shown in post.

[PeterClemmensen]

If you want to verify that there is a difference between the two computed values, try applying the Hex16. Format in the SQL step and check the values. Do they differ?

[PaigeMiller]

---

@David_Billa wrote:

@PeterClemmensen @JosvanderVelden  There is no format or length has been applied to the variable SHIPPED_YTD_GSV. Then how the values will differ internally?

---

It's called round-off error, or machine precision, or machine epsilon. Some numbers cannot be represented exactly in binary digital computers.

https://en.wikipedia.org/wiki/Machine_epsilon

How can we see the actual value of SHIPPED_YTD_GSV. without any rounding? I want to see the difference between the two values as I shown in post.

Assign format best32. to that variable.

There is no format or length has been applied to the variable SHIPPED_YTD_GSV. Then how the values will differ internally?

Even I did checked the source variable which creates SHIPPED_YTD_GSV and there also no format applied.

You did not try the suggestion to round off the variable to 2 digits.

[JosvanderVelden]

The unformatted values in the dataset must be different.
Proc rank does show the same rank if the values are exactly the same.
See the example1 from the documentation: https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n18s1uw2tqvtxdn1chay1qq1jke6.htm

[JosvanderVelden]

If you want to get background information about numeric precision with SAS please visit: https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lepg/p0dv87zb3bnse6n1mqo360be70qr.htm

[FreelanceReinh]

Just to add a specific consideration for your example 31147.2:

Randomly pick a number x from the 3,114,719-element set {0.01, 0.02, 0.03, ..., 31147,18, 31147.19} and compute y=31147.2-x by hand or with a pocket calculator.

What is the probability that, "surprisingly,"  x+y ne 31147.2 in SAS 9.4M5 under Windows? Have a guess.

Spoiler

Answer: 37.33%.

309   %let n=3114720;
310
311   data _null_;
312   do k=1 to &n-1;
313     u+(k/100+(&n-k)/100 ne &n/100);
314   end;
315   p=u/(k-1);
316   put p= percent8.2;
317   run;

p=37.33%
NOTE: DATA statement used (Total process time):
      real time           0.16 seconds
      cpu time            0.15 seconds


318
319   /* Examples: */
320
321   data _null_;
322   s0=31147.2;
323   s1=10001.35+21145.85;
324   s2=10001.36+21145.84;
325   s3=10001.37+21145.83;
326   put (s:)(=hex16./);
327   run;

s0=40DE6ACCCCCCCCCD
s1=40DE6ACCCCCCCCCC
s2=40DE6ACCCCCCCCCD
s3=40DE6ACCCCCCCCCE

[Reeza]

proc sql;
create table work.tst_ytd_gsv as
select         MATERIAL,
            round(sum(SHIPPED_GSV), 0.01) as SHIPPED_YTD_GSV
from        work.all_data
where        FISC_YR = 2021
group by    MATERIAL;
quit;

proc rank data=work.tst_ytd_gsv out=work.tst_ytd_gsv_rank ties=dense descending;
    var SHIPPED_YTD_GSV;
    ranks SHIPPED_YTD_GSV_RANK;
run;

What happens if you do it as above? 

Note that you say you round to 12.2 -> which is two decimal places, but show only one decimal place. The second decimal is likely different.