Recursive Query with PROC SQL

The SAS implementation of SQL is not as robust as most relational databases. One example where SAS falls short is in the area of recursive queries. I know that SQL Server and PostgreSQL handle recursive queries via the use of common table expressions or CTEs using the WITH statement. Hopefully SAS will add this feature in a future release since CTEs were included in the SQL:1999 standard.

In the mean time, I have emulated an existing article "Learn PostgreSQL Recursive Query By Example". Please reference that article for additional details and to compare the different techniques. Credit must be given to my co-worker and excellent SAS programmer, Dave Devoll for the concept he used recently.

data employees;
  infile datalines dsd;
  input 
    employee_id : 4.
    full_name : $32.
    manager_id : 4.
  ;
  datalines;
1, 'Michael North', .
2, 'Megan Berry', 1
3, 'Sarah Berry', 1
4, 'Zoe Black', 1
5, 'Tim James', 1
6, 'Bella Tucker', 2
7, 'Ryan Metcalfe', 2
8, 'Max Mills', 2
9, 'Benjamin Glover', 2
10, 'Carolyn Henderson', 3
11, 'Nicola Kelly', 3
12, 'Alexandra Climo', 3
13, 'Dominic King', 3
14, 'Leonard Gray', 4
15, 'Eric Rampling', 4
16, 'Piers Paige', 7
17, 'Ryan Henderson', 7
18, 'Frank Tucker', 8
19, 'Nathan Ferguson', 8
20, 'Kevin Rampling', 8 
;
run;

The very short macro function code for %isblank can be found here. The initial query in the below code only returns a single row for employee_id = 2, Megan Berry. After that, a test is made to ensure that at least one row was returned. If not, an error message will be generated and the macro will terminate.

If all is good, the output that was written to the lev0 data set is copied to a data set named results. This is done so it can be used to append subsequent query results. The next SQL statement uses a sub-query to reference back to the prior data set using %eval(&iter -1). If that query returned rows (&sqlobs > 0) then it is append to the results data set and is repeated until no rows are returned.

%macro recursive(id =, iter = );
  %if %isblank(&id) %then %do;
    %put %str(E)RROR: Must pass in a PK value.;
    %return;
  %end;

  %if %isblank(&iter) %then %do;
    proc sql;
      create table lev0 as
        select    employee_id
                , full_name
                , manager_id
        from      employees
        where     employee_id = &id
      ;
    quit;

    %if &sqlobs = 0 %then %do;
      %put %str(E)RROR: No entries found using ID = &id;
      %return;
    %end;

    %if %sysfunc(exist(results)) %then %do;
      /* Clear out old version should it exist */
      proc delete data = results;
      run;
    %end;

    data results;
      set lev0;
    run;

    %let iter = 1;
  %end;

  proc sql;
    create table lev&iter as
      select      employee_id
                , full_name
                , manager_id
      from        employees
      where       manager_id in(
        select    distinct employee_id
        from      lev%eval(&iter - 1))
    ;
  quit;

  %if &sqlobs %then %do;
    proc append
      base = results
      data = lev&iter
    ;
    run;
    
    %recursive(id = &id, iter = %eval(&iter + 1 ));
  %end;

%mend;

%recursive(id = 2);