turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- SAS Procedures
- /
- Help: Monotonic function is misbehaving when used ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

09-07-2012 03:06 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nitesh_k

09-07-2012 03:35 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

09-07-2012 03:55 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nitesh_k

09-07-2012 06:59 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to LinusH

09-07-2012 07:58 AM

*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:

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nitesh_k

09-07-2012 10:19 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nitesh_k

09-07-2012 01:12 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

09-09-2012 10:39 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

09-10-2012 09:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

09-10-2012 09:58 PM

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. */**

PG

PG

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to PGStats

09-10-2012 11:27 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

09-11-2012 08:31 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

09-11-2012 08:40 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nitesh_k

09-11-2012 09:46 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to nitesh_k

09-07-2012 01:51 PM

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 ;