BookmarkSubscribeRSS Feed
nitesh_k
Calcite | Level 5

Hi All,

Check the below query:

SELECT MIN(total_cost), MAX(total_cost)

FROM

(

    SELECT distinct total_cost

    FROM WORK.product

) WHERE MONOTONIC() BETWEEN 1 AND 5;

This query works fine if you execute it initially. But if you run it again and again, sometimes its returning Null values. Smiley Sad

Note: I read somewhere monotonic function is undocumented function of SAS.

Where can I find if this is a bug. And if it is, then does any fix/patch exists?  Do I need to upgrade my SAS version? I am on SAS 9.2.

Just put up all questions which came to my mind. Please help?

Note: I really need to use this query as is. Changing the query doesn't help. Why I am saying this? please refer:

So please reply accordingly. 

20 REPLIES 20
Ksharp
Super User

So did you try my code ? That is the weakness of SAS SQL (a standard SQL) .

Due to your need of only one single SQL statement , My code is too long and awkward ,but maybe it is worked. Try it.

data product;
 set sashelp.class;
 keep weight;
 rename weight= total_cost;
run;





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

(
select max(total_cost) as total_cost from product
union
select max(total_cost) as total_cost from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                   )



union
select max(total_cost) as total_cost from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                   ))
                                  )
union
select max(total_cost) as total_cost from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                   ))
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                   ))
                                  ) )
                                  )

union
select max(total_cost) as total_cost from (                                   
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                   ))
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                   ))
                                  ) )
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                   ))
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                  except
                                  (select max(total_cost)  from (
                                  select total_cost from product
                                  except
                                  select max(total_cost)  from product
                                   ))
                                  ) )
                                  ))
                                  )

)
;
quit;




Ksharp

nitesh_k
Calcite | Level 5

Hi KSharp. Sorry I couldn't reply to you earlier as I was not looking for this kind of solution.

Your solution will work only when I need to get min and max from top 5 rows.

But I need it little generic way to get the min and max values from any start and end row number.

For example, say we need to get min and max from row number 5 to 10.

Hope I clarified your doubt. Sorry again, if you got distracted with my example having 1 and 5. Row number can be from anything to anything.

LinusH
Tourmaline | Level 20

Without having read the other thread in detail, here's a crazy (?) idea:

If it's possible to have a temporary table created for you with distinct values, you could use firstobs= and obs= options...

proc sql;

     create table dist_cost as

          select distinct total_cost

          from work.product

     ;

     select min(total_cost), max(total_cost)

          from dist_cost(firstobs=5 obs=10)

     ;

quit;

Data never sleeps
nitesh_k
Calcite | Level 5

If it's possible to have a temporary table created for you with distinct values

  -- Yah LinusH, its not possible. That's what the other thread is all about. And you already mention similar solution in that other thread too. It doesn't help though. :smileysilly:




Cynthia_sas
SAS Super FREQ

Hi:

  There is a difference between undocumented and unsupported. From this Tech Support note:

http://support.sas.com/kb/15/138.html

  

The MONOTONIC() function is not supported in PROC SQL.  Using the

MONOTONIC() function in PROC SQL can cause missing or non-sequential

values to be returned.

I believe if you check with Tech Support you will find that while there are instances where MONOTONIC might work, there are also instances (as you discovered) where MONOTONIC is not reliable for your needs.

cynthia

PGStats
Opal | Level 21

There is a way to circumvent the problem (which btw I think is caused by poor database design) but it is not very efficient. Consider this example :

proc sql;

select min(horsepower) as min_h, max(horsepower) as max_h

from

     ( select a.horsepower

       from sashelp.cars as A inner join sashelp.cars as B

       on A.horsePower <= B.horsepower

       group by A.horsepower

       having sum(A.horsePower < B.horsepower)<5 );

quit;

PG

PG
Ksharp
Super User

PG, I like your code. Yours better than mine.

But if there are some duplicated value ,your code can't work , so you need add a keyword DISTINCT into it.

proc sql;

select min(horsepower) as min_h, max(horsepower) as max_h

from

     ( select a.horsepower

       from (select distinct horsepowrer from sashelp.cars) as A inner join (select distinct horsepowrer from sashelp.cars)  as B

       on A.horsePower <= B.horsepower

       group by A.horsepower

       having sum(A.horsePower < B.horsepower)<5 );

quit;

Ksharp

Ksharp
Super User

PG,

Under your stimulation, I found another way by using sub-query. Don't know which one is faster.I guess yours . Smiley Happy

data class;
 set sashelp.class;
run;

proc sql;
 select distinct weight,(select count(*) from
        (select distinct weight as w from class) as x
         where x.w gt weight) as n
 from class
  where calculated n in (0 4);
quit;






Ksharp

PGStats
Opal | Level 21

Haha! Not that simple this little problem... When there are duplicate values among the 5 highest, counting the pairs in a self-join must be done with great care. Using DISTINCT doesn't work for all cases, consider :

data test;
input x @@;
datalines;
1 2 2 3 3 3 4 4 4 4
11 22 22 33 33 44 44
;

/* A solution based on DISTINCT returns min=4 max=44, not the right answer */
proc sql;
select min(x) as min_x, max(x) as max_x
from
     ( select a.x
       from (select distinct x from test) as A inner join (select distinct x from test) as B
       on A.x <= B.x
       group by A.x
       having sum(A.x < B.x)<5 );
quit;

/* This returns min=22 max=44, as expected. Change the test dataset to see that it always gives the right result. */
proc sql;
select min(x) as min_x, max(x) as max_x
from
     (select a.x
     from test as a inner join test as b on a.x <= b.x
     group by a.x
     having sum(a.x<b.x)**2 < 5**2*sum(a.x=b.x));
quit;

PG

PG
Ksharp
Super User

PG,

Maybe you misunderstand what OP means, OP want TOPn which are unique.

E.X.

5

5

4

4

3

2

1

Top5 is 5 4 3 2 1

Not 5 5 4 4 3

Ksharp

Patrick
Opal | Level 21

What about using Proc Rank for this? The TIES option allows you to choose how you want to calculate the ranks.

nitesh_k
Calcite | Level 5

Ksharp, sorry but I am not looking for only TOPn here. As I replied to your earlier post, I want to get min and max values from any row number to any other row number. 

Ksharp
Super User

You can do it ,just add a dataset option . But I am not sure whether JDBC will take care of it.

Or if your table has already has a column like : 1 2 3 4 5 6 7 8 9 10 ...........  to mark it from smallest to biggest.

Then that would be more easy.

proc sql;

select distinct weight,(select count(*) from

        (select distinct weight as w from class(firstobs=100 obs=200)  ) as x

         where x.w gt weight) as n

from class(firstobs=100 obs=200)

  where calculated n in (0 4);

quit;

Ksharp

Tom
Super User Tom
Super User

I am not sure what you want to do.

Normally the use of limited return values from MYSQL query is for an interactive session where you want to quickly (user interface speeds instead of analysis results speeds) let user page through data.  If so I am not sure how it applies to min and max over 5 observations?

Can you provide some sample data with what you want the output to be?  For example here are 7 observations, not sorted.

data product;

do total_cost=4,2,3,6,5,1,7; output; end;

run;

I can find the min,max over the first 5 observations using:

proc sql noprint;

  select min(total_cost), max(total_cost)

  into :min,:max

  from product (firstobs=1 obs=5)

;

%put min=&min max=&max;

* Yields 2 and 6 ;

Do you want to find the first and fifth lowest value? 

proc sql ;

  select total_cost

  into :cost1-:cost5

  from (

    select distinct total_cost from product

  )

  order by total_cost

;

%let min=&cost1;

%let max=&cost5;

%put min=&min max=&max;

* Yield 1 and 5 ;

What if there are fewer than 5 distinct costs?  Then you can use SQLOBS macro variable to know how many were actually returned.

So for example if we only had the first 3 values in the dataset.

proc sql ;

  select total_cost

  into :cost1-:cost5

  from (

    select distinct total_cost from product (obs=3)

  )

  order by total_cost

;

%let min=&cost1;

%let max=&&cost&sqlobs;

%put min=&min max=&max;

* Yields 2 and 4 ;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 20 replies
  • 2706 views
  • 2 likes
  • 8 in conversation