Date: Fri, 4 Jun 2004 12:49:51 -0400
Reply-To: Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Howard Schreier <Howard_Schreier@ITA.DOC.GOV>
Subject: Re: proc sort vs. order by with character variables
I was going to suggest
order by put(id,$hex12.)
but Quentin's research suggests that's not necessary if the processing is
done by PROC SQL.
On Fri, 4 Jun 2004 12:36:46 -0400, Quentin McMullen
>On Fri, 4 Jun 2004 10:22:58 -0400, Walt Davis <walter_davis@UNC.EDU> wrote:
>>Old-time SAS-Ler who hasn't posted in years. Also proc sql (and regular
>>So here's the problem. We have a character ID variable which is a mix of
>>upper and lowercase values. PROC SORT is of course case-sensitive but
>>we use an ORDER BY statement in PROC SQL, the sort doesn't appear to be
>>case-sensitive. Here's a brief example:
>>SQL "order by" proc sort
>>aAACPb .. AAAjeB .
>>aAaEbz .. AAAnqW .
>>aAAgoW .. AAAzhY .
>>If it matters, we're running SAS v. 8 and Microsoft SQL Server. I did
>>search SAS tech support but didn't find anything on this specific problem
>>Is there a SAS option, a PROC SQL option, or an SQL "order by" option to
>>"order by" to sort this the same way as SAS?
>>Thanks. A direct reply would be appreciated, but we'll be grateful for
>>help we get.
>One of the neat things about SQL is that you can use it with another
>database, which sounds like what you are doing (pulling from SQL server).
>One of things to keep track of when you do this is, which program is
>running the SQL code (i.e. is SAS running SQL code, or is SAS passing the
>SQL code to SQL server, so M$ runs the code?).
>In below example, it shows (I think) that SAS SQL respects case when it
>orders, just like SAS sort. Consistency is good. : ) My guess is, your
>query is passed to SQL Server, which may have its own rules/options for
>So I thin you either need to look into SQL Sever sort order stuff, or find
>a way to make SAS execute the SQL code.
> input var $6.;
> select var from a
> order by var
>Hope that helps,