SQLプロシジャで_N_やfirst. last.の処理を疑似的に表現する_列番号を返すmonotonicを利用して

SQLで_N_やfirst. last.を使えますか？と聞かれることがあります。

SQLは本来、データ格納の位置やソート順に縛られない言語なので
そういうのはデータステップでやってくださいなというところです。

実際、標準SQLには列番号を取得するような関数は意図的に存在しないのですが
実はSASのSQLプロシジャには、monotonic関数という独自関数があります。
こいつを使うことで、_N_やfirst. last.と結果が同じになる処理を行うことはできます。

本来のSQLの理念に反しているということで、賛否両論、批判的な人も多いですが
まあ、実際あるもんだから、知ってて損はないはずです。

以下のようなデータセットがあったとします。

data Q1;
 do X=1 to 5;
  do Y=1 to 3;
   output;
  end;
 end;
run;

if _N_=3で絞ってデータセットを作る処理、つまり3番目に格納されているobsのみ残すのを
をSQLプロシジャでやるなら

proc sql noprint;
 create table A1 as
  select X,Y
  from Q1
  where monotonic()=3;
quit;

となります。
monotonic()がいわゆる行番号を返します。

つまり

proc sql noprint;
 create table A2 as
  select X,Y,monotonic() as N
  from Q1;
quit;

の結果は

となります。

で、ここからは応用でfirst.Xまたはlast.Xのみを残す処理をかいてみます。
多分、すでに予想がついているかと思いますが、案の定group byとhaving及び、minまたはmax関数を使います

first.Xは

proc sql noprint;
 create table A3(drop=N) as
  select X,Y,monotonic() as N
  from Q1
  group by X
  having min(N)=N
;
quit;

で結果は

となりlast.Xは

proc sql noprint;
 create table A4(drop=N) as
  select X,Y,monotonic() as N
  from Q1
  group by X
  having max(N)=N
;
quit;

となります。

まあ、あくまで結果を同じにしているだけで、データステップと同じように動いているわけではないので注意。

【追記】
う～ん、matsu a  さんもリンク先で指摘されているので、あんまり頼らない方がいいかもしれませんね。。
http://sas-boubi.blogspot.jp/2014/02/sqlmonotonic.html