turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- PROC SORT NUMERIC AS STRING

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 03:43 PM

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

Accepted Solutions

Solution

04-11-2017
04:24 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 03:55 PM

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...

All Replies

Solution

04-11-2017
04:24 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 03:55 PM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 04:23 PM

thank you. This worked!

format NewID z12. ;

NewID=OldID ;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 03:58 PM

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;
```

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 04:06 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 04:30 PM - edited 04-11-2017 04:34 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

04-11-2017 04:36 PM

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!