topic Re: Find all parents and children for each record. in SAS Enterprise Guide

Find all parents and children for each record.

RVO — Wed, 20 Nov 2024 12:02:50 GMT

I'm stuck trying to go from this table:

cid	parent_cid
a
b	a
c	b
d	a
e
f
g	f

To this table:

cid	sort_cid	sort_chain	level
a	a	1.1.1	1
a	b	1.2.1	2
a	c	1.2.2	3
a	d	1.3.1	2
b	a	1.1.1	1
b	b	1.2.1	2
b	c	1.2.2	3
c	a	1.1.1	1
c	b	1.2.1	2
c	c	1.2.2	3
d	a	1.1.1	1
d	d	1.3.1	2
e	e	2.1.1	1
f	f	3.1.1	1
f	g	3.2.1	2
g	f	3.1.1	1
g	g	3.2.1	2

In short, I want for every cid every parent and child cid in the 'chain' as sort_cid. Next to that, for every sort_cid I need a dot-separated chain of numbers, to be able to sort the sort_cid in a Viya Dashboard. I also need to know which 'level' each sort_cid is, e.g. if it is the main parent (level 1) or it has two parents (level 3).

Any help would be much appreciated!

Re: Find all parents and children for each record.

PeterClemmensen — Wed, 20 Nov 2024 12:23:08 GMT

Why is level = 2 in the 4th obs?

Re: Find all parents and children for each record.

Tom — Wed, 20 Nov 2024 15:54:22 GMT

I do not get your output. But here is a simple way you can load the parent/child pairs into a hash object and use that to generate a list of each person and which family and what level within they family they are.

data have ;
  input cid $	parent_cid $;
cards;
a	.
b	a
c	b
d	a
e	.
f	.
g	f
;

data want;
  set have ;
  where missing(parent_cid);
  family+1;
if _n_=1 then do;
  declare hash h(multidata:'yes',dataset:'have(where=(not missing(parent_cid)))');
  h.definekey('parent_cid');
  h.definedata('cid');
  h.definedone();
end;
 do generation=1 by 1 until(h.find());
    output;
    parent_cid=cid;
 end;
run;

Result

              parent_
Obs    cid      cid      family    generation

 1      a                   1           1
 2      b        a          1           2
 3      c        b          1           3
 4      e                   2           1
 5      f                   3           1
 6      g        f          3           2

Re: Find all parents and children for each record.

RVO — Wed, 20 Nov 2024 16:54:22 GMT

Because a is the parent of d. So d is the child of a. Since a = level 1, then d = level 2. Another way to see it: b and d are siblings :). This can be deducted from level = 2.

What I want to achieve:
I want to use the level-column to put spaces in front of the cid (which in my real data is a company name), together with the sort_chain to sort the data in viya. My final result in Viya should look like this:

In Viya I want to select a company (using cid), and then want to display the full company-chain to the user.

So if a user selects 'a', they should see this in Viya in a list table:

The sort_chain is used to make sure the results are always displayed in this order.

Re: Find all parents and children for each record.

RVO — Wed, 20 Nov 2024 16:50:03 GMT

Hi Tom,

Thank you for taking the time to help me out!

I have replied above to another comment to further explain my output (and why it is needed).

I have no experience with hash-objects, so I do not fully understand the workings of the code.

The output you have generates is a step in the right direction, but record [d a] is missing. I do not fully understand from the code why that would happen (unless a hash in SAS is the equivalent of a dictionary in Python? Then one cannot have duplicate keys).

Hope this together with my other comment clarifies it a bit. I understand the output might be 'strange', but Viya is a strange program to work with, so sometimes unconventional tables must be loaded into it to create the output a user wants 😉

Re: Find all parents and children for each record.

quickbluefish — Wed, 20 Nov 2024 18:27:42 GMT

I think my brain melted trying to understand the 'sort_chain' variable, but the following code produces the rest of them in the format you specified - I think I'm just not understanding how to derive sort_chain from this.


data have;
infile cards dsd truncover firstobs=1 dlm=',';
length cid parent_cid $1;
input cid parent_cid;
cards;
a,
b,a
c,b
d,a
e,
f,
g,f
;
run;

proc sql noprint;
create table c2num as
select 'fc2num' as fmtname length=8, 
'C' as type length=1, cid as start, monotonic() as label
from have;
quit;

data num2c (rename=(_start=start _label=label));
set c2num (rename=(start=_label label=_start));
fmtname='fnum2c';
type='N';
run;

data c2num;
set c2num end=last;
output;
n+1;
if last then do;
	start=' ';
	label=0;
	output;
	call symputx("ncids",n);
end;
drop n;
run;

proc format cntlin=c2num; run;
proc format cntlin=num2c; run;

data want;
set have (rename=(cid=_CID parent_CID=_PC)) end=last;
array nparents {0:&ncids} _temporary_ (%eval(&ncids+1)*0);
array p {&ncids} _temporary_;
nparents[put(_CID,$fc2num.)*1]+nparents[put(_PC,$fc2num.)*1]+1;
p[put(_CID,$fc2num.)*1]=put(_PC,$fc2num.)*1;
if last then do;
	do i=1 to dim(p);
		cid=put(i,fnum2c.);
		sort_cid=cid;
		level=nparents[i];
		output;
		do j=1 to dim(p);
			if p[j]=i then do;
				sort_cid=put(j,fnum2c.);
				level=nparents[j];
				output;
				do k=1 to dim(p);
					if p[k]=j then do;
						sort_cid=put(k,fnum2c.);
						level=nparents[k];
						output;
					end;
				end;
			end;
		end;
		level=nparents[i];
		_i=i;
		do while (level>1);
			sort_cid=put(p[_i],fnum2c.);
			level=nparents[p[_i]];
			output;
			_i=p[_i];
		end;
	end;
end;
keep cid sort_cid level;
run;

proc sort data=want; by cid sort_cid; run;

proc print data=want; run;

Re: Find all parents and children for each record.

Tom — Wed, 20 Nov 2024 20:35:42 GMT

So you just need to calculate the depth for each CID and which CID is at the top of their tree. Then you can use BY group processing to create your various counters.

data want;
* Load the cid->parent_cid mapping into a hash ;
  if _n_=1 then do;
    declare hash h(dataset:'have');
    h.definekey('cid');
    h.definedata('parent_cid');
    h.definedone();
  end;
* Load next CID value ;
  set have ;
* Find their top ancestor and how deep they are ;
  do depth=1 by 1 until(h.find());
    top=cid;
    cid=parent_cid;
  end;
* Re-load the same CID value to reset CID and PARENT_CID ;
  set have;
run;

proc sort;
  by top depth cid ;
run;

* find how many levels will be needed ;
proc sql noprint;
  select max(depth) into :md trimmed from want;
quit;

* Use FIRST. processing to get the counters ;
* Concatenate to make index string ;
data numbered;
  set want;
  by top depth cid;
  array c[&md] ;
  c[depth]+1;
  if first.depth then do;
     do j=depth+1 to &md;
       c[j]=1;
     end;
  end;
  drop j ;
  sort_chain=catx('.',of c[*]);
run;

Results

NOTE: If none of the individual counters exceed 9 then you can generate your sort key variable just using CATX() as in this program. But if you do have values like 10 then you might want to code a loop so you can use the Z format to generate strings like 1.10.1 and 1.09.1 instead. Those will sort properly.

Re: Find all parents and children for each record.

quickbluefish — Wed, 20 Nov 2024 23:07:44 GMT

That's really interesting - I understand the code more or less (I've never set the same dataset twice -seems much less awkward than having to store the original values in separate variables) and understand how you're deriving the sort chain now, but I still don't think I follow what the purpose of it is - like, what is "1.3.1" actually telling us as it relates to CID='d', parent_CID='a'? Doesn't 'd' have a depth of 2?

Re: Find all parents and children for each record.

Tom — Wed, 20 Nov 2024 23:24:03 GMT

That step reads the same dataset three times since it is also loaded into the HASH.

If we stick to the parent/child analogy we have A as the head of the first family, so 1 for the first place. D is the second child of A, so since A was number 1 for that second position D is number 3. And since the depth of D is two you have 1 for all of the other levels since D is the head that sub-tree.

Re: Find all parents and children for each record.

quickbluefish — Wed, 20 Nov 2024 23:26:40 GMT

Mind blown... thank you!

Re: Find all parents and children for each record.

RVO — Thu, 21 Nov 2024 09:03:40 GMT

Hi Tom,

Once again thank you for thinking along!

The resulting dataset looks almost right, however what does not seem correct is obs. 4: this should be 1.2.2, not 1.3.2. Also, the list needs the 'top' column to show 'b', and who are related to 'b' (which are 'a' and 'c').

I will give a full explanation why the format is needed, starting from my final table in Viya, working backwards towards the dataset I have.

My client wants to have an overview of all companies within a certain sector (these are 1000's of companies, so to answer your question: yes, many values will be larger than 10). These companies are related to each other (holding company, daughter companies, branches, etc.). A company can have multiple daughters/branches, but each company only one parent. In the dashboard, these companies need to be shown with their relation to each other in a listtable. Each company is operating in certain sub-sectors. So if the user selects a certain sub-sector, a subset of the companies is returned. BUT, and this is the important part: each company that is returned, must have all the parent/daughter (NOT sister) companies displayed, to understand where in the global sector a company operates.

The solution to this is to send a table like this to Viya*:

(note the spaces in front of sort_name, this is derived from the 'level' column in EG, level 1 = no spaces, level 2 = 2 spaces, level 3 = 4 spaces, etc.).

Note that each company (in another table) is linked to sub-sectors via datamapping.

I will use an excel pivot table to mimic the Viya behavior. The following listtable is created in Viya, normally the 'sort chain' column is hidden (now shown for clarity). The result would be:

Say the user select subsector 1. And companies 'b' and 'f' are active in that sector. Then the result would be:

'e' dropped out, nothing to do with the subsector. But 'a', 'c' and 'g' are shown, because they are related to a company in the chain. Note: in the final version, 'b' and 'f' will be highlighted to show they are the direct result of the filter, while the rest is to show the company structure within the sector (I have that part working if I load the desired output table into Viya when manually created from a small subset of the data).

The whole reason that the above table works, is because:

I have the sort-chain column.
I have the level-column to create spaces in front of c company name to make the result human-readable.
And most important: for each company in the list all parents and children are in the table.

To further clarify point 3, while using my original example table in my original post:

Company 'a' is linked to 3 other companies. Hence, for cid 'a', we get sort_cid 'a' (itself), 'b' (first daughter), 'c' (first daughter of first daughter), 'd' (second daughter).

Company 'b' is linked to 2 other companies, being 'a' (its parent), and 'c' (its daughter). So 'b' inherits the first digit from 'a', and digit two gets a +1.

Company 'd' is linked to 1 other company, being 'a' (its parent). So 'd' inherits the first digit from 'a' and digit two gets a +1 (i.e. 3).

I have also created the below identical working example, but have reduces all sort_chain numbers by 1, maybe that is more logical for some people:

Data sent to Viya:

Basic Viya listtable:

User selects companies 'b' and 'f'

Re: Find all parents and children for each record.

RVO — Thu, 21 Nov 2024 09:16:25 GMT

Hi quickbluefish,

Thank you as well for trying to help me out! Your output table looks (except for the sort_chain) like my output table! I have in another reply tried to better explain the sort_chain (how it works, a different variant that might feel more 'logical', and why it is needed).

My main problem is: I do not understand how your code works (I've only just started to program in SAS EG).
On a high level I understand that you loop over parent>child (c2num?) and child>parent (num2c?) tables, to get the parents of every child, and the children of every parent. But I do not understand most parts of the code what it does.

Re: Find all parents and children for each record.

Tom — Thu, 21 Nov 2024 13:16:34 GMT

So you just want to add extra observations?

Re: Find all parents and children for each record.

RVO — Thu, 21 Nov 2024 13:21:55 GMT

Yes, there will be additional observations (1000's of companies). And chains can be long: a company can have many children, grandchildren, grand-grandchildren, etc. (10+ levels deep).

Re: Find all parents and children for each record.

Tom — Thu, 21 Nov 2024 13:36:24 GMT

I do not see how yours is much different than what I produced.

(Hard to use your photographs since I cannot copy the text from a photograph).

It looks like you just want to add the extra observations to replicate the parents that have many children. So instead of just having A 1.1.1 a single time you want it replicated.

Re: Find all parents and children for each record.

Tom — Thu, 21 Nov 2024 13:42:02 GMT

Converting CID and LEVEL to SORT_NAME is simple using the REPEAT() function.

Note that the number passed to REPEAT() is the number of EXTRA copies to produce so it always produces at least one copy. If you want the SORT_NAME to look indented like that when printed to ODS destinations then use non-breaking space ('A0'x) instead of a normal space.

sort_name=substr(repeat('A0'X,depth)||cid,3);

Re: Find all parents and children for each record.

Tom — Thu, 21 Nov 2024 13:48:20 GMT

@RVO wrote:
Yes, there will be additional observations (1000's of companies). And chains can be long: a company can have many children, grandchildren, grand-grandchildren, etc. (10+ levels deep).

So did you try your visualization in VIYA with an example where one of the values between the periods is larger than 9?

1.1.1
1.2.1
1.3.1
...
1.9.1
1.10.1
1.11.1

Did it work as you wanted it? Did it sort properly?

Re: Find all parents and children for each record.

RVO — Thu, 21 Nov 2024 14:04:58 GMT

Correct. Note the sort_chain is 1-on-1 coupled to sort_name (each <space><space>b has sort_chain 1.2.1). This is as mentioned to use it in Viya as required.

I will show you where ours differ (and that is a very important difference):

The two highlighted records should be the same, but mine (correctly) shows 1.2.2, whereas your version shows 1.3.2, which is incorrect, since c is not a daughter of d (1.3.1).

I understand it is difficult to understand when it is not fully clear why an output is needed. But Viya has many peculiarities compared to coding a user interface where each visual can have SQL code behind it, especially if a company (like the one I work for) tries to misuse Viya for a CRM-system (that is the main problem ;))

Re: Find all parents and children for each record.

RVO — Thu, 21 Nov 2024 14:21:00 GMT

Hi Tom, in reply to both your questions:

I understand that the current example breaks when obs > 9. But in my final code (already use it for other parts in my EG script) will use z5. (up to 9999 unique parent companies/daughters/etc.) Can always upgrade to z6., z7, etc.

An example where I already use it in another part of my script:

 sort_id = cats(sort_id, put(new_id, z3.));

Example result in Viya for that code:

FYI: Viya does not sort characters after the 5th character (in my testing), so I actually replace the sort_id buy a sort_number in code and use that in Viya:

data &table_out(drop=sort_id);
	set &table_out;
	by sort_id;

	if first.sort_id then
		hierarchy_sort_number+1;
run;

By using first. all sort_id's that are identical get the same sort_number.

Regarding repeat, using this for the example in this reply:

hierarchy_sort_name = substr(cat(repeat("  ", %eval(&i-1)), hierarchy_level_&i), 3);

The tip to use 'A0'x is much appreciated. I currently replace the spaces in Viya in a calculated column with this unicode character: https://www.compart.com/en/unicode/U+2800 (braille blank pattern), but I like your suggestion better. When I just tried to copy the unicode charater in EG, it gave me an error and refused to run the script.

The code this is part of actually creates the same effect, but there the hierarchy data is in separate columns (hierarchy_level1, hierarchy_level2, etc). This is just the way the source data is formatted. For some reason that was easy for me to create. But to achieve the same with only parent-child data, recursive coding (CTE?) is required, and I am unfamiliar with it.

Re: Find all parents and children for each record.

Tom — Thu, 21 Nov 2024 14:46:26 GMT

So that is actually perhaps a little easier.

To start with build a string with CID values of all of the parents of the current node.

data want;
* Load the cid->parent_cid mapping into a hash ;
  if _n_=1 then do;
    declare hash h(dataset:'have');
    h.definekey('cid');
    h.definedata('parent_cid');
    h.definedone();
  end;
* Load next CID value ;
  set have ;
  length parents $20;
* Find their parents ;
  do depth=1 by 1 until(h.find(key:parent_cid));
    parents=catx('|',parent_cid,parents);
  end;
  keep cid parents depth ;
run;

Now you can generate the numbers you want.

proc sort;
  by parents cid;
run;

data want;
  set want;
  by parents ;
  order+1;
  if first.parents then order=1;
run;

And then again use a HASH to get the order numbers of the parents of the current node.

data numbered ;
  set want;
  if _n_=1 then do;
    declare hash h(dataset:'want');
    h.definekey('cid');
    h.definedata('order');
    h.definedone();
  end;
  array c[&md];
  c[depth]=order;
  do i=depth+1 to &md;
    c[i]=0;
  end;
  do i=1 to depth-1;
    h.find(key:scan(parents,i,'|'));
    c[i]=order;
  end;
  drop i;
  length sort_name $20 sort_index $30 ;
  sort_name=substr(repeat('A0'x,depth)||cid,3);
  sort_index=catx('.',of c[*]);
run;
proc sort;
  by sort_index;
run;

Result: