Yes. I should have asked MONOTONIC() rather than ORDER BY MONOTONIC(). And essentially I was asking the order that PROC SQL follows to read the observations, which is random. I was confused because I thought that MONOTONIC() is something readily available before SQL joins multiple data sets hopefully—such as a.MONOTONIC() and b.MONOTONIC()—but actually SQL assigns the values for MONOTONIC() after joining processes, which also differs from how _N_ behaves. data a; do i=1 to 100; x=rannor(1); output; end; run; data bc; set a; if i>50; b=_n_; c=monotonic(); run; I considered both _N_ and MONOTONIC() as something similar to ROW() in Excel—and my understanding was (1) SQL processes observations in a random order and (2) ORDER BY MONOTONIC() enables one to preserve the original order, which was why I put the nonsensical statement—but it seems I need to be careful when the order matters. ... View more

I didn't say SAS was "broken." I was more asking the function of MONOTONIC() rather than ORDER BY MONOTONIC(), but it seems I need to more pinpoint my curiosity down. According to your replies, 1. MONOTONIC() is the function that gives 1 to the first observation it processes, 2 to the second observation it processes, and so forth. 2. The order that SQL follows to read observations is completely unknown. 3. Therefore, the resulting value that MONOTONIC() assigns to each observation is unpredictable in SQL. If these three are correct then, a. In a DATA step, MONOTONIC() returns orderly values to the observations. b. In a SQL query, MONOTONIC() doesn't necessarily return orderly values to the observations—even in a query with only one data, the orderly values for the observations are not guaranteed. c. In a DATA step, just adding an _N_ column is identical to just adding a MONOTONIC() column. d. Just adding a MONOTONIC() column in a DATA step and the same thing in a SQL query are not guaranteed to be identical. Am I correct? ... View more

"Changing the order that you reference the datasets in the FROM clause does not impose any restrictions on how SAS decides to implement your query." Changing the order affects the results if not FULL JOIN but just JOIN. Let me reuse your code. data a; /* a_row +1;*/ input a @@; b=rannor(1); cards; 1 2 3 4 ; data b; /* b_row+1;*/ input a @@; c=rannor(1); cards; 5 3 2 1 ; proc sql ; /*title 'Order by A_ROW';*/ title 'A JOIN B'; select * from a join b on a.a=b.a order by monotonic() ; /*title 'Order by B_ROW';*/ title 'B JOIN A'; select * from b join a on a.a=b.a order by monotonic() ; quit; Everything but the order is identical. Here are the results. They show the opposite sorts. What I want to know is why MONOTONIC() with FULL JOIN and MONOTONIC() with just JOIN behave in a different way. I know I can achieve the results by adding two DATA steps and going back and forth, but I want to understand what MONOTONIC() does exactly and save code lines if possible. A JOIN B a b a c 1 1.804823 1 -1.08332 2 -0.07992 2 0.396577 3 0.396577 3 -0.07992 B JOIN A a c a b 3 -0.07992 3 0.396577 2 0.396577 2 -0.07992 1 -1.08332 1 1.804823 As stated here, MONOTONIC() is an undocumented function and the way it assigns each number to each observation is not stated when it comes to aggregating multiple data sets—"If the MONOTONIC function is used in an SQL procedure that aggregates data then the function may return non-sequential or missing results." ... View more

Here are the data sets a, b, c, and d in a clockwise order. And here are the queries. proc sql; create table c as select coalesce(a.a,b.a) as a,b,c FROM A FULL JOIN B on a.a=b.a order by monotonic(); create table d as select coalesce(a.a,b.a) as a,b,c FROM B FULL JOIN A on a.a=b.a order by monotonic(); quit; There is no problem in the first query. The first observation for this query is the first observation of the data set a—i.e. (1,1.8048229506) because the FULL JOIN uses A first and then B. The problem is the second query. The FULL JOIN in the second query uses B first and then A, so the first observation for this second query should be the first observation of the data set b—i.e. (5,1.8048229506). However, SQL put the last observation of the data set b (1,-1.083317655) as the first observation despite the ORDER BY MONOTONIC() at the end. With the data set b only, the MONOTONIC() assigns 1 for (5,1.8048), 2 for (3,-0.0799), 3 for (2,0.3966), and 4 for (1,-1.0833), and the outer ORDER BY sorts the observations in this order. Why did SAS flip this order when the data set a is incorporated through FULL JOIN? This doesn't happen when not FULL JOIN but just JOIN—in this case, MONOTONIC() assigns 1 for (3,-0.0799), 2 for (2,0.3966), and 3 for (1,-1.0833) orderly, so the resulting data set respects their order as well. ... View more

I know SQL does random access. I only wondered what MONOTONIC() does when a query incorporates multiple data sets. It seems MONOTONIC() is unstable in these situations and only reasonable when a query with only one data set. Thanks. ... View more

What is MONOTONIC() doing here then? When it comes to JOIN rather than FULL JOIN, SQL displays the opposite order as follows. proc sql; create table c as select coalesce(a.a,b.a) as a,b,c from a join b on a.a=b.a order by monotonic(); create table d as select coalesce(a.a,b.a) as a,b,c from b join a on a.a=b.a order by monotonic(); quit; The order of the data set c is a=1,2,3, but that of the data set d is 3,2,1 because the data set b is in a descending order. Since MONOTONIC() assigns _n_ to each observation, shouldn't the data set d keep the original order a=5,3,2,1 of the data set b? ... View more

Very appreciate. I was looking at this document, but it does not use two INPUTs—does my code require another INPUT because of @? ... View more

This code (1) downloads the dmrs.tar.gz file (which contains just a dmrs.tar file), (2) removes the .gz part inflating the downloaded file. It seems SAS does not support the last piece of the puzzle—unbinding the members inside the .tar—yet. Thank you for this quick response. ... View more