topic Help: Need equivalent SAS SQL query for given MySql query in SAS Procedures

Help: Need equivalent SAS SQL query for given MySql query

nitesh_k — Thu, 16 Feb 2012 07:01:30 GMT

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.

Help: Need equivalent SAS SQL query for given MySql query

Newbee_MVK — Thu, 16 Feb 2012 08:18:13 GMT

data want;

input number;

cards;

;

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.

Help: Need equivalent SAS SQL query for given MySql query

Ksharp — Thu, 16 Feb 2012 09:51:16 GMT

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;

Help: Need equivalent SAS SQL query for given MySql query

nitesh_k — Thu, 16 Feb 2012 10:06:22 GMT

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?

Help: Need equivalent SAS SQL query for given MySql query

LinusH — Thu, 16 Feb 2012 14:58:51 GMT

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

Help: Need equivalent SAS SQL query for given MySql query

nitesh_k — Thu, 16 Feb 2012 15:33:05 GMT

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

Help: Need equivalent SAS SQL query for given MySql query

Ksharp — Fri, 17 Feb 2012 04:05:21 GMT

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

Help: Need equivalent SAS SQL query for given MySql query

nitesh_k — Fri, 17 Feb 2012 08:56:12 GMT

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

Help: Need equivalent SAS SQL query for given MySql query

LinusH — Fri, 17 Feb 2012 12:57:58 GMT

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.

Help: Need equivalent SAS SQL query for given MySql query

nitesh_k — Fri, 17 Feb 2012 15:30:04 GMT

Yep, data is stored in SAS.

Help: Need equivalent SAS SQL query for given MySql query

art297 — Fri, 17 Feb 2012 15:49:20 GMT

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

data have;

input number;

cards;

;

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;

Help: Need equivalent SAS SQL query for given MySql query

nitesh_k — Fri, 17 Feb 2012 16:28:13 GMT

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.

Re: Help: Need equivalent SAS SQL query for given MySql query

Tom — Fri, 17 Feb 2012 16:28:42 GMT

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?

Help: Need equivalent SAS SQL query for given MySql query

nitesh_k — Fri, 17 Feb 2012 16:52:31 GMT

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?

Re: Help: Need equivalent SAS SQL query for given MySql query

Tom — Fri, 17 Feb 2012 17:09:22 GMT

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')
;

Re: Help: Need equivalent SAS SQL query for given MySql query

Patrick — Sat, 18 Feb 2012 00:12:15 GMT

In case Tom's answer is not what you need then it might be worth to raise your question with SAS TechSupport http://support.sas.com/ctx/supportform/createForm

SAS is not a database vendor but has access modules to most data base vendors. SAS' own implementation of SQL to query SAS files is only a subset of what you can expect from database implementations.

I see TOPn questions popping up since years - and it's unfortunately a statement which is still not implemented in SAS SQL.

As a work-around and not sure about this at all: eventually methods setFetchSize() or setMaxRows() could help.

If you ask SAS TechSupport the question then please post their answer here. Very interested what solution will be suggested.

Re: Help: Need equivalent SAS SQL query for given MySql query

Reeza — Sat, 18 Feb 2012 00:19:44 GMT

Why not use a data step or built in proc?

proc means data=WORK.product n mean min max;

class brand;

var total_cost;

run;

Help: Need equivalent SAS SQL query for given MySql query

nitesh_k — Sat, 18 Feb 2012 06:21:00 GMT

Tom, I meant "range of bands" NOT "range of brands". So again as requested lets not validate the problem statement and come up with different angle for solution.

I do not want to divert the actual limitation in SAS SQL which other database supports.

Reeza I hope above comments answer your question.

And patrick thanks a lot!! I was looking for this sort of help if nothing works. Will definitely post it to tech support now and let you guys know if there is some solution to this problem. Yeah, you are right on Top N query. I read it in many places too.

Help: Need equivalent SAS SQL query for given MySql query

Tom — Sat, 18 Feb 2012 15:38:42 GMT

Not sure what "range of bands" is, but unless it is defined by values of actual variables I am not sure what interpretation you could put onto any statistics calculated from them.

How did you decide to find the min and max over the first 5 values? Why not the first 6 or 10 values?

Help: Need equivalent SAS SQL query for given MySql query

Ksharp — Mon, 20 Feb 2012 04:01:56 GMT

Here is a solution for TOPn.

Just for fun.

proc sql;
select min(weight) as min,max(weight) as max from

(
select max(weight) as weight from sashelp.class
union
select max(weight) as weight from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                   )



union
select max(weight) as weight from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                   ))
                                  )
union
select max(weight) as weight from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                   ))
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                   ))
                                  ) )
                                  )

union
select max(weight) as weight from (                                   
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                   ))
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                   ))
                                  ) )
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                   ))
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                  except
                                  (select max(weight)  from (
                                  select weight from sashelp.class
                                  except
                                  select max(weight)  from sashelp.class
                                   ))
                                  ) )
                                  ))
                                  )

)
;
quit;

Ksharp