Sorting a character variable with numeric values

[Mathis1]

Hello, 
I'm trying to sort the variable "Variable" that is a character variable but containing some numeric values that i wish to be sorted. 

I know about this option : 

proc sort data=HAVE SORTSEQ =LINGUISTIC /*(NUMERIC_COLLATION=ON)*/;
by Variable;
run;

but it doesn't work on sas 9.1 which i'm working on. 

Do you have any idea how to proceed ?

Thank you by advance 🙂

[ballardw]

If you have a fixed order and fixed list of values you might consider adding a numeric variable that has the sort order and possibly creating a custom format to display the desired text depending on how you currently get the variable text. Then sort on the numeric version or in report procedures tell the procedure to use the unformatted value to control order.

You might even create an informat to do the conversion to numeric if this exact issue is frequent for the same values.

[FreelanceReinh · Accepted solution]

Hello @Mathis1,

In PROC SQL you can define a suitable sort key "on the fly" in an ORDER BY clause.

Example:

data have;
input stat $20.;
cards;
100% Max
99%
95%
90%
75% Q3
50% Median
25% Q1
10%
5%
1%
0% Min
Interquartile Range
Range
Mean
Variance
Std Deviation
;

proc sql;
create table want as
select *
from have
order by input(compress(scan(stat,1),,'kd'),3.), find('MSVRI',char(stat,1));
quit;

In the second expression in the ORDER BY clause you can define the sort order of the five non-quantile statistics using their initial letters: Just permute the string in the first argument of the FIND function. By exchanging the two expressions ("input(...)" and "find(...)") you can sort the quantiles first if you like.

Edit: I'm not quite sure if all features used in the ORDER BY clause were already available in v9.1, but you could definitely replace them by others to achieve what you want.