BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metalray
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

    );

View solution in original post

12 REPLIES 12
Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Reeza
Super User

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?

metalray
Calcite | Level 5

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

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
metalray
Calcite | Level 5

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.

Reeza
Super User

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

    );

metalray
Calcite | Level 5

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

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
metalray
Calcite | Level 5

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;

LinusH
Tourmaline | Level 20

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.
Data never sleeps
metalray
Calcite | Level 5

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

LinusH
Tourmaline | Level 20

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.

Data never sleeps

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 4600 views
  • 3 likes
  • 4 in conversation