topic Re: Proc sort didn't properly sort in SAS Procedures

Proc sort didn't properly sort

dupp99 — Wed, 04 Dec 2019 23:03:50 GMT

For example, the unsorted data set looks like:

x z

21 AL

22 ZA

9 BC

19 AL

21 BC

23 AL

9 ZA

11 BC

I did

proc sort data=data out=sorted ;
by z x;
run;

and tried this too

proc sort data=data out=sorted ;
by z;
run;

But what I got looks like:

x z

19 AL

23 AL

9 BC

11 BC

9 ZA

22 ZA

21 AL

21 BC

Why is the bottom two lines not sorted properly? Help!

Re: Proc sort didn't properly sort

ballardw — Wed, 04 Dec 2019 23:16:09 GMT

@dupp99 wrote:

For example, the unsorted data set looks like:

x z

21 AL

22 ZA

9 BC

19 AL

21 BC

23 AL

9 ZA

11 BC

I did
proc sort data=data out=sorted ;
by z x;
run;
and tried this too
proc sort data=data out=sorted ;
by z;
run;
But what I got looks like:

x z

19 AL

23 AL

9 BC

11 BC

9 ZA

22 ZA

21 AL

21 BC

Why is the bottom two lines not sorted properly? Help!

Most likely cause in a character that you can't see.

Also note that posts in the main message window here will remove lots of white space or blank type information so what you posted is likely not what you actually have in your data set.

Try using a data step with:

Put z= z $hex16. ;

And see what the values following the Z=AL look like. I suspect that you will see one of them has different starting value than 414C

You might try posting a data step created from your data set using Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Proc sort didn't properly sort

dupp99 — Wed, 04 Dec 2019 23:24:51 GMT

I tried the Put z= z $hex16. ;

But that doesn't change anything in the dataset.

and the proc sort still got the same output.

Re: Proc sort didn't properly sort

ballardw — Wed, 04 Dec 2019 23:57:30 GMT

@dupp99 wrote:

I tried the Put z= z $hex16. ;

But that doesn't change anything in the dataset.

and the proc sort still got the same output.

I did not imply that it would "fix" anything. I said you could use it to examine the values. Did you see different results for the ones that didn't sort properly? You need to remove something from the data value but depending on what it might be the code needed may differ.

You might have an encoding issue where some of your data is encoded using a different language scheme, you may just have not printable character (or 3). Show the results of the PUT statement. Paste the result from the LOG into a code box opened using the forum's {I} icon.

Re: Proc sort didn't properly sort

Astounding — Thu, 05 Dec 2019 00:03:48 GMT

Another possibility:

Run a PROC CONTENTS on your data and see what you find for the variable Z. It may be that you are looking at formatted values for Z and the data is actually in order by the true (unformatted) value of Z.

Re: Proc sort didn't properly sort

Kurt_Bremser — Thu, 05 Dec 2019 06:48:42 GMT

When I create data out of what you posted, and run the sort:

data have;
input x z $;
datalines;
21         AL
22         ZA
9           BC
19         AL
21         BC
23         AL
9           ZA
11         BC
;

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

proc print data=want noobs;
run;

I get this:

 x    z

21    AL
19    AL
23    AL
 9    BC
21    BC
11    BC
22    ZA
 9    ZA

So it is obvious that what you posted is not your real data, but only what you are shown. Leading blanks, invisible characters, or formats disguise the raw values in your dataset.

So you need to follow Maxim 3 and get to know your data. Run a proc contents to see if any formats are involved, convert the strings to hex display (use the double length of the variable when specifiying the $HEX format) as suggested by @ballardw if no format is in play.

Re: Proc sort didn't properly sort

Ksharp — Thu, 05 Dec 2019 11:41:33 GMT

Try to remove some unprintable characters .

data data;

set data;

z=compress(z,' ','s');

z=compress(z, ,'ka');

run;

proc sort ..........