topic Re: The order of Average value when missing data is involved in SAS Programming

The order of Average value when missing data is involved

jc3992 — Sun, 09 Dec 2018 17:07:06 GMT

Hello everyone,

I wonder may I ask why in the output of the code below,

the average value of "Jones" which is 100,

would be ordered higher than that for "Smith", which is 400.

Thank you very much!

data work.one;
  input Rep $ Cost;
  cards;
  Smith 200
  Smith 400
  Jones 100
  Smith 600
  Jones 100
  Chang 400
  Jones .
  Chang 600
  ;

proc sql;
  select Rep, avg(Cost) as Average
  from one
  group by Rep
  having Average ge 100
  order by Rep
  ;
  quit
  ;

Re: The order of Average value when missing data is involved

mkeintz — Sun, 09 Dec 2018 17:33:31 GMT

You have the expression

"order by rep"

which tells sql to sequence results sorted by rep (change, jones, smith).

But even if you drop this expression, you also have "group by rep". proc sql needs a way to establish those groups, and it seems that sql is using alphabetic order to determine how to assign an individual row to a group.

So you need to order by the new variable average.

"order by average"

or
"order by average descending"

Re: The order of Average value when missing data is involved

jc3992 — Sun, 09 Dec 2018 18:53:56 GMT

Thanks!!

Yes, I found that later on that it used "alphabetic" order. Thank you!!!