sqlfun01.sas

SAS是功能強大的統計軟體

 /****************************************************************/
 /*          S A S   S A M P L E   L I B R A R Y                 */
 /*                                                              */
 /*    NAME: SQLFUN01                                            */
 /*   TITLE: fun/interesting applications of PROC SQL. (fun01)   */
 /* PRODUCT: BASE                                                */
 /*  SYSTEM: ALL                                                 */
 /*    KEYS: SQL DATMAN CREATE TABLE SELECT BETWEEN WHERE JOIN   */
 /*   PROCS: SQL                                                 */
 /*    DATA:                                                     */
 /*                                                              */
 /* SUPPORT: pmk                         UPDATE:                 */
 /*     REF:                                                     */
 /*    MISC: this example shows how one can implement a          */
 /*          recursive join with PROC SQL.                       */
 /*                                                              */
 /****************************************************************/

 title1 'SAS SAMPLE LIBRARY, MEMBER(SQLFUN01)';

 /*
  * notes: this is an example of fishing out a "bill-of-materials"
  *        type parts implosion using sql. The data are modelled
  *        after VAX PROCESS data, where each process may spawn
  *        sub-processes (who may in turn spawn sub-sub-processes.
  *
  *        to add a quirk, the VAX reuses process id's after the
  *        process dies, so ID is not unique... parentage is
  *        determined by a combination of parent ID, and the
  *        time span that the parent lived.
  *
  *        the data shows three processes, with subprocesses like
  *        this
  *
  *        1
  *        ---
  *        2
  *          3
  *          4
  *            5
  *              6
  *          7
  *        ---
  *        1
  *          2
  */


data rj;
 input type $1. id pid start stop;
 length stuff $5;
 stuff = type || put(id,1.) || put(pid,1.);
 cards;
p 1 0  1 10
p 2 0  5 11
s 3 2  7 12
s 4 2  8 12
s 5 4  9 15
s 6 5 10 15
s 7 4 10 15
p 1 0 12 17
s 2 1 13 18
run;



%macro rj;

  %local level nlevel;
  %let   level = 1;

  proc sql;

  * get first level as table l1;
  create table work.l1 as
  select s.id as sid, p.id, p.pid, s.start, 1 as level
    from rj s, rj p
   where s.pid =  p.id
     and s.pid ^= 0
     and s.start between p.start and p.stop;


  * now, recursively get successive levels.;
  * sql sets macro variable SQLOBS to the number of rows it processed.;
  %do %while(&SQLOBS > 0);

      %let nlevel = %eval(&level + 1);

      create table work.l&nlevel as
      select sid, p.id, p.pid, s.start, &nlevel as level
        from work.l&level s, rj p
       where s.pid =  p.id
         and s.pid ^= 0
         and s.start between p.start and p.stop;

      %let level = &nlevel;
      %end;


  * now, concatenate all levels together keeping leafs only.;
  * if you get more than 50 levels, will need another scheme.;
  * level - 1, cos last table is empty by definition.;
  data work.leaf;
    set %do i = 1 %to %eval(&level - 1);
          work.l&i
          %end;
        ;

    where pid = 0;
    keep sid id start level;
    run;


  * now merge original data, with ultimate parent info.;
  * (or alternatively, one could update a column in the original data.;
  proc sql;
  create table final as
  select rj.*, case leaf.id
                 when . then rj.id
                 else leaf.id
                 end
                   as ownerid,
               level
    from rj full join work.leaf
      on leaf.sid = rj.id
     and leaf.start between rj.start and rj.stop
       ;


  %mend;


* uncomment if youre curious;
* options mprint;

* so go do this "recursive join";
%rj;

* and display the results;
title2 'Lookee Here!';
select * from final;
quit;