/****************************************************************/ /*          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 10p 2 0  5 11s 3 2  7 12s 4 2  8 12s 5 4  9 15s 6 5 10 15s 7 4 10 15p 1 0 12 17s 2 1 13 18run;%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;