Help: Need equivalent SAS SQL query for given MySql query

[Patrick]

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.

[Reeza]

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;

[nitesh_k]

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.

[Tom]

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?

[nitesh_k]

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

-- This answers your range of bands question. Lets assume we have 15 rows in  table, then for some stats computation will fire 3 queries(or one union query)

with range of bands 1-5, 6-10,11-15, etc. Hope this answers the curiosity.

Anyways, point is, forget about stats and all, and lets concentrate on the SAS SQL limitation which other database vendors supports.

As mentioned earlier, waiting now for the SAS tech support guys help.

[Ksharp]

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

[nitesh_k]

Finally, we got some round about answer to the problem we were facing.

Below query fixed the problem to some extent:

PROC SQL Query:

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

SELECT MIN(total_cost), MAX(total_cost)

FROM

(

    SELECT distinct total_cost

    FROM WORK.product

) WHERE MONOTONIC() BETWEEN 1 AND 5;

Note: Using "distinct" in the inner query is working as what we intended with the order by earlier. So avoided the order by and used the MONOTONIC in the outer query. This solved the problem. But again caution if you are using monotonic as read somewhere it is undocumented function of SAS.

Gosh, Any other better way is always welcome!!