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
thank you. This worked!
format NewID z12. ;
NewID=OldID ;
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;
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.
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
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.