How to - Sort Variable Which Includes Both Numeric and Character Value

[turcay]

Hello everyone,

I'm trying to sort the variable ReferenceID. This variable includes both character and numeric datas. Type of ReferenceID is character. When I sort this variable , double-digit values come before one-digit values. I would like to sort this variable according to numeric values. I shared a basic data as below and desired output. I'm not sure is it possible. Does anybody has knowledge about this case ? 

Data Have;
Length ReferenceID $ 10;
Infile Datalines Missover;
Input ReferenceID;
Datalines;
Tur1
Tur2
Tur10
Tur11
Tur5
Tur6
Tur9
Tur14
Tur15
Tur7
Tur8
Tur12
Tur13
Tur3
Tur4
;
Run;

Proc Sort Data=Have;
By ReferenceID;
Run;

Thank you.

[Reeza]

https://communities.sas.com/t5/General-SAS-Programming/Question-about-the-order-of-memname-and-indsn...

[Jagadishkatam]

Please try with sortseq option in proc sort

Proc Sort Data=Have sortseq=linguistic(NUMERIC_COLLATION=on);
By ReferenceID;
Run;

[Ksharp · Accepted solution]

I like SQL way better.

Data Have;
Length ReferenceID $ 10;
Infile Datalines Missover;
Input ReferenceID;
Datalines;
Tur1
Tur2
Tur10
Tur11
Tur5
Tur6
Tur9
Tur14
Tur15
Tur7
Tur8
Tur12
Tur13
Tur3
Tur4
;
Run;
proc sql;
 create table want as 
  select *
   from have
    order by input(compress(ReferenceID,,'kd'),best.);
run;

[RW9]

You may want to consider another reference id in this case, the Tur doesn't actually add anything to the data, the id is just the numeric part.  If you absolutely have to have a character field then padd to a fixed length.  It just makes your programming easier, for instance CDISC standards a subject identifier would take the form of:

<study>_<site><subject>

Fixed width for each, and it makes it simple to extract each part (although each part is available separately anyways for ease of processing), and sort etc.