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. > > >Walter R Davis >Research Director >Center for Community Capitalism >Kenan Institute of Private Enterprise >CB# 3440 >University of North Carolina >Chapel Hill, NC 27599 >ph: (919) 843-6288 >fax: (919) 962-8202 >email: walter_davis@unc.edu >web: www.ccc.unc.edu

Hi Walt

both proc sort and proc sql have an option which can be used to achieve case insensitive sorting, when needed. Obviously, the default is case insensitive in sas, but normally, sas would pass as much processing as possible to a server, so order out of ms-sql depends on those defaults. The case insensitive option is proc sort sortseq= <name> proc sql sortseq= <name> valid values for <name> are any trantab in sashelp.host or sashelp.locale

here is a subset +FSVIEW: WORK.VCATALG (B)-------------------------------------Where ...+ | libname memname objname objtype objdesc | | | | SASHELP LOCALE SELT_UCS TRANTAB SAS OEM Mainframe Upcase | | SASHELP LOCALE SLT1_UCS TRANTAB SAS OEM Latin1 Upcase | | SASHELP LOCALE SLT2_UCS TRANTAB SAS OEM Latin2 Upcase | | SASHELP LOCALE THAI_UCS TRANTAB ISO 8859/11-thai Upcase | | SASHELP LOCALE WARA_UCS TRANTAB Win Cp1256-arabic Upcase | | SASHELP LOCALE WGRK_UCS TRANTAB Win Cp1253-greek Upcase | | SASHELP LOCALE WHEB_UCS TRANTAB Win Cp1255-hebrew Upcase | | SASHELP LOCALE WLT1_UCS TRANTAB Win Cp1252-latin1 Upcase | | SASHELP LOCALE WLT2_UCS TRANTAB Win Cp1250-latin2 Upcase | | SASHELP LOCALE WTUR_UCS TRANTAB Win Cp1254-turkish Upcase | | SASHELP LOCALE YPCS_UCS TRANTAB Trad Chinese cp1114-Zt UCS Table | | | +-----------------------------------------------------------------------+ The best value is probably WLT1_UCS but depends on your windows environment and ms-sql

SORTSEQ= is also a system option