BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

I am trying to sort numbers as string but they are not sorting in the same order when comparing to an Excel file. In excel it first orders by the number of digits and then by the value of the number. In Sas it orders by the first value of the number regardless as to how many digits. I tried using all the SORTSEQ options with SAS and cannot replicate Excel order.  Does anyone know if there is an easy way to order by number value within digit size of the number but maintain the string?

 

In Excel:

482731
482731
482731
482856
482856
482856
2502546
2502546

2539454
2539454
2539454

 

In SAS:

 

2502546
2502546

2539454
2539454
2539454

482731
482731
482731
482856
482856
482856

1 ACCEPTED SOLUTION

Accepted Solutions
me55
Quartz | Level 8
i believe what is happening is the data is being absolute positionally sorted and left aligned. so in sas it is sorting by 2 smaller than 4 even though the numbers are longer on the right. what i usually do is add another field with leading zeroes and then sort by that. so then 0 is less than 2...

View solution in original post

6 REPLIES 6
me55
Quartz | Level 8
i believe what is happening is the data is being absolute positionally sorted and left aligned. so in sas it is sorting by 2 smaller than 4 even though the numbers are longer on the right. what i usually do is add another field with leading zeroes and then sort by that. so then 0 is less than 2...
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

thank you. This worked!

 

format NewID z12.  ;

NewID=OldID ;

 

 

 

collinelliot
Barite | Level 11

You could use SQL to sort on the numeric conversion of the text value without having to create a new variable:

 

proc sql;
    CREATE TABLE sorted AS
    SELECT *
    FROM txtNums
    ORDER BY input(num, best.);
quit;
CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

Thanks but I'm looking for the Excel sort order which sorts numeric value ascending based on number of digits. A 6-digit number starting with a higher value is before an 7 digit number starting with a lower number. 

BrunoMueller
SAS Super FREQ

Hi

 

Have a look at the SORTSEQ=linguistic... option. It will do what you need. See example below.

 

Also have a look at this paper: https://support.sas.com/resources/papers/linguistic_collation.pdf

 

data have;
  input someString : $32.;
  cards;
482731
482731
482731
2502546
2502546
2539454
2539454
2539454
482856
482856
482856
;

proc sort data=have out=want sortseq=linguistic(numeric_collation=on);
  by someString;
run;

Bruno

CP2
Pyrite | Level 9 CP2
Pyrite | Level 9

Actually, that works too. I thought I tried that unsuccesfully but this time it worked. I might have been looking at the wrong variable at the time. 

Thanks!

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 4418 views
  • 1 like
  • 4 in conversation