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

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

nitesh_k — Fri, 07 Sep 2012 07:06:11 GMT

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.

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.

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

Ksharp — Fri, 07 Sep 2012 07:35:35 GMT

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

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

nitesh_k — Fri, 07 Sep 2012 07:55:11 GMT

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.

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

LinusH — Fri, 07 Sep 2012 10:59:06 GMT

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;

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

nitesh_k — Fri, 07 Sep 2012 11:58:09 GMT

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:

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

Cynthia_sas — Fri, 07 Sep 2012 14:19:23 GMT

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

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

PGStats — Fri, 07 Sep 2012 17:12:03 GMT

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;

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

Tom — Fri, 07 Sep 2012 17:51:29 GMT

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 ;

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

Ksharp — Mon, 10 Sep 2012 02:39:15 GMT

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

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

Ksharp — Mon, 10 Sep 2012 13:11:18 GMT

PG,

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

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

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

PGStats — Tue, 11 Sep 2012 01:58:32 GMT

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;

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

Ksharp — Tue, 11 Sep 2012 03:27:17 GMT

PG,

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

E.X.

Top5 is 5 4 3 2 1

Not 5 5 4 4 3

Ksharp

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

Patrick — Tue, 11 Sep 2012 12:31:58 GMT

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

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

nitesh_k — Tue, 11 Sep 2012 12:40:31 GMT

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.

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

Patrick — Tue, 11 Sep 2012 14:05:26 GMT

I was now also looking at your other post and from what I understand you're simply sorting by total_cost and then you select the 1st to the 5th row in the result set.

You then calculate the min and max within this result set (actually the first row contains the max value and the 5th the min value).

If your source table is still in MySQL then you could simply use SAS' explicit SQL pass-through facility and you wouldn't need to change your MySQL code at all.

If the table is now in SAS then there is no need to do everything in pure SQL.

/* sample data */
data product(drop=_i);
do _i=1 to 50;
total_cost=ceil(ranuni(1)*30);
output;
end;
run;

/* dedup and sort data by descending total_cost */

proc sort data=product out=inter nodupkey;

by descending total_cost;

run;

/* select 1st to 5th row and calculate min and max values */
proc sql;
create table want as
select min(total_cost) as min_total_cost, max(total_cost) as max_total_cost
from v_product(firstobs=1 obs=5)
;
quit;

/* alternative: calculate ranks and then select as required. */
/* for the example: max is rank 1 and min is rank 5 */
proc rank data=product ties=dense;
var total_cost;
ranks rank;
run;

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

nitesh_k — Tue, 11 Sep 2012 14:33:56 GMT

Patrick, I mentioned many a times in the other post.. not sure if you have read each comments of mine carefully..

Anyways, Let me summarize again for once and all.

We have below limitations with the query and data:

1. Need to get the min and max value of some column from the table by first ordering the complete data of the table on that column and then limiting the data from any row number to any row number using ONE single SQL.

2. No intention of getting just TopN values. It should give us min and max values of some column from say row number 11 to row number 20 of that table.

So don't worry about value being distinct or not. I used distinct because SAS by default does sorting on that column if we use distinct.

3. No sorting of data is allowed separately.

4. It has to be one single SAS SQL query.

NOTE: Refer the sample MySQL query in my other post. The same MySQL query we can write for other db vendors like Oracle, MSSQL, Teradata, etc.

But its just not possible in SAS, because of its limitations.

So I could only get close to that query by having some workaround using MONOTONIC function and distinct.

That's how I could close that post then and there.

And, this is the separate post, with the intention of concentrating on just MONOTONIC misbehavior. The query is working most of the time.

But suddenly fails in between if we fire the query again and again.

I hope everyone now understands the problem and limitations we have with the requirement. Please reply by keeping above points in context.

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

Astounding — Tue, 11 Sep 2012 15:29:20 GMT

Sorry to be this late to the party, but I have a couple of questions. First, suppose your original data looks like this:

What is the max of the first 5 rows? Should it be 1 or should it be 5? (Or is it not possible that your data will contain duplicate values?)

Second, I wanted to push and prod a little on the requirement to use a "single SQL". Clearly the LinusH solution creates two tables, each with its own CREATE statement. If the first one switched to creating a VIEW instead of a TABLE, would that be acceptable?

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

Patrick — Tue, 11 Sep 2012 23:34:50 GMT

@nitesh

- SAS is not a db vendor.

- distinct de-dups also. So using this in an inline view but then monotonic() in the outline view is not the same like the query in your other post.

- as Cynthia explained: monotonic() is UNSUPPORTED so forget about using it once and for all for commercial purposes

- that distinct leaves your data sorted is true but as this is undocumented as well no one promises you that this won't change in the next version or is true in all environments and architectures

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

PGStats — Wed, 12 Sep 2012 00:10:51 GMT

Hi Patrick, here is to support your last point. Look at the following example :

data test;
do x = 1,2,3,4,5,6,7,8,9; output; end;
run;

proc sort data=test out=stest nodupkey; by descending x; run;

proc sql;
select distinct x from stest;
quit;

----------

If the dataset is already sorted in reverse order by a unique key, the result is not as assumed. The presence of a unique index might do the same also.

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

Tom — Wed, 12 Sep 2012 00:11:25 GMT

"using ONE single SQL"

I still haven't seen any explanation for this requirement. Why is it important for you to use a single SQL statement? Especially if you are basing the requirement on database engines like MySQL and Oracle that allow your queries to use stored history so that the single query you want so much is no longer an independent query from the one that preceded it. Why not just create WORK datasets and query those? I am sure that MySQL and Oracle are using "spool" space that is effectively the same thing as a SAS work library.