Help using Base SAS procedures

Help: Monotonic function is misbehaving when used in query with inline views

Reply
Occasional Contributor
Posts: 19

Help: Monotonic function is misbehaving when used in query with inline views

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. 

Super User
Posts: 9,687

Re: Help: Monotonic function is misbehaving when used in query with inline views

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

Occasional Contributor
Posts: 19

Re: Help: Monotonic function is misbehaving when used in query with inline views

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.

Super User
Posts: 5,260

Re: Help: Monotonic function is misbehaving when used in query with inline views

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
Occasional Contributor
Posts: 19

Re: Help: Monotonic function is misbehaving when used in query with inline views

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:




SAS Super FREQ
Posts: 8,743

Re: Help: Monotonic function is misbehaving when used in query with inline views

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

Respected Advisor
Posts: 4,654

Re: Help: Monotonic function is misbehaving when used in query with inline views

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
Super User
Posts: 9,687

Re: Help: Monotonic function is misbehaving when used in query with inline views

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

Super User
Posts: 9,687

Re: Help: Monotonic function is misbehaving when used in query with inline views

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

Respected Advisor
Posts: 4,654

Re: Help: Monotonic function is misbehaving when used in query with inline views

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
Super User
Posts: 9,687

Re: Help: Monotonic function is misbehaving when used in query with inline views

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

Respected Advisor
Posts: 3,899

Re: Help: Monotonic function is misbehaving when used in query with inline views

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

Occasional Contributor
Posts: 19

Re: Help: Monotonic function is misbehaving when used in query with inline views

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. 

Super User
Posts: 9,687

Re: Help: Monotonic function is misbehaving when used in query with inline views

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

Super User
Super User
Posts: 6,502

Re: Help: Monotonic function is misbehaving when used in query with inline views

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 ;

Ask a Question
Discussion stats
  • 20 replies
  • 961 views
  • 2 likes
  • 8 in conversation