topic Re: Sort by 2nd character of variable in SAS Programming

Sort by 2nd character of variable

polpel — Thu, 03 Oct 2019 13:53:54 GMT

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...

Re: Sort by 2nd character of variable

PeterClemmensen — Thu, 03 Oct 2019 13:58:19 GMT

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

Re: Sort by 2nd character of variable

polpel — Thu, 03 Oct 2019 13:59:45 GMT

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

Re: Sort by 2nd character of variable

polpel — Thu, 03 Oct 2019 14:02:09 GMT

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])

Re: Sort by 2nd character of variable

PaigeMiller — Thu, 03 Oct 2019 14:07:25 GMT

@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.

Re: Sort by 2nd character of variable

polpel — Thu, 03 Oct 2019 14:09:08 GMT

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

Re: Sort by 2nd character of variable

PaigeMiller — Thu, 03 Oct 2019 14:11:37 GMT

@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.

Re: Sort by 2nd character of variable

data_null__ — Thu, 03 Oct 2019 14:11:36 GMT

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;

Re: Sort by 2nd character of variable

data_null__ — Thu, 03 Oct 2019 14:21:58 GMT

It is easy see my answer.

Re: Sort by 2nd character of variable

novinosrin — Thu, 03 Oct 2019 14:23:58 GMT

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!

Re: Sort by 2nd character of variable

data_null__ — Thu, 03 Oct 2019 14:29:08 GMT

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.

Re: Sort by 2nd character of variable

polpel — Thu, 03 Oct 2019 14:34:24 GMT

it's perfect! thanks!!!

Re: Sort by 2nd character of variable

FreelanceReinh — Thu, 03 Oct 2019 14:57:09 GMT

@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

Re: Sort by 2nd character of variable

hashman — Sat, 05 Oct 2019 16:38:35 GMT

@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.