Sort by 2nd character of variable

[polpel]

Hello,

Basically I have a series of ranges, and I'd like to sort them in ascending order, except the ascii table is made so that when I use PROC SORT it's all in order except for one observation.

Is there are way to sort the table by the 2nd character of the observations.

Here's the series of ranges that are put as character variable after I've sorted the table. The error is quite explicit:

>500%
[0%;100%]
]100%;150%]
]150%;200%]
]200%;250%]
]250%;300%]
]300%;350%]
]350%;400%]
]450%;500%]

As you can see, the +500% range is at the top of the table when it should be at the bottom...

[PeterClemmensen]

This seems to be part of a larger problem. Why do you want to do this sort? 

[polpel]

I need it sorted so that I can add a cumulated percent column later on, and if I start this cum-percent with the 500+ range my export will be wrong

[polpel]

I any other language like C, this would be easy, I would just send the strings by the address of the 2nd character (&string[1])

[data_null__]

It is easy see my answer.

[FreelanceReinh]

---

@polpel wrote:
I any other language like C, this would be easy, I would just send the strings by the address of the 2nd character (&string[1])

---

It's easy in SAS, too, even if none of the specialist options of PROC SORT (thanks, @data_null__, for the reminder!) was applicable.

Example:

data have;
input string $;
cards;
Wcar
Xdog
Yapple
Zball
;

proc sql;
select * from have
order by substr(string,2);
quit;

Result:

string
--------
Yapple
Zball
Wcar
Xdog

[PaigeMiller]

---

@polpel wrote:

Hello,

Basically I have a series of ranges, and I'd like to sort them in ascending order, except the ascii table is made so that when I use PROC SORT it's all in order except for one observation.

Is there are way to sort the table by the 2nd character of the observations.

Here's the series of ranges that are put as character variable after I've sorted the table. The error is quite explicit:

>500%
[0%;100%]
]100%;150%]
]150%;200%]
]200%;250%]
]250%;300%]
]300%;350%]
]350%;400%]
]450%;500%]

As you can see, the +500% range is at the top of the table when it should be at the bottom...

---

Agreeing with @PeterClemmensen, the data should not be stored this way, and this is indicative of some other problem (unless what we are seeing is formatted data). Storing data this way just makes your life difficult, as you see. Better to store ranges by a single numeric value, and format them to appear the way you want.

Assuming this is unformatted data, you could use the SCAN function to extract the first number (ignoring the brackets, percent signs, greater than signs, etc.) and once you have done that, the sort ought to be simple.

[polpel]

I'm just formatting the data for when I export to excel.
In Excel I search the actual strings, so I need my data stored this way

[PaigeMiller]

---

@polpel wrote:
I'm just formatting the data for when I export to excel.
In Excel I search the actual strings, so I need my data stored this way

---

I disagree. It is much easier to store data as actual numbers, and then do things in SAS with the actual numbers, and then use SAS PROC FORMAT to make them to appear the way you want. You can send the data to Excel in whatever appearance you want.

[data_null__ · Accepted solution]

 Does this give the result you expect?

data range;
   input range $ 1-16;
   cards4;
>500%
[0%;100%]
]100%;150%]
]150%;200%]
]200%;250%]
]250%;300%]
]300%;350%]
]350%;400%]
]450%;500%]
;;;;
   run;
proc print;
   run;
proc sort data=range sortseq=linguistic(NUMERIC_COLLATION=ON);
   by range;
   run;
proc print;
   run;

[novinosrin]

Guru @data_null__   Marvelous. Great! Could you offer a couple of notes to how that works when you have a moment plz. I can add that to my notes. Thank you in advance!

[data_null__]

All I know is this from the documentation.

NUMERIC_COLLATION=

orders integer values within the text by the numeric value instead of characters used to represent the numbers.

[hashman]

@novinosrin, @data_null__ :

A very nice and pretty complete compendium of proc SORT's features of this sort has been put together by Derek Morgan:

https://www.sas.com/content/dam/SAS/support/en/sas-global-forum-proceedings/2018/2773-2018.pdf 

Much better and more systematical than wading through the docs. And it was masterfully presented, too (I did attend the talk).

Kind regards

Paul D.  

[polpel]

it's perfect! thanks!!!