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
<quentin_mcmullen@BROWN.EDU> wrote:

>On Fri, 4 Jun 2004 10:22:58 -0400, Walt Davis <walter_davis@UNC.EDU> wrote:
>
>>hi folks,
>>
>>Old-time SAS-Ler who hasn't posted in years.  Also proc sql (and regular
>>sql) neophyte.
>>
>>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
>when
>>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  .
>>aaAHmL  ..
>>AaAhOe  ..
>>aAAiQZ  ..
>>AAAjeB  ..
>>
>>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
>get
>>"order by" to sort this the same way as SAS?
>>
>>Thanks.  A direct reply would be appreciated, but we'll be grateful for
>any
>>help we get.
>
>Hi Walter,
>
>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
>sort order.
>
>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.
>
>data a;
>  input var $6.;
>  cards;
>aAACPb
>aAaEbz
>aAAgoW
>aaAHmL
>AaAhOe
>aAAiQZ
>AAAjeB
>;
>
>proc sql;
>  select var from a
>    order by var
>  ;
>quit;
>
>
>Hope that helps,
>--Quentin