02-16-2012 02:01 AM
Im trying to get the SAS SQL equivalent for the below 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.
02-16-2012 03:18 AM
select min(number) as minnum, max(number) as maxnum from want(firstobs=1 obs=5);
hope this might give you some idea.
02-16-2012 04:51 AM
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;
02-16-2012 05:06 AM
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()
Do we have something similar in SAS? Or any other solution to solve this problem?
02-16-2012 09:58 AM
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...
02-16-2012 10:33 AM
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..
02-16-2012 11:05 PM
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.
02-17-2012 03:56 AM
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..
02-17-2012 07:57 AM
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.
02-17-2012 10:49 AM
I probably don't understand what you are trying to do. Is it just something like the following?:
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)
02-17-2012 11:28 AM
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.
02-17-2012 11:28 AM
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?
02-17-2012 11:52 AM
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?
02-17-2012 12:09 PM
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.
SELECT MIN(WXP49.total_cost) , MAX(WXP49.total_cost) FROM WORK.product WXP49 WHERE WXP49.BRAND in ('GE','Motorola') ;