topic Re: Hierachy data (Parent Child) in SAS Programming

Hierachy data (Parent Child)

anandbillava — Mon, 27 Aug 2018 17:55:17 GMT

Hi There,

I have a data like this

data a;
a=1; b=5.1; c=5.5; output;
a=1; b=5.2; c=5.5; output;
a=1; b=5.0; c=5.2; output;

a=1; b=4.7; c=5.0; output;
a=1; b=4.6; c=5.2; output;

a=1; b=.; c=4.6; output;

run;

I am trying to create a output in which for a given parent nodie (variable c) it should get all the childs/nodes (variable b) which should not have any child/nodes. for e.g in the above example for parent node with value 5.5 it should output 5.1, 4.7 and 4.6 which do not have any child.

Here is how the output is picked.

5.5 has 5.1 and 5.2 child, then 5.2 has child 5.0, 5.0 has child 4.7 and 5.2 has child 4.6. 4.6, 4.7 and 5.1 do not have any childs.

Please let me know if you need more expalnation.

Thanks in advance for the help.

Anand

Re: Hierachy data (Parent Child)

PGStats — Mon, 27 Aug 2018 18:33:44 GMT

Are we to understand that a child node has a number < parent node number? Why not code the child node number as missing when it is greater than the parent node?

Re: Hierachy data (Parent Child)

PeterClemmensen — Mon, 27 Aug 2018 18:53:18 GMT

Is your desired result a data set? Can you show us what your desired result looks like?

Re: Hierachy data (Parent Child)

anandbillava — Tue, 28 Aug 2018 12:28:24 GMT

I this case the output will be

new variable

5.1

4.7

4.6

All this do not have any child nodes or children for the ancestor or parent 5.5.

Basically when user asks variable c with value 5.5 then output should be look like above.

Steps are

1. The program reads c variable where value is 5.5 and then looks for the children which is variable b and the values are 5.1 and 5.2

2. For each of b value which is 5.1 and 5.2 the program looks for children and their children are for 5.1 no children so it will appear in the report and 5.2 has children 5.0... and it goes on for all the childrens...

Re: Hierachy data (Parent Child)

Ksharp — Tue, 28 Aug 2018 15:05:14 GMT

data have(keep=_start _end);
a=1; b='5.1'; c='5.5'; output;
a=1; b='5.2'; c='5.5'; output;
a=1; b='5.0'; c='5.2'; output;

a=1; b='4.7'; c='5.0'; output;
a=1; b='4.6'; c='5.2'; output;

a=1; b=' '; c='4.6'; output;
rename c=_start b=_end;
run;
proc sql;
create table _have as
 select *
  from have where _start not in (select _end from have);
quit;


data want(keep=path want);
if _n_ eq 1 then do;
length path _path  $ 800 ;
if 0 then set have;
declare hash ha(hashexp:20,dataset:'have(where=(_start is not missing and _end is not missing))',multidata:'y');
ha.definekey('_start');
ha.definedata('_end');
ha.definedone();

declare hash pa(ordered:'y');
declare hiter hi_path('pa');
pa.definekey('n');
pa.definedata('n','path');
pa.definedone();

end;


set _have;
count=1;n=1;_n=1;
path=catx('|',_start,_end);
   
pa.add();
do while(hi_path.next()=0);
 if n ne 1 then pa.remove(key:_n);_n=n;
 _path=path;   
 _start=scan(path,-1,'|');
 rc=ha.find();
 if rc ne 0 then do; want=_start;output;end;
 do while(rc=0);
  if not findw(path,strip(_end),'|') then do;
   if length(path)+length(_end)+1 gt lengthc(path) then do;
    putlog 'ERROR: The length of path and _path are set too short';
    stop;
   end;
   
   count+1;n=count;
   path=catx('|',path,_end);
   pa.add(); 
   path=_path;
 end;
  rc=ha.find_next();
end;
end;
pa.clear();

run;

Re: Hierachy data (Parent Child)

anandbillava — Tue, 28 Aug 2018 15:30:52 GMT

Thank you very much. Its kind of working. But I need to output this based on value a and c. In this case the variable value for a is 1 and c is 5.5.

data a;
a=1; b=5.1; c=5.5; output;
a=1; b=5.2; c=5.5; output;
a=1; b=5.0; c=5.2; output;
a=1; b=4.7; c=5.0; output;
a=1; b=4.6; c=5.2; output;
a=1; b=.; c=4.6; output;

a=2; b=5.4; c=6.1; output;
a=2; b=5.3; c=6.1; output;
a=2; b=5.0; c=5.3; output;
a=2; b=4.7; c=5.0; output;
a=2; b=4.5; c=4.7; output;
run;

For the second set of record where a=2 and lets say value c is 6.1 the children output must be as below

5.4

4.5

Only these two do not have children.

I am sorry for not mentioning this case earlier.

run;

Re: Hierachy data (Parent Child)

Ksharp — Wed, 29 Aug 2018 11:41:56 GMT

You could split this table into two tables and running my code separately .

If you have many a, make a macro to go through all of a . That is not too difficulty .

Re: Hierachy data (Parent Child)

Ksharp — Wed, 29 Aug 2018 11:45:43 GMT

You could split this table into two tables and running my code separately .

If you have many a, make a macro to go through all of a . That is not too difficulty .

Re: Hierachy data (Parent Child)

Ksharp — Thu, 30 Aug 2018 14:47:09 GMT

OK. Since I have some time. Check the following code .


data have;
a=1; b=5.1; c=5.5; output;
a=1; b=5.2; c=5.5; output;
a=1; b=5.0; c=5.2; output;
a=1; b=4.7; c=5.0; output;
a=1; b=4.6; c=5.2; output;
a=1; b=.; c=4.6; output;

 

a=2; b=5.4; c=6.1; output;
a=2; b=5.3; c=6.1; output;
a=2; b=5.0; c=5.3; output;
a=2; b=4.7; c=5.0; output;
a=2; b=4.5; c=4.7; output;

rename c=_start b=_end;
run;



proc sql;
create table _have as
 select distinct a,_start
  from have where catx(' ',a,_start) not in (select catx(' ',a,_end) from have);
quit;


data want(keep=a _start  want);
if _n_ eq 1 then do;
if 0 then set have;
length key n path 8;
declare hash ha(hashexp:20,dataset:'have(where=(_start is not missing and _end is not missing))',multidata:'y');
ha.definekey('a','_start');
ha.definedata('_end');
ha.definedone();

declare hash pa(ordered:'y');
declare hiter hi_path('pa');
pa.definekey('n');
pa.definedata('n','path');
pa.definedone();

declare hash k();
k.definekey('key');
k.definedone();
end;


set _have;
count=1;n=1;_n=1;
path=_start;pa.add();
key=_start;k.add();
do while(hi_path.next()=0);
 if n ne 1 then pa.remove(key:_n);_n=n;
 rc=ha.find(key:a,key:path);
 if rc ne 0 then do; want=path;output;end;
 do while(rc=0);
  if k.check(key:_end) ne 0 then do;
   count+1;n=count;
   path=_end;
   pa.add();   
   key=_end;
   k.add();
 end;
  rc=ha.find_next();
end;
end;
pa.clear();	k.clear();

run;

Re: Hierachy data (Parent Child)

anandbillava — Thu, 30 Aug 2018 19:35:06 GMT

Thank you.. I am able to solve this issue with another approach. Thanks for all your help

Re: Hierachy data (Parent Child)

anandbillava — Mon, 05 Aug 2019 20:15:03 GMT

Can you help me in solving problem below. I am providing here the appropriate ancestor for each of the record. I need to find this ancestor for each of the record by user. Can you please help.

data a;

user=1; base=4.1; cur=4.2; output; /* 4.1 to 4.2 - Ancestor here is 4.4 */

user=1; base=4.2; cur=4.4; output; /* 4.2 to 4.4 - Ancestor here is 4.4 */

user=1; base=4.6; cur=5.5; output; /* 4.6 to 5.5 - Ancestor here is 6.1 */

user=1; base=5.5; cur=6.1; output; /* 5.5 to 6.1 - Ancestor here is 6.1 */

user=2; base=4.2; cur=4.6; output; /* 4.2 to 4.6 - Ancestor here is 6.1 */

user=2; base=4.6; cur=5.5; output; /* 4.6 to 5.5 - Ancestor here is 6.1 */

user=2; base=5.5; cur=6.1; output; /* 5.0 to 6.1 - Ancestor here is 6.1 */

user=3; base=4.0; cur=4.2; output; /* 4.0 to 4.2 - Ancestor here is 5.5 */

user=3; base=4.2; cur=4.6; output; /* 4.2 to 4.6 - Ancestor here is 5.5 */

user=3; base=4.6; cur=5.5; output; /* 4.6 to 6.1 - Ancestor here is 5.5 */

run;

I am able to achieve this if there is only one user in the dataset using code below. But its not working if there are more than one user.

data _tmp1(rename=(base=base1 cur=cur1));

set a;

run;

**Repeat the training module for each of the delta version between base and current module;

data _tmp2;

retain base cur;

set _tmp1; by u;

cur = cur1;

base = base1;

output;

drop base1 cur1 i;

run;

**Get all the base version for the current modue which has training then need to check the

corresponding base version full access;

data _lineage;

set _tmp2; by u;

array hist(6);

count=1;

hist(count)=base;

do i=1 to last while (count<dim(hist)-1);

set _tmp2(rename=(base=base1 cur=cur1)) nobs=last point=i;

if cur=base1 then do;

count+1;

hist(count)=base1;

base=base1;

cur=cur1;

i=1;

end;

count+1;

hist(count)=cur;

ancest = cur;

run;

Re: Hierachy data (Parent Child)

Ksharp — Tue, 06 Aug 2019 14:06:51 GMT

You can split the table into three tables and run your code separately ? data user1; set a; if user=1; run; ...........

Re: Hierachy data (Parent Child)

anandbillava — Tue, 06 Aug 2019 15:23:55 GMT

Hi ,

I have lot of records in the dataset not just 3. It will take lot of time to process if i write a macro to subset these. Is there a way i can do it in one step.

Thank you,

Re: Hierachy data (Parent Child)

andreas_lds — Tue, 06 Aug 2019 16:33:34 GMT

@anandbillava wrote:

Hi ,

I have lot of records in the dataset not just 3. It will take lot of time to process if i write a macro to subset these. Is there a way i can do it in one step.

Thank you,

Creating subsets won't decrease the time required to create the final result. Do you get any errors while processing the complete dataset.

Re: Hierachy data (Parent Child)

anandbillava — Tue, 06 Aug 2019 17:12:18 GMT

I will have to create more than 4000 datasets which is am already doing and its taking lot of time to process.

Re: Hierachy data (Parent Child)

ErikLund_Jensen — Tue, 06 Aug 2019 21:10:55 GMT

Hi @anandbillava

Here is a different approach where splitting the input data set is avoided. I think it gives the wanted output.

Orphans are identified by a SQL join, where the input data set is joined by itself with match on user as as a join criteria, so it holds childs and parents with the same user together.

Ancestors are handled by a format to set parents on childs, and this it done in a loop, until the chain is exhausted. It makes use of extra variables, where user-base, resp user-cur, are concatenated, so user becomes part of the key.


/* test data 1 */
data have1;
	format user 8. cur base 10.1;
	user=0; base=5.1; cur=5.5; output;
	user=0; base=5.2; cur=5.5; output;
	user=0; base=5.0; cur=5.2; output;
	user=0; base=4.7; cur=5.0; output;
	user=0; base=4.6; cur=5.2; output;
	user=0; base=.; cur=4.6; output;
	user=2; base=5.4; cur=6.1; output;
	user=2; base=5.3; cur=6.1; output;
	user=2; base=5.0; cur=5.3; output;
	user=2; base=4.7; cur=5.0; output;
	user=2; base=4.5; cur=4.7; output;
run;

/* find orphans */
proc sql;
	create table orphans as 
		select a.user, coalesce(a.base,a.cur) as orphan format=3.1 
		from have1 as a left join have1 as b
		on a.user = b.user and (b.cur = a.base or b.cur = .)
		where b.user = .;
quit;

/* test data 2 */
data have2;
	format user 8. cur base 10.1;
	user=1; base=4.1; cur=4.2; output; /* 4.1 to 4.2 - Ancestor here is 4.4 */
	user=1; base=4.2; cur=4.4; output; /* 4.2 to 4.4 - Ancestor here is 4.4 */
	user=1; base=4.6; cur=5.5; output; /* 4.6 to 5.5 - Ancestor here is 6.1 */
	user=1; base=5.5; cur=6.1; output; /* 5.5 to 6.1 - Ancestor here is 6.1 */
	user=2; base=4.2; cur=4.6; output; /* 4.2 to 4.6 - Ancestor here is 6.1 */
	user=2; base=4.6; cur=5.5; output; /* 4.6 to 5.5 - Ancestor here is 6.1 */
	user=2; base=5.5; cur=6.1; output; /* 5.0 to 6.1 - Ancestor here is 6.1 */
	user=3; base=4.0; cur=4.2; output; /* 4.0 to 4.2 - Ancestor here is 5.5 */
	user=3; base=4.2; cur=4.6; output; /* 4.2 to 4.6 - Ancestor here is 5.5 */
	user=3; base=4.6; cur=5.5; output; /* 4.6 to 6.1 - Ancestor here is 5.5 */
run;
		
/* Find ancestors */
data w; set have2 end=eof;
	type = 'C'; hlo=''; fmtname = 'par';
	start = put(user,z8.)||'-'||put(base,z10.1);
	label = put(user,z8.)||'-'||put(cur,z10.1);
	output;
	if eof then do;
		hlo = 'O'; label='';
		output;
	end;
run;
proc format cntlin=w;
run;
data ancestors; 
	drop type hlo fmtname start label c w inter;
	set w (where=(hlo=''));
	format ancestor 10.1;
	c = 0;
	do until (w = .);
		c = c + 1;
		if c = 1 then inter = put(start,$par.); else inter = put(inter,$par.);
		w = input(scan(inter,2,'-'),10.1);
		if w ne . then ancestor = w;
	end;
run;