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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.