topic Re: How to - Sort Variable Which Includes Both Numeric and Character Value in SAS Programming

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

turcay — Tue, 05 Jan 2016 00:18:21 GMT

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.

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

Reeza — Tue, 05 Jan 2016 00:43:12 GMT

https://communities.sas.com/t5/General-SAS-Programming/Question-about-the-order-of-memname-and-indsname/m-p/238655#M34633

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

Jagadishkatam — Tue, 05 Jan 2016 00:46:36 GMT

Please try with sortseq option in proc sort

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

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

Ksharp — Tue, 05 Jan 2016 05:20:21 GMT

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;

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

RW9 — Tue, 05 Jan 2016 09:48:10 GMT

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:

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.