DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 381
Accepted Solution

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

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;

Desired.png

Thank you.


Accepted Solutions
Solution
‎01-05-2016 08:07 AM
Super User
Posts: 9,691

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

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;

View solution in original post


All Replies
Super User
Posts: 17,948

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

Trusted Advisor
Posts: 1,131

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

Please try with sortseq option in proc sort

 

Proc Sort Data=Have sortseq=linguistic(NUMERIC_COLLATION=on);
By ReferenceID;
Run;
Thanks,
Jag
Solution
‎01-05-2016 08:07 AM
Super User
Posts: 9,691

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

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;
Super User
Super User
Posts: 7,430

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 250 views
  • 6 likes
  • 5 in conversation