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.
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;
Please try with sortseq option in proc sort
Proc Sort Data=Have sortseq=linguistic(NUMERIC_COLLATION=on);
By ReferenceID;
Run;
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;
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.
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.