proc sort vs. order by with character variables

Discussion:

(too old to reply)

Walt Davis

2004-06-04 14:22:58 UTC

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: ***@unc.edu
web: www.ccc.unc.edu

Quentin McMullen

2004-06-04 16:36:46 UTC

Permalink

Post by Walt Davis
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

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to be
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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

Walt Davis

2004-06-04 17:43:11 UTC

Permalink

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.)

Post by Quentin McMullen

when

get

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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

Scott

2004-06-08 05:11:29 UTC

Permalink

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 :-)

Post by Walt Davis
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.)

Post by Quentin McMullen

when

get

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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

Howard Schreier

2004-06-04 16:49:51 UTC

Permalink

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

Post by Quentin McMullen

when

get

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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

Pardee, Roy

2004-06-04 17:44:40 UTC

Permalink

I bet Quentin's on the money here. IIRC by default, mssql's collation
order is case-insensitive (tho that's changeable). Your dba should be
able to (dis)confirm the collating sequence for the table in question &
change it if necessary.

I spent an afternoon once wracking my brain as to why the results of a
PROC SQL SELECT DISTINCT were violating a unique key constraint when I
tried to insert them into a mssql table. SAS considered some
differently-cased values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Quentin McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to be
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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

Nathan Fong

2004-06-04 17:58:51 UTC

Permalink

If it turns out the problem must be solved on the SAS side of things (maybe there are SQL Server settings that can't be changed for whatever reason), you could:

1) Simply create an alternate ID that's all-caps. This obviously wouldn't work if, say, two different IDs could be identical except for case. However, if this were true I don't see how you could predict the sort order on the SQL side at all! If it turns out the SQL side is case-insensitive with some kind of uppercase preference, you could sort by the all-caps ID, and then by the case-sensitive ID (unfortunately, might slow you down some).

2) Use PROC TRANTAB and create a custom sort-order that is case-insensitive on the SAS side. If the SQL side is case-insensitive with some kind of uppercase preference, though, I think you will need the two separate ID variables, because you wouldn't be able to give PROC SORT a strict sort-order to stick with.

Walt Davis

2004-06-04 20:12:42 UTC

Permalink

Folks have been giving lots of good suggestions (here and privately) but I'm
not getting anything to work. Here's the sort of thing we might have to
sort.

data fred4; input id $ tp;
cards;
AAajeb 108
AAAjeb 108
AAajeb 109
AAAjeb 109
AAAjeb 111
AAAjeb 110
AAajeb 110
;
run;

and we need to sort by id and tp.

If anyone has any ideas, I'd greatly appreciate it. thanks.

------------------

"Nathan Fong" <***@HOTMAIL.COM> wrote in message news:BAY2-***@hotmail.com...
If it turns out the problem must be solved on the SAS side of things (maybe
there are SQL Server settings that can't be changed for whatever reason),
you could:

1) Simply create an alternate ID that's all-caps. This obviously wouldn't
work if, say, two different IDs could be identical except for case. However,
if this were true I don't see how you could predict the sort order on the
SQL side at all! If it turns out the SQL side is case-insensitive with some
kind of uppercase preference, you could sort by the all-caps ID, and then by
the case-sensitive ID (unfortunately, might slow you down some).

2) Use PROC TRANTAB and create a custom sort-order that is case-insensitive
on the SAS side. If the SQL side is case-insensitive with some kind of
uppercase preference, though, I think you will need the two separate ID
variables, because you wouldn't be able to give PROC SORT a strict
sort-order to stick with.

Sigurd Hermansen

2004-06-04 18:14:36 UTC

Permalink

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating sequence
(if I recall correctly, the upper case letters follow the lower case letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe. Other
possibilities include different installation options, ordering on formatted
values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of people
in a training session. Early on I used random number functions and BYTE()
and COLLATE() functions to produce sequences of variable values. Some in the
group asked why database programmers need to know anything about number
representation, collating sequences, bytes, and binary coding. I guess the
details don't matter that much anymore when MS Windows and SAS hide them so
successfully. Only ever so often, what you see in a display isn't really
what you have stored, and you have to move down a level or two.
Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation order
is case-insensitive (tho that's changeable). Your dba should be able to
(dis)confirm the collating sequence for the table in question & change it if
necessary.

I spent an afternoon once wracking my brain as to why the results of a PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Quentin
McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to be
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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

Sigurd Hermansen

2004-06-04 18:29:43 UTC

Permalink

Walt:
In other posts I have nattered on about the advantages of SAS SQL and Data
step views. You can also define or create views in SQL Server that will
deliver data in an ordered SAS dataset stream for BY group processing. For
example, you can define any number of queries or stored procedures on the
SQL Server side and select the one required. The space required to store the
views is negligible. You can also 'pass thru' a SQL Server query that orders
data and streams them to a SAS dataset or process.
Sig

-----Original Message-----
From: Walt Davis [mailto:***@UNC.EDU]
Sent: Friday, June 04, 2004 1:43 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

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.)

Post by Quentin McMullen

Post by Walt Davis
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

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful
for

any

Post by Walt Davis
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

Peter Crawford

2004-06-04 18:37:22 UTC

Permalink

when

get

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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
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

Choate,

2004-06-04 18:49:56 UTC

Permalink

Fyi -

The ASCII (UNIX and its derivatives, OpenVMS, Windows) and EBCDIC (OS/390,
z/OS) sort sequences are under Concepts: SORT Procedure:
http://support.sas.com/91doc/getDoc/proc.hlp/a002473663.htm (keyboard
characters)

or @

http://www.asciitable.com/ (this list includes the special characters)

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Sigurd
Hermansen
Sent: Friday, June 04, 2004 11:15 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating sequence
(if I recall correctly, the upper case letters follow the lower case letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe. Other
possibilities include different installation options, ordering on formatted
values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of people
in a training session. Early on I used random number functions and BYTE()
and COLLATE() functions to produce sequences of variable values. Some in the
group asked why database programmers need to know anything about number
representation, collating sequences, bytes, and binary coding. I guess the
details don't matter that much anymore when MS Windows and SAS hide them so
successfully. Only ever so often, what you see in a display isn't really
what you have stored, and you have to move down a level or two.
Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation order
is case-insensitive (tho that's changeable). Your dba should be able to
(dis)confirm the collating sequence for the table in question & change it if
necessary.

I spent an afternoon once wracking my brain as to why the results of a PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Quentin
McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to be
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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

Pardee, Roy

2004-06-04 18:57:24 UTC

Permalink

Too true! And I haven't even had to work w/unicode yet--no doubt that
further complicates things...

BTW--I think the default collating sequence for mssql truly does not
distinguish between upper & lower case versions of the same letter.
From the books online topic "Windows Collation Sorting Styles":

If not selected, SQL Server considers the uppercase and lowercase
versions of letters to be equal. SQL Server does not define whether
lowercase letters sort lower or higher in relation to uppercase letters
when Case-sensitive is not selected.

(mssql books online is a free download, btw--get it at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp)

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Sigurd Hermansen
Sent: Friday, June 04, 2004 11:15 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating
sequence (if I recall correctly, the upper case letters follow the lower
case letters in EBCDIC.) I wonder if he is running the query on an IBM
mainframe. Other possibilities include different installation options,
ordering on formatted values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of
people in a training session. Early on I used random number functions
and BYTE() and COLLATE() functions to produce sequences of variable
values. Some in the group asked why database programmers need to know
anything about number representation, collating sequences, bytes, and
binary coding. I guess the details don't matter that much anymore when
MS Windows and SAS hide them so successfully. Only ever so often, what
you see in a display isn't really what you have stored, and you have to
move down a level or two. Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation
order is case-insensitive (tho that's changeable). Your dba should be
able to (dis)confirm the collating sequence for the table in question &
change it if necessary.

I spent an afternoon once wracking my brain as to why the results of a
PROC SQL SELECT DISTINCT were violating a unique key constraint when I
tried to insert them into a mssql table. SAS considered some
differently-cased values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Quentin McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to be
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful for

any

Post by Walt Davis
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

Jack Hamilton

2004-06-04 19:20:39 UTC

Permalink

Wouldn't it be more reasonable to say that Windows and Unix are
derivatives of the predecessors to VMS? Not that would be accurate
either - none of the ancestral OSs is still in commercial use, and
there's always been heavy cross-fertilization.

--
***@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
Fyi -

The ASCII (UNIX and its derivatives, OpenVMS, Windows) and EBCDIC
(OS/390,
z/OS) sort sequences are under Concepts: SORT Procedure:
http://support.sas.com/91doc/getDoc/proc.hlp/a002473663.htm (keyboard
characters)

or @

http://www.asciitable.com/ (this list includes the special
characters)

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Sigurd
Hermansen
Sent: Friday, June 04, 2004 11:15 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT
and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating
sequence
(if I recall correctly, the upper case letters follow the lower case
letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe.
Other
possibilities include different installation options, ordering on
formatted
values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of
people
in a training session. Early on I used random number functions and
BYTE()
and COLLATE() functions to produce sequences of variable values. Some
in the
group asked why database programmers need to know anything about
number
representation, collating sequences, bytes, and binary coding. I guess
the
details don't matter that much anymore when MS Windows and SAS hide
them so
successfully. Only ever so often, what you see in a display isn't
really
what you have stored, and you have to move down a level or two.
Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation
order
is case-insensitive (tho that's changeable). Your dba should be able
to
(dis)confirm the collating sequence for the table in question & change
it if
necessary.

I spent an afternoon once wracking my brain as to why the results of a
PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried
to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Quentin
McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
of upper and lowercase values. PROC SORT is of course case-sensitive
but

when

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to

Post by Walt Davis
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

Post by Walt Davis
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful

for
any

Post by Walt Davis
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

Terjeson, Mark

2004-06-04 19:49:45 UTC

Permalink

Windows(i.e. DOS v1.0) was originally 70%CP/M and 30%Unix.

-----Original Message-----
From: Jack Hamilton [mailto:***@FIRSTHEALTH.COM]
Sent: Friday, June 04, 2004 12:21 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Wouldn't it be more reasonable to say that Windows and Unix are
derivatives of the predecessors to VMS? Not that would be accurate
either - none of the ancestral OSs is still in commercial use, and
there's always been heavy cross-fertilization.

--
***@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
Fyi -

The ASCII (UNIX and its derivatives, OpenVMS, Windows) and EBCDIC
(OS/390,
z/OS) sort sequences are under Concepts: SORT Procedure:
http://support.sas.com/91doc/getDoc/proc.hlp/a002473663.htm (keyboard
characters)

or @

http://www.asciitable.com/ (this list includes the special
characters)

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Sigurd
Hermansen
Sent: Friday, June 04, 2004 11:15 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT
and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating
sequence
(if I recall correctly, the upper case letters follow the lower case
letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe.
Other
possibilities include different installation options, ordering on
formatted
values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of
people
in a training session. Early on I used random number functions and
BYTE()
and COLLATE() functions to produce sequences of variable values. Some
in the
group asked why database programmers need to know anything about
number
representation, collating sequences, bytes, and binary coding. I guess
the
details don't matter that much anymore when MS Windows and SAS hide
them so
successfully. Only ever so often, what you see in a display isn't
really
what you have stored, and you have to move down a level or two.
Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation
order
is case-insensitive (tho that's changeable). Your dba should be able
to
(dis)confirm the collating sequence for the table in question & change
it if
necessary.

I spent an afternoon once wracking my brain as to why the results of a
PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried
to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Quentin
McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
of upper and lowercase values. PROC SORT is of course case-sensitive
but

when

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to

did

Post by Walt Davis
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful

for
any

Post by Walt Davis
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

Choate,

2004-06-04 20:19:39 UTC

Permalink

Interesting to read how DOS originated on MVS.

Have you seen "The Operating Systems Handbook (or Fake Your Way Through
Minis and Mainframes)"? It's available in pdf at
http://www.snee.com/bob/opsys.html

It neatly covers UNIX, OpenVMS, OS/400, VM/CMS and MVS.

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Jack
Hamilton
Sent: Friday, June 04, 2004 12:21 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Wouldn't it be more reasonable to say that Windows and Unix are
derivatives of the predecessors to VMS? Not that would be accurate
either - none of the ancestral OSs is still in commercial use, and
there's always been heavy cross-fertilization.

--
***@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
Fyi -

The ASCII (UNIX and its derivatives, OpenVMS, Windows) and EBCDIC
(OS/390,
z/OS) sort sequences are under Concepts: SORT Procedure:
http://support.sas.com/91doc/getDoc/proc.hlp/a002473663.htm (keyboard
characters)

or @

http://www.asciitable.com/ (this list includes the special
characters)

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Sigurd
Hermansen
Sent: Friday, June 04, 2004 11:15 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT
and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating
sequence
(if I recall correctly, the upper case letters follow the lower case
letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe.
Other
possibilities include different installation options, ordering on
formatted
values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of
people
in a training session. Early on I used random number functions and
BYTE()
and COLLATE() functions to produce sequences of variable values. Some
in the
group asked why database programmers need to know anything about
number
representation, collating sequences, bytes, and binary coding. I guess
the
details don't matter that much anymore when MS Windows and SAS hide
them so
successfully. Only ever so often, what you see in a display isn't
really
what you have stored, and you have to move down a level or two.
Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation
order
is case-insensitive (tho that's changeable). Your dba should be able
to
(dis)confirm the collating sequence for the table in question & change
it if
necessary.

I spent an afternoon once wracking my brain as to why the results of a
PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried
to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of
Quentin
McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
of upper and lowercase values. PROC SORT is of course case-sensitive
but

when

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to

did

Post by Walt Davis
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful

for
any

Post by Walt Davis
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

Sigurd Hermansen

2004-06-04 20:20:08 UTC

Permalink

See
http://www.windowsreinstall.com/install/msdos/msdos/ods/historyofdos.html

I believe that Gates purchased an predecessor of CP/M.

According to
http://www.thocp.net/software/operatingsystems/operating_systems.htm

Tim Patterson developed QDOS. And I had thought that DOS stood for Disk
Operating System! Now we know the pedigree of MS DOS.
Sig
-----Original Message-----
From: Terjeson, Mark [mailto:***@DSHS.WA.GOV]
Sent: Friday, June 04, 2004 3:50 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Windows(i.e. DOS v1.0) was originally 70%CP/M and 30%Unix.

-----Original Message-----
From: Jack Hamilton [mailto:***@FIRSTHEALTH.COM]
Sent: Friday, June 04, 2004 12:21 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Wouldn't it be more reasonable to say that Windows and Unix are derivatives
of the predecessors to VMS? Not that would be accurate either - none of the
ancestral OSs is still in commercial use, and there's always been heavy
cross-fertilization.

--
***@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
Fyi -

The ASCII (UNIX and its derivatives, OpenVMS, Windows) and EBCDIC (OS/390,
z/OS) sort sequences are under Concepts: SORT Procedure:
http://support.sas.com/91doc/getDoc/proc.hlp/a002473663.htm (keyboard
characters)

or @

http://www.asciitable.com/ (this list includes the special
characters)

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Sigurd
Hermansen
Sent: Friday, June 04, 2004 11:15 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating sequence
(if I recall correctly, the upper case letters follow the lower case letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe. Other
possibilities include different installation options, ordering on formatted
values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of people
in a training session. Early on I used random number functions and
BYTE()
and COLLATE() functions to produce sequences of variable values. Some in the
group asked why database programmers need to know anything about number
representation, collating sequences, bytes, and binary coding. I guess the
details don't matter that much anymore when MS Windows and SAS hide them so
successfully. Only ever so often, what you see in a display isn't really
what you have stored, and you have to move down a level or two. Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation order
is case-insensitive (tho that's changeable). Your dba should be able to
(dis)confirm the collating sequence for the table in question & change it if
necessary.

I spent an afternoon once wracking my brain as to why the results of a PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Quentin
McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
of upper and lowercase values. PROC SORT is of course case-sensitive
but

when

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to

did

Post by Walt Davis
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful

for
any

Post by Walt Davis
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

Choate,

2004-06-04 20:42:24 UTC

Permalink

More history, a little farther back
http://www.osdata.com/kind/history.htm

This site has a plethora of OS info....http://www.osdata.com/

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Sigurd
Hermansen
Sent: Friday, June 04, 2004 1:20 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

See
http://www.windowsreinstall.com/install/msdos/msdos/ods/historyofdos.html

I believe that Gates purchased an predecessor of CP/M.

According to
http://www.thocp.net/software/operatingsystems/operating_systems.htm

Tim Patterson developed QDOS. And I had thought that DOS stood for Disk
Operating System! Now we know the pedigree of MS DOS.
Sig
-----Original Message-----
From: Terjeson, Mark [mailto:***@DSHS.WA.GOV]
Sent: Friday, June 04, 2004 3:50 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Windows(i.e. DOS v1.0) was originally 70%CP/M and 30%Unix.

-----Original Message-----
From: Jack Hamilton [mailto:***@FIRSTHEALTH.COM]
Sent: Friday, June 04, 2004 12:21 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Wouldn't it be more reasonable to say that Windows and Unix are derivatives
of the predecessors to VMS? Not that would be accurate either - none of the
ancestral OSs is still in commercial use, and there's always been heavy
cross-fertilization.

--
***@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
Fyi -

The ASCII (UNIX and its derivatives, OpenVMS, Windows) and EBCDIC (OS/390,
z/OS) sort sequences are under Concepts: SORT Procedure:
http://support.sas.com/91doc/getDoc/proc.hlp/a002473663.htm (keyboard
characters)

or @

http://www.asciitable.com/ (this list includes the special
characters)

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Sigurd
Hermansen
Sent: Friday, June 04, 2004 11:15 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating sequence
(if I recall correctly, the upper case letters follow the lower case letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe. Other
possibilities include different installation options, ordering on formatted
values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of people
in a training session. Early on I used random number functions and
BYTE()
and COLLATE() functions to produce sequences of variable values. Some in the
group asked why database programmers need to know anything about number
representation, collating sequences, bytes, and binary coding. I guess the
details don't matter that much anymore when MS Windows and SAS hide them so
successfully. Only ever so often, what you see in a display isn't really
what you have stored, and you have to move down a level or two. Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation order
is case-insensitive (tho that's changeable). Your dba should be able to
(dis)confirm the collating sequence for the table in question & change it if
necessary.

I spent an afternoon once wracking my brain as to why the results of a PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Quentin
McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
of upper and lowercase values. PROC SORT is of course case-sensitive
but

when

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to

did

Post by Walt Davis
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful

for
any

Post by Walt Davis
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

Terjeson, Mark

2004-06-04 21:10:56 UTC

Permalink

To clarify, there were two different DOS operating systems.
First, chronologically was the DOS by Billy Boy(Bill Gates)
which was what I referred to as:
Windows(i.e. DOS v1.0) was originally 70%CP/M and 30%Unix.
Which came out after CP/M.

A couple of years later, IBM (big blue) created what they
called DOS. Within the next 3 years, the confusion of
two different o/s's on the market called DOS, sprung the
formalization of the prefixes MS-DOS(Microsoft) and PC-DOS(IBM).

Several o/s's such as CP/M and AMOS and others have roots
back to the DEC Top-10 o/s.

As far as "Gates purchased an predecessor" of CP/M, those of
us around in o/s and language development circles of that
era can recall that for years it was not an amicable situation
between Gary Kildall and Gates, and for years it was highly
suspect that Gates ever really performed what you would call
"purchased". The core of MS-DOS still(at least 1.0, 2.0, 3.0,
3.3, Windows, and possibly Win95, I haven't checked the rest lately)
still has CP/M jump tables and the same byte for byte low level
BIOS routines. The MS-DOS still contained all of the original
nuances inherent in CP/M. I could take my assembler code and
drivers from CP/M and they would run without alteration on the
newly-?created? MS-DOS... Those of us writing and porting
language and operating system components from CP/M to MS-DOS
found we didn't have to re-write or port very much at all,
almost 97% didn't require any change! shall we say, "Hmmm").
For a couple decades there was a good sized chasm between
Monterey, CA and Kirkland, WA.

FWIW,
Mark

-----Original Message-----
From: Sigurd Hermansen [mailto:***@WESTAT.com]
Sent: Friday, June 04, 2004 1:20 PM
To: 'Terjeson, Mark'; SAS-***@LISTSERV.UGA.EDU
Subject: RE: proc sort vs. order by with character variables

See
http://www.windowsreinstall.com/install/msdos/msdos/ods/historyofdos.html

I believe that Gates purchased an predecessor of CP/M.

According to
http://www.thocp.net/software/operatingsystems/operating_systems.htm

Tim Patterson developed QDOS. And I had thought that DOS stood for Disk
Operating System! Now we know the pedigree of MS DOS.
Sig
-----Original Message-----
From: Terjeson, Mark [mailto:***@DSHS.WA.GOV]
Sent: Friday, June 04, 2004 3:50 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Windows(i.e. DOS v1.0) was originally 70%CP/M and 30%Unix.

-----Original Message-----
From: Jack Hamilton [mailto:***@FIRSTHEALTH.COM]
Sent: Friday, June 04, 2004 12:21 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Wouldn't it be more reasonable to say that Windows and Unix are derivatives
of the predecessors to VMS? Not that would be accurate either - none of the
ancestral OSs is still in commercial use, and there's always been heavy
cross-fertilization.

--
***@FirstHealth.com
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
Fyi -

The ASCII (UNIX and its derivatives, OpenVMS, Windows) and EBCDIC (OS/390,
z/OS) sort sequences are under Concepts: SORT Procedure:
http://support.sas.com/91doc/getDoc/proc.hlp/a002473663.htm (keyboard
characters)

or @

http://www.asciitable.com/ (this list includes the special
characters)

Paul Choate
DDS Data Extraction
(916) 654-2160

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Sigurd
Hermansen
Sent: Friday, June 04, 2004 11:15 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Roy:
My tests confirmed what Quentin reported, and showed that PROC SORT and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating sequence
(if I recall correctly, the upper case letters follow the lower case letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe. Other
possibilities include different installation options, ordering on formatted
values, or displays of formatted values.

Not long back I introduced the topic of SAS functions to a group of people
in a training session. Early on I used random number functions and
BYTE()
and COLLATE() functions to produce sequences of variable values. Some in the
group asked why database programmers need to know anything about number
representation, collating sequences, bytes, and binary coding. I guess the
details don't matter that much anymore when MS Windows and SAS hide them so
successfully. Only ever so often, what you see in a display isn't really
what you have stored, and you have to move down a level or two. Sig

-----Original Message-----
From: Pardee, Roy [mailto:***@GHC.ORG]
Sent: Friday, June 04, 2004 1:45 PM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

I bet Quentin's on the money here. IIRC by default, mssql's collation order
is case-insensitive (tho that's changeable). Your dba should be able to
(dis)confirm the collating sequence for the table in question & change it if
necessary.

I spent an afternoon once wracking my brain as to why the results of a PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.

Cheers,

-Roy

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-***@LISTSERV.UGA.EDU] On Behalf Of Quentin
McMullen
Sent: Friday, June 04, 2004 9:37 AM
To: SAS-***@LISTSERV.UGA.EDU
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
of upper and lowercase values. PROC SORT is of course case-sensitive
but

when

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to

did

Post by Walt Davis
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful

for
any

Post by Walt Davis
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

Richard Ristow

2004-06-05 03:49:19 UTC

Permalink

To clarify, there were two different DOS operating systems. First,
chronologically was the DOS by Billy Boy(Bill Gates) which was what I
Windows(i.e. DOS v1.0) was originally 70%CP/M and 30%Unix.
Which came out after CP/M.
A couple of years later, IBM (big blue) created what they called
DOS. Within the next 3 years, the confusion of two different o/s's on
the market called DOS, sprung the formalization of the prefixes
MS-DOS(Microsoft) and PC-DOS(IBM).

Worse than that. I had thought the PC-DOS was actually a slightly
modified derivative of MS-DOS. But years before that, there was an
operating system named DOS for IBM 360s (now "mainframes"): less
ambitious and sophisticated than the OS/360 that has morphed and
morphed and morphed into MVS.

Several o/s's such as CP/M and AMOS and others have roots
back to the DEC Top-10 o/s.
As far as "Gates purchased an predecessor" of CP/M, those of
us around in o/s and language development circles of that
era can recall that for years it was not an amicable situation
between Gary Kildall and Gates, and for years it was highly
suspect that Gates ever really performed what you would call
"purchased". The core of MS-DOS still(at least 1.0, 2.0, 3.0,
3.3, Windows, and possibly Win95, I haven't checked the rest lately)
still has CP/M jump tables and the same byte for byte low level
BIOS routines. The MS-DOS still contained all of the original
nuances inherent in CP/M. I could take my assembler code and
drivers from CP/M and they would run without alteration on the
newly-?created? MS-DOS... Those of us writing and porting
language and operating system components from CP/M to MS-DOS
found we didn't have to re-write or port very much at all,
almost 97% didn't require any change! shall we say, "Hmmm").
For a couple decades there was a good sized chasm between
Monterey, CA and Kirkland, WA.
FWIW,
Mark
-----Original Message-----
Sent: Friday, June 04, 2004 1:20 PM
Subject: RE: proc sort vs. order by with character variables
See
http://www.windowsreinstall.com/install/msdos/msdos/ods/historyofdos.html
I believe that Gates purchased an predecessor of CP/M.
According to
http://www.thocp.net/software/operatingsystems/operating_systems.htm
Tim Patterson developed QDOS. And I had thought that DOS stood for Disk
Operating System! Now we know the pedigree of MS DOS.
Sig
-----Original Message-----
Sent: Friday, June 04, 2004 3:50 PM
Subject: Re: proc sort vs. order by with character variables
Windows(i.e. DOS v1.0) was originally 70%CP/M and 30%Unix.
-----Original Message-----
Sent: Friday, June 04, 2004 12:21 PM
Subject: Re: proc sort vs. order by with character variables
Wouldn't it be more reasonable to say that Windows and Unix are derivatives
of the predecessors to VMS? Not that would be accurate either - none of the
ancestral OSs is still in commercial use, and there's always been heavy
cross-fertilization.
--
Manager, Technical Development
Metrics Department, First Health
West Sacramento, California USA
Fyi -
The ASCII (UNIX and its derivatives, OpenVMS, Windows) and EBCDIC (OS/390,
http://support.sas.com/91doc/getDoc/proc.hlp/a002473663.htm (keyboard
characters)
http://www.asciitable.com/ (this list includes the special
characters)
Paul Choate
DDS Data Extraction
(916) 654-2160
-----Original Message-----
Hermansen
Sent: Friday, June 04, 2004 11:15 AM
Subject: Re: proc sort vs. order by with character variables
My tests confirmed what Quentin reported, and showed that PROC SORT and
ORDER BY produced identical results given the test data. The ORDER BY
results reported by Walt appear (almost) to be in EBCDIC collating sequence
(if I recall correctly, the upper case letters follow the lower case letters
in EBCDIC.) I wonder if he is running the query on an IBM mainframe. Other
possibilities include different installation options, ordering on formatted
values, or displays of formatted values.
Not long back I introduced the topic of SAS functions to a group of people
in a training session. Early on I used random number functions and
BYTE()
and COLLATE() functions to produce sequences of variable values. Some in the
group asked why database programmers need to know anything about number
representation, collating sequences, bytes, and binary coding. I guess the
details don't matter that much anymore when MS Windows and SAS hide them so
successfully. Only ever so often, what you see in a display isn't really
what you have stored, and you have to move down a level or two. Sig
-----Original Message-----
Sent: Friday, June 04, 2004 1:45 PM
Subject: Re: proc sort vs. order by with character variables
I bet Quentin's on the money here. IIRC by default, mssql's collation order
is case-insensitive (tho that's changeable). Your dba should be able to
(dis)confirm the collating sequence for the table in question & change it if
necessary.
I spent an afternoon once wracking my brain as to why the results of a PROC
SQL SELECT DISTINCT were violating a unique key constraint when I tried to
insert them into a mssql table. SAS considered some differently-cased
values as distinct & mssql did not.
Cheers,
-Roy
-----Original Message-----
McMullen
Sent: Friday, June 04, 2004 9:37 AM
Subject: Re: proc sort vs. order by with character variables

Post by Walt Davis
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

Post by Walt Davis
of upper and lowercase values. PROC SORT is of course case-sensitive
but

when

Post by Walt Davis
we use an ORDER BY statement in PROC SQL, the sort doesn't appear to

did

Post by Walt Davis
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

Post by Walt Davis
"order by" to sort this the same way as SAS?
Thanks. A direct reply would be appreciated, but we'll be grateful

for
any

Post by Walt Davis
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