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 ;
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"
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"
Thanks!!
Yes, I found that later on that it used "alphabetic" order. Thank you!!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.