Help: Need equivalent SAS SQL query for given MySql query

[nitesh_k]

Im trying to get the SAS SQL equivalent for the below MYSQL query

MySQL Query:

===========

SELECT MIN(temp.total_cost), MAX(temp.total_cost) FROM ( SELECT WXP49.total_cost FROM product WXP49 ORDER BY WXP49.total_cost DESC LIMIT 1, 5) AS temp;

Idea is to get the min and max value of total cost from row number 1 to 5 but before that need to order by total cost column .

I am new to SAS. I tried below query,

PROC SQL Query:

==============

SELECT MIN(WXP49.total_cost), MAX(WXP49.total_cost) FROM WORK.product WXP49 WHERE MONOTONIC() BETWEEN 1 AND 5 ORDER BY WXP49.total_cost ASC

This query seems to be misbehaving and giving me five results back with same min and max value.

Is this is the correct way of getting what I was intending? Is there any other way?

Please help and let me know the equivalent SAS SQL for the above MySQL query.

[Newbee_MVK]

data want;

input number;

cards;

1

2

3

4

5

6

7

;

run;

proc sql;

select min(number) as minnum, max(number) as maxnum from want(firstobs=1 obs=5);

run;

hope this might give you some idea.

[Ksharp]

Or Maybe like:

SELECT MIN(temp.total_cost), MAX(temp.total_cost)

FROM ( SELECT total_cost

                  FROM product (obs=5)

                   ORDER BY total_cost DESC ) AS temp;

[nitesh_k]

Yeah Ksharp thanks for the reply. you got the requirement, but order by doesn't seems to work in the inner query atleast on sas sharenet server 9.2.

I got the below error:

>[Error] Script lines: 1-4 --------------------------

Sharenet.S206.ex.txt: Sharenet.S252.ex.txt: -2013077488 ERROR: SQL passthru expression contained these errors: ERROR 79-322: Expecting a )....  ERROR 22-322: Syntax error, expecting one of the following: ;, ','....  ERROR 200-322: The symbol is not recognized and will be ignored. -2147295222.. 

When I remove the order by clause, it does works. But i need to first apply the order by and then limit as mentioned in that mysql query.

I read somewhere similar problem for teradata database, the solution mentioned there was to bring the limit and order by to the outer query with QUALIFY and  OVER()

function.

Do we have something similar in SAS? Or any other solution to solve this problem?

[LinusH]

For some reason, SAS doesn't seem to allow ORDER BY in a FROM subquery. You can solve it by pre-sorting the input table, or create a sorted view.

Be aware of that ORDER BY sorts the output data, so a OBS=5 will not get the top 5 total_cost...

[nitesh_k]

Thanks LinusH for the reply. But my requirement is not only one column sorting. Say I have 'n' columns on the same table, but each time

based on some business requirement will want to sort on different column. So pre-sorting doesn't helps, also so many views creation is not possible.

That's why mentioned in my previous comment to  Ksharp that other DB vendors(take teradata, oracle, mysql, mssql, etc) support this requirement.

This issue is dragging, any one please help? Feeling little restless with SAS SQL..

[Ksharp]

Oh .Dear.

Sorry. I can't give you what you want.

I tried to use function largest(),But it dosen't work for SQL.

I also try to use EXCEPT operation,But that is too tedious.

Ksharp

[nitesh_k]

okk will wait for further inputs from someone else or SAS guys itself.. !!  Anyways thanks for the effort... atleast i validated my understanding to some extent..

[LinusH]

But is your data stored in SAS? And you just compared to MySQL from your knowledge?

Because if your data is resides in an external RDBMS, you can use explicit SQL pass-thru and use any RDBMS specific SQL syntax you need.

[nitesh_k]

Yep, data is stored in SAS.

[art297]

I probably don't understand what you are trying to do.  Is it just something like the following?:

data have;

input number;

cards;

1

2

3

4

5

6

7

;

proc sql;

  create table ordered as

    select * from have

      order by number desc

  ;

  select min(number) as minnum,

         max(number) as maxnum

    from ordered (firstobs=1 obs=5)

  ;

quit;

[nitesh_k]

This is not what i was looking for art297. I need one single SAS SQL which gets executed from my JDBC layer of Java code.

So multiple statements in proc sql is ruled out.

[Tom]

What is the actual issue that you are trying to solve by limiting the number of rows returned by your query?

My impression is that those types of limitations are useful for interactive systems where the user fetches a few rows and view in their browser but what is the value in generating a statistic over an arbitrary limit on the number or rows of data?

[nitesh_k]

Thats a good question Tom. Idea here is to get the different min and max values of data for not total arbitrary number of rows but for some set of range bands.

And yes this is required instantaneously based on some end user query. Hope that helps.

So please guys no more thought on validating the problem statement, because we have the solution in place for other database vendors(teradata, oracle, mysql, etc).

They all support this requirement and hopefully SAS should support this. Need help?

[Tom]

In that case then supply the "range of brands" directly by specifying the proper WHERE clause for the query.

Also you do not need to worry about sorting to handle different queries. Instead create indexes and SAS will automatically use them when appropriate.  These same techniques will work for other databases also.

Example:

SELECT MIN(WXP49.total_cost)
     , MAX(WXP49.total_cost)
  FROM WORK.product WXP49 
  WHERE WXP49.BRAND in ('GE','Motorola')
;