%macro hierarch (ds, vars); %* Copyright 1998-2000, Richard A. DeVenezia %* %* For a given dataset and list of hierarchal variables %* make a data view which can be used with AF legacy class %* Organizational Chart; %* %* Arguments: %* ds - source data set name %* vars - class variables that define a hierarchy %* %* Warning: %* No error checking is performed to ensure the variables %* listed in 'vars' are actually columns in data set 'ds'; %* %* 05/13/98 Initial coding ; %* 06/30/00 Comments by Benjamin Guralnik and RAD; %local ds vars i N h varlist; %* establish the value of macro variable 'varlist' as a %* comma delimited list of the hierarchy variables %* to be used later a SELECT DISTINCT clause; %let i = 1; %do %while (%scan(&vars,&i) ne); %if &i gt 1 %then %let varlist = &varlist,%scan(&vars,&i); %else %let varlist = %scan(&vars,&i); %let i = %eval (&i+1); %end; %* N is the number of class variables in the hierarchy; %let N = %eval (&i-1); %* create a view to the source data that contains %* the class level values and the number of occurences; proc sql; create view h as select *, count(*) as N from (select distinct &varlist from &ds); quit; %* open the source dataset and get the format of each class variable; %* store the class variable format in the indexed macro variable 'vf'; %let h = %sysfunc(open(&ds)); %if &h %then %do; %do i = 1 %to &N; %local vf&i; %let vf&i=%sysfunc(varfmt(&h,%sysfunc(varnum(&h,%scan(&vars,&i))))); %end; %let h = %sysfunc(close(&h)); %end; %* Traverse the view of class level values outputting parent/child relationships; data h2 / view = h2; set h; by &vars notsorted; %* use notsorted just in case the SQL view 'h' is unsorted; length node_val $25; format _n_ n 4.; drop n; %* every 2% of levels update a display of progress; window status rows=6 columns=20 #1 @4 _n_ ' of ' n; if 0 = mod (_n_ , 1+int(n/50)) then display status noinput; %* create the "documentElement" node in XML terms (BG); if _n_ = 1 then do; node_pop = .; node_id = 0; node_val = 'Drill Down'; output; end; %* _Ni are temporary data set variables used to maintain the %* value of the parent node ids that must be traversed to reach the current leaf; retain _N1-_N&N; drop _N1-_N&N; %do i = 1 %to &N; if first.%scan(&vars,&i) then do; %* a class level value has changed, update the parent node id; %if &i=1 %then node_pop = 0; %else node_pop = _N%eval(&i-1); ; %* the value to show in a node is the class level value; %* use the formatted value if the column is formatted; %if (&&vf&i=) %then node_val = %scan(&vars,&i); %else node_val = put(%scan(&vars,&i),&&vf&i); ; %* Each row in the output data set represents a node, %* hence increment node_id, the value that uniquely identifies each node; node_id + 1; %* make sure the current level node id is updated for this first. condition; %* it will be retained for all its children; _N&i = node_id; output; end; %end; run; %mend;
Sample code
/* This was an e-mail I sent to Benjamin on how to get started using this * macro along with the Organization Chart class; * submit these statements; data snapshot; set sashelp.vcatalg; where libname = 'SASHELP' and memtype = 'CATALOG' and memname in ('FSP','CLASSES'); ; run; %include 'hierarch.sas'; %hierarch (snapshot, libname memname objname); * now build a sample frame with these commands: * BUILD WORK.TEST.HIERARCH.FRAME * make a organizational chart object * for dataset enter WORK.H2 (created by hierarch macro) * click on Mapping list * for parent_node use variable node_pop * for current_node use variable node_id * for node variable select TEXT * for data set variable select NODE_VAL * click on the Add button under Mapping List * click OK * click on Chart appearance * uncheck Show all levels * check Use +,- and dotted lines * click OK * click on Node appearance * click on Select action * check Hide/unhide children * click OK * click OK * In Chart Style radio group, pick Directory * In Node Spacing group, enter 4 for Vertical * Now TESTAF to see the meta-data in an explorer like fashion */