|Date: ||Tue, 8 Jun 2004 15:11:29 +1000|
|Reply-To: ||Scott <foo_yahoo_com_au@CRONKITE.CC.UGA.EDU>|
|Sender: ||"SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>|
|From: ||Scott <foo_yahoo_com_au@CRONKITE.CC.UGA.EDU>|
|Subject: ||Re: proc sort vs. order by with character variables|
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:
I'd suggest using the COLLATE clause (Method 2) as your first option.
P.S.: I lived in NC for 9 years. Wonderful memories. Go 'Heels :-)
"Walt Davis" <firstname.lastname@example.org> wrote in message
> I responded to Quentin privately, but let me sum that post up quickly
> 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
> 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
> > On Fri, 4 Jun 2004 10:22:58 -0400, Walt Davis <walter_davis@UNC.EDU>
> > >hi folks,
> > >
> > >Old-time SAS-Ler who hasn't posted in years. Also proc sql (and
> > >sql) neophyte.
> > >
> > >So here's the problem. We have a character ID variable which is a mix
> > >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
> > >
> > >Is there a SAS option, a PROC SQL option, or an SQL "order by" option
> > 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
> > 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
> > 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
> > 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