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

[Patrick]

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;

[nitesh_k]

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.

[Astounding]

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

1

1

1

1

1

2

3

4

5

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?

[Patrick]

@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

[PGStats]

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;

  x

----------

  9

  8

  7

  6

  5

  4

  3

  2

  1

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.

PG

[Tom]

"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.