BookmarkSubscribeRSS Feed
radha009
Quartz | Level 8

i have the following data, the sort results different from the SAS order by sort. Looks like '-' variable sort first in the SAS code - Please help.

 

Code:

proc sql;
Create table lib.fruits as
select Name, calories,Fat,carb,protein from lib.fruits_data
order by upper(Name) asc;
quit;

 

result:

fruits CaloriesFatCarbsProtein
AH-LP Banana690.417.60.8
AH Banana690.417.60.8
Cantalope270.26.50.7
CANTALOPE/GREEN270.26.50.7
MA-MAS Cherry420.69.70.7
MAC-Cherry420.69.70.7
Peach360.19.40.6
Strawbery240.35.80.5
Watermelon240.35.50.5

 

Expected sort same as excel :

 

fruits CaloriesFatCarbsProtein
AH Banana690.417.60.8
AH-LP Banana690.417.60.8
CANTALOPE/GREEN270.26.50.7
Cantalope270.26.50.7
MAC-Cherry420.69.70.7
MA-MAS Cherry420.69.70.7
Peach360.19.40.6
Strawbery240.35.80.5
Watermelon240.35.50.5
2 REPLIES 2
Tom
Super User Tom
Super User

Why would Excel sort hyphen before space?  Perhaps it is using some strange multi-step logic for sorting?

ballardw
Super User

You can find the ASCII sort order with this code:

data junk;
do i= 32 to 255;
   x= byte(i);
   output;
end;
run;

Characters with position less than 32 are generally not used for printed output.

 

The actual behavior you see might involve a higher order value impersonating a space that appears as a blank in your data.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 786 views
  • 0 likes
  • 3 in conversation