topic Re: sorting char id in SAS Programming

sorting char id

znhnm — Wed, 28 Dec 2022 21:27:40 GMT

I have a char (32) type id column where I would like to sort but the sorting is not done properly so I created a new numeric_id column with Num (8) type. However, the sorting is still not working. To some point, it is correct but then I have a wrong order. For example:
....
956257
957515
121
11737
....

Why do 121 is coming after than 957515 in an ascending sorting and how do I make a proper sorting for this char type id?

Re: sorting char id

SASKiwi — Wed, 28 Dec 2022 21:35:18 GMT

Please run this code on your table and post the results. If your data is sorted by ID then it will report this:

proc contents data = MyTable;
run;

Re: sorting char id

znhnm — Wed, 28 Dec 2022 22:28:30 GMT

Thank you.
I have:
Sorted NO

as part of the output. How can I understand what is wrong with the sorting?

Re: sorting char id

SASKiwi — Wed, 28 Dec 2022 22:37:27 GMT

It means your sort didn't work. Try rerunning your PROC SORT step then check your SAS log for any errors. If there are no errors, run the PROC CONTENTS again to confirm the sort order is correct.

Re: sorting char id

Astounding — Wed, 28 Dec 2022 22:40:19 GMT

One thing to check for is characters in your data that you can't see. The simplest case, for example, would be leading blanks, which would affect the sorted order. A quick check:

data leading_blank;
   set have;
   if varname ne left(varname);
run;

A second possibility might be other "leading" characters such as a tab character. That's a little harder to check for, but not much:

data something_else;
   set have;
   if varname ne compress(varname,, 'kd');
run;

In this DATA step, the COMPRESS function keeps all digits and removes all other characters.

Re: sorting char id

znhnm — Wed, 28 Dec 2022 23:22:22 GMT

Hi, thank you so much for the suggestion. I tries it both and I have zero observations in the output dataset. I think it means I don't have characters in my data that I can't see or a leading character, right? Please let me know if you'd have any other suggestions.

Re: sorting char id

SASKiwi — Wed, 28 Dec 2022 23:48:10 GMT

See my last post regarding rerunning PROC SORT. Please post your complete SAS log including any notes and errors. If there is no evidence we would be just guessing what is going wrong.

Re: sorting char id

Patrick — Thu, 29 Dec 2022 01:00:21 GMT

With a character var sorting will use the string using one character after the next starting from the left side. If you have two strings like "121" and "11737" then the second character is "1" and "2" respectively and though "11737" will sort before "121".

If you read the strings into a numerical variable then they should sort numerical and as you want them to. If that's not the case then either something with your conversion to numeric goes wrong or you don't sort the right table.

You can take below code as your "template". The line in comment using a compress() function removes any non-digit character from your source string. That's a bit brute force but should help you to identify rows where you've got more than digits only in your source.

data have;
  infile datalines truncover;
  input c_var $32.;
  n_var=input(c_var,best32.);
/*  n_var2=input(compress(c_var,,'kd'),best32.);*/
datalines;
956257
957515
121
11737
;

proc sort data=have out=want;
  by n_var;
run;

proc print data=want;
run;

Re: sorting char id

Aku — Thu, 29 Dec 2022 11:00:16 GMT

You can do sorting using sortseq option in Proc Sort;

proc sort data=have out=want sortseq=linguistic(Numeric_Collation=ON);
by c_var ;
run;

Re: sorting char id

Kurt_Bremser — Thu, 29 Dec 2022 23:55:58 GMT

Please post the complete(!!!) log of both the step where you create the numeric variable and the PROC SORT. Use this button to pist the log text: