topic Re: Monotonic() function in ODS and Base Reporting

Monotonic() function

deleted_user — Sun, 16 Sep 2007 00:30:51 GMT

I found the following code in an old program at my shop that uses monotonic() in a where statement. I know that monotonic is a SQL function that's not supported but here it is used in a proc sort.

Can someone please tell me how this works in the following context? The code returns the 10 highest scores. Thanks.

proc sort data = mydata out=top10(where=(monotonic() le 10));
by descending start_dt;
run;

Re: Monotonic() function

Cynthia_sas — Sun, 16 Sep 2007 04:14:15 GMT

Hi:
You might try Tech Support for the definitive answer. This note says it's not supported and, if used, might give incorrect results,
http://support.sas.com/techsup/unotes/SN/015/015138.html

Yet, these papers all mention it as a function that "sort of" works like _N_:
http://www2.sas.com/proceedings/sugi29/040-29.pdf
http://www2.sas.com/proceedings/sugi31/250-31.pdf
http://www.lexjansen.com/pharmasug/2006/tutorials/tu01.pdf

cynthia

Re: Monotonic() function

deleted_user — Mon, 17 Sep 2007 14:35:25 GMT

Thanks Cynthia.

The reason I posted my question was the use of monotonic in a where statement outside of SQL. I was thinking maybe this is a different function because it is not being used in SQL.

dan Message was edited by: danthesasman

Re: Monotonic() function

Cynthia_sas — Mon, 17 Sep 2007 15:30:32 GMT

Ah, I understand. I believe that there's only ONE where statement/clause processor and it lives in the PROC SQL routine. SAS did not have WHERE before PROC SQL. Tech Support would know for sure. I know that the WHERE statement in a SAS proc works the same way as a WHERE in SQL -- the type (character and numeric) have to agree. So, for example, in an IF statement, I can say
[pre]
if numvar = '1';
[/pre]
and the IF statement will very nicely do a conversion for me, but a WHERE statement:
[pre]
where numvar = '1';
[/pre]
gets you an error message. Which all indicates to me that ANYTHING you can do in WHERE (because it "belongs" to SQL) will work.

cynthia

Re: Monotonic() function

deleted_user — Mon, 17 Sep 2007 17:51:44 GMT

Thanks, I wasn't aware that WHERE was a part of SQL.

The funny thing is that I ran the code 2 different ways. The first with MONTONIC in SQL and the second time using WHERE in a PROC SORT. I got the correct results with the SORT but not with SQL.

dan

Re: Monotonic() function

JackHamilton — Mon, 17 Sep 2007 19:13:27 GMT

As Ian Whitlock once said, "You are probably getting something that you didn't expect because you didn't know what your program was telling the computer to do."

Using a data set that everyone has:

=====
proc sort data=sashelp.class out=top10 (where=(monotonic() le 10));
by age name;
run;

proc sql;
create table top10a as
select *
from sashelp.class
where monotonic() le 10
order by age, name;
quit;
====

The two output data sets are different because they're asking for different things. The data step equivalent of the SQL code is:

=====
proc sort data=sashelp.class (where=(monotonic() le 10)) out=top10b;
by age name;
run;
=====

Comparisons:

=====
64 proc compare base=top10 compare=top10b warning;
65 run;

WARNING: Values of the following 5 variables compare unequal: Name Sex Age Height Weight
WARNING: The data sets WORK.TOP10 and WORK.TOP10B contain unequal values.
NOTE: There were 10 observations read from the data set WORK.TOP10.
NOTE: There were 10 observations read from the data set WORK.TOP10B.
NOTE: PROCEDURE COMPARE used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds

66 proc compare base=top10a compare=top10b warning;
67 run;

NOTE: There were 10 observations read from the data set WORK.TOP10A.
NOTE: There were 10 observations read from the data set WORK.TOP10B.
NOTE: PROCEDURE COMPARE used (Total process time):
real time 0.03 seconds
cpu time 0.03 seconds
=====