2

(this is homework, not going to lie)

I have an ANSI SQL query I wrote

this produces the required 3rd highest prices correctly,

table sample is

select unique uni, price
from
(
(
     select unique uni, price
     from 
     (
          select unique uni, price
          from table1
          group by uni
          having price < max(price)
     )
     group by uni
     having price < max(price)
) 
group by uni
having price < max(price)
)

now i need to list the 1st, 2nd and 3rd into one table but make is such that it could be used nth times.

  example:
  Col1    Col2    
  uni1    10
  uni1    20
  uni2    20
  uni2    10
  uni3    30
  uni3    20
  uni1    30

/sorry for the formatting i havent been here for a very long time, i appreciate any assistance, i will supply a link to the uni of which i have asked the tutor if i can do so he said yes but not the whole code, something like 10%, but anyways./

Ray Man
  • 65
  • 11
  • 3
    You should (at least) put the query in as text in the question, not an image. The data is better as text tables too. – Gordon Linoff Oct 29 '20 at 11:53
  • @GordonLinoff thank you, i will fix that now – Ray Man Oct 29 '20 at 12:19
  • @GordonLinoff hey, may I ask you how do I draw a table here. I got mixed results when googling. – Ray Man Oct 29 '20 at 12:34
  • The way to provide data for SAS posts is as a stand alone data step with in-line data so users can copy and paste into a SAS session to reproduce your example data. – Tom Oct 29 '20 at 13:31

1 Answers1

0

In SAS you can use the proprietary option OUTOBS to restrict how many rows of a result set are output.

Example:

Use OUTOBS=3 to create top 3 table. Then use that table in a subsequent query.

data have;
input x @@; datalines;
10 9 8 7 6 5 4 3 2 1 0
;

proc sql;
  reset outobs=3;
  create table top3x as
  select * from have
  order by x descending;

  reset outobs=max;
  * another query;
quit;
Richard
  • 25,390
  • 3
  • 25
  • 38