Hi,

I'm coming in late to this thread, haven't read all the responses, and am in a hurry. So, sorry if this has already been answered, although it looked like the thread digressed somewhat ;-).

Someone pointed me to this URL a while back, which may apply in your case:

http://vyaskn.tripod.com/case_sensitive_search_in_sql_server.htm

I'd suggest using the COLLATE clause (Method 2) as your first option.

HTH, Scott

P.S.: I lived in NC for 9 years. Wonderful memories. Go 'Heels :-)

"Walt Davis" <walter_davis@unc.edu> wrote in message news:40c0b4b4$1_1@news.unc.edu... > I responded to Quentin privately, but let me sum that post up quickly here. > > The data started as SAS and we wrote it to SQL via proc sql. > > The main reason we did this is because these are quite large datasets > (sometimes more than a gig) and sorting them in SAS was taking a lot of > time. (There were also disk space issues involved -- i.e. there was space > on the SQL server but not enough elsewhere on the network). We also think > that data views would come in quite handy. > > So if we need to read the data back into SAS to do the sort (by either proc > sort or proc sql) to prepare the data for SAS by-processing, this is a lot > less beneficial to us. And the views would be of limited use as well. > > (Right now it occurs to me that we could read the data back into SAS, sort > it once, the write that file back out to SQL ... which wouldn't be so bad > until we needed a different sort.) > > > "Quentin McMullen" <quentin_mcmullen@BROWN.EDU> wrote in message > news:200406041636.i54Gakk13933@listserv.cc.uga.edu... > > 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 > >