BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
5 REPLIES 5
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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
JackHamilton
Lapis Lazuli | Level 10
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
=====

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 4208 views
  • 0 likes
  • 3 in conversation