topic Re: PROC SQL and ordering alphanumeric in SAS Procedures

PROC SQL and ordering alphanumeric

metalray — Mon, 21 Jan 2013 16:43:49 GMT

Hello,

We use an Oracle 10g database and I need to order an alphanumeric columns in my PROC SQL.

Now, SAS does not understand Oracle specific functions like to_number, lpad, translate, regex so I

wonder if someone knows a standard sql (ansi) way to sort such a column?

Thanks

Re: PROC SQL and ordering alphanumeric

Quentin — Mon, 21 Jan 2013 18:10:45 GMT

Hi,

It might be most efficient to run the query as a pass-through query to Oracle. That way Oracle would do the heavy lifting, and would return the results to SAS. And you could use Oracle-specific functions.

But sounds like you are using SAS to run the query. In which case, you will need to use SAS functions in your query rather than oracle functions.

Below is an example of using the INPUT function on a character variable to order by numeric values. Feel free to post more specifics if you need help with other SASsy conversions (handling date strings, etc).

data a;
  input var $;
  cards;
5
10
15
20
;
run;

proc sql;
  select *
    from a
    order by var
  ;
  select *
    from a
    order by input(var,8.)
  ;
quit;

HTH,

--Q.

Re: PROC SQL and ordering alphanumeric

Reeza — Mon, 21 Jan 2013 18:54:23 GMT

Can you explain a bit more what type of data you're trying to sort and how?

Within a SQL step or is proc sort an option?

Re: PROC SQL and ordering alphanumeric

metalray — Tue, 22 Jan 2013 10:18:51 GMT

Hello Quentin.

I am using PROC SQL (in a SAS STP) and sql pass through, that is right but the SAS driver's
for Oracle only allow me to use ANSI standard sql function not Oracle specific functions
like decode or to_date, etc.

Is there no ANSI standard SQL I can use for ordering alphanumeric data?
Too bad.

alphanumeric data, stuff like:
1
3
65
42
EA
UD
EB

@Reeza, PROC SQL, sorting a column that contains alphanumeric data

Thanks

Re: PROC SQL and ordering alphanumeric

Quentin — Tue, 22 Jan 2013 10:35:54 GMT

Hi,

Can you show a little bit of your SQL code? If it's an explicit pass-through query, then I'm pretty confident that you can use Oracle specific functions (I've certainly done it in the past).

Does your code look something like:

proc sql;
  connect to oracle (user=... );
  select * from connection to oracle
    (
      select *
      from MyFavoriteOracleTable
    );
 ...

If it does, then the nested query should be executing on oracle, and should allow oracle specific functions.

--Q.

Re: PROC SQL and ordering alphanumeric

LinusH — Tue, 22 Jan 2013 11:41:33 GMT

If you like to influence the sort order in SAS, you could take a look at SORTSEQ=, TRANTAB and Collating Sequence options in SAS. I'm pretty sure you could accomplish what you need using these in the right way.

Functions: SAS is able to translate some SAS functions to corresponding Oracle functions, see online doc for SAS/ACCESS for more details. To analyze what parts of the query that implicitly be sent to Oracle, use the SASTARCE global options.

But again, if you really need to a pass-thru, maybe for performance issues, you are most safe writing an explicit SQL pass-thru.

Re: PROC SQL and ordering alphanumeric

metalray — Tue, 22 Jan 2013 15:12:26 GMT

Helllo Quentin,

Thanks for your reply. Unfortunately I can't provide a more extensive example but why
is it needed for sorting?

Your suggested "order by input(var,8.)" worked! Just that the letter sorting like
EA
JK
QO
1
2
3
comes before the numbers, and I need it the other way around.

Yes, my Oracle query looks like yours.
proc sql;
connect to oracle (user=... );
select * from connection to oracle
    (
      select *
      from MyFavoriteOracleTable
    );
...

but...Oracle specific functions like DECODE do not work. (ERROR: Function DECODE could not be located.)

@LinusH,
Thanks for your input. I need to use SQL pass-through. no way around it.

Re: PROC SQL and ordering alphanumeric

Reeza — Tue, 22 Jan 2013 15:24:16 GMT

proc sql;

connect to oracle (user=... );

select * from connection to oracle

(

select *

from MyFavoriteOracleTable

);

...

Your Oracle SQL functions need to go in the second select * area, e.g

proc sql;

connect to oracle (user=... );

select * from connection to oracle

(

select decode() , to_char()

from MyFavoriteOracleTable

);

Re: PROC SQL and ordering alphanumeric

metalray — Wed, 23 Jan 2013 08:13:39 GMT

Reeza. I know that but that does not solve the problem. How do I sort my alpha numeric data in the column. Numbers first, then the letter combinations.

Re: PROC SQL and ordering alphanumeric

Quentin — Wed, 23 Jan 2013 17:01:43 GMT

I'm confused, are you asking how to do this in Oracle now?

Assuming you know how to do what you want in Oracle, I would suggest you:

1. Send a (simple) example of the query you can run in Oracle to do what you want.

2. Send the SAS log you get from running that query inside of an explicit pass-through block.

I'm still stuck on that if you have working code in Oracle, that code should work inside the pass-through block.

But if you don't know how to do want in Oracle, then I missed the point of the original question...

--Q.

Re: PROC SQL and ordering alphanumeric

metalray — Thu, 24 Jan 2013 08:06:50 GMT

I did ask if someone knows a standard ansi sql way of doing it in oracle (proc sql pass through) since this does not seem to exist. the error I get I posted. I am happy

to learn about a way how to do it in sas. what I tried yesterday , after the proc sql. a proc sort.

but the proc sort did not work.

proc sort data= t_stp_temp noduprecs;

by myIDnumber ;

run;

Re: PROC SQL and ordering alphanumeric

LinusH — Thu, 24 Jan 2013 10:49:31 GMT

I'm also confused of what the real question is.

If the main issue is if there is support in ANSI 92 SQL for dealing with your specific sorting requirement - no, I don't think so.

So, next issue, to solve this in

Oracle specific SQL, using SAS SQL pass-thru: you need to search for Oracle documentation/go to an Oracle forum
Using SAS SQL: explore the possibilities to setting custom sequences, see my earlier post.

Re: PROC SQL and ordering alphanumeric

metalray — Thu, 24 Jan 2013 11:50:33 GMT

LinusH, glad that even though none of you got the "real" question quite right,
the problem is understood. Sorting of alpha numeric characters (see title)

If there is no ansi sql standard that I can use with my proc sql,
and believe me, I looked a lot through Oracle documentation (but one can never be sure to cover it all -so I asked here), then I was hoping there is a SAS one. Various SAS people I have asked in the meantime suggested to fill the single digit numbers
(rpad in oracle -but does not work for me *ERROR: Function RPAD could not be located.*)
with 0 and try the sorting again. - I think this comes close to what
Quentin suggested with order by input(var,8.)