%***********************************************************************;
%* This macro will convert a data set with a parent/child hierarchical *;
%* structure to a data set that used the level hierarchical structure  *;
%*                                                                     *;
%* numslvls - Maximum number of levels                                 *;
%* subset   - Value of parent column for the root node                 *;
%* prntcol  - Name of parent column                                    *;
%* chldcol  - Name of child column                                     *;
%* textcol  - Name of node text column                                 *;
%* indata   - Name of input data set                                   *;
%* outdata  - Name of output data set                                  *;
%* addvars  - List of COLON SEPARATED columns wanted on output dataset *;
%*                                                                     *;
%* Authors - Kathy Wisniewski/Tony Prier                               *;
%* Date: 18SEP1999                                                     *;
%***********************************************************************;
*options nomprint nomlogic nosymbolgen;

%macro pctolvl(numlvls=6,subset=,prntcol=,chldcol=,textcol=,indata=,outdata=,addvars=);

%let dsid = %sysfunc(open(&indata));
%let parvtype=%sysfunc(vartype(&dsid,%sysfunc(varnum(&dsid,&prntcol))));
%let dsid = %sysfunc(close(&dsid));

%do level=1 %to &numlvls; /* create a data set of each level code */

   data work.level&level(rename=(&chldcol=code&level &prntcol=prnt&level
                         &textcol=text&level));
      set &indata (keep=&prntcol &chldcol &textcol ) end=eof;

      %if &parvtype=C %then
      %do;
         %let sepby='","';
         where &prntcol in ( "&subset " );
      %end;
      %else %if &parvtype=N %then
      %do;
          %let sepby=',';
         where &prntcol in ( &subset );
      %end;
   run;

   proc sort data=level&level;
      by text&level;

   /* create a macro var with all distinct values for where clause */
   proc sql noprint;
      select distinct code&level
      into :subset separated by &sepby
      from work.level&level;
   quit;

%end; /* create a data set of each level code */

proc sql noprint;
   create table work.combine as
   select *
   from work.level1 full join work.level2
   on code1=prnt2;

%do level=2 %to %eval(&numlvls-1); /* combine each data set by parent */

   create table work.combine as
   select *
   from work.combine full join work.level%eval(&level+1)
   on code&level=prnt%eval(&level+1)
   order by code&level;
   ;

%end;  /* combine each data set by parent */

quit;

/* get things in the right order */
proc sort data=work.combine;
   by
   %do i=1 %to &numlvls;  /* all code fields */

      text&i

   %end;  /* all code fields */
   ;
run;


/* split the data set out again */
data &outdata;
  set work.combine;
  %if &addvars ne %then %str(keep code text level index;);
  %else %str(keep text level index;);

  array codes{*} $ code1-code&numlvls;
  array prnts{*} $ prnt1-prnt&numlvls;
  array texts{*} $ text1-text&numlvls;

  do i=1 to &numlvls; /* loop through every var */

     if codes{i} ne '' and codes{i} ne lag(codes{i}) then
     do; /* create a new record */

        code=codes{i};
        prnt=prnts{i};
        text=texts{i};
        level=i;
        index+1;
        output;

     end; /* create a new record */

  end; /* loop through every var */

run;
%if &addvars ne %then
%do;
   proc sql;
      create table &outdata as
      select one.text, one.level, one.index

      %let str=start;
      %let i=1;

      %do %while(&str ne );
         %let str= %scan(&addvars,&i,%str(:));
         %let i=%eval(&i+1);
         %if &str ne %then %str(,two.)&str;
      %end;

      from &outdata as one, &indata as two
      where one.code = two.&chldcol
      order by index;
   quit;
%end;


%mend pctolvl;
