the ancestors for each children - and more data describing them

[ANLYNG]

Hi ,

I got a input dataset like this

FROM_DATE TO_DATE Ratio child parent 14Jan2011 0:00:00 30May2012 0:00:00 66,67 273704 272275 14Jan2011 0:00:00 30May2012 0:00:00 33,33 273704 273825 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294288 31May2012 0:00:00 06Mar2018 0:00:00 32,80 273704 273825 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294292 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294278 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294296 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294295 07Mar2018 0:00:00   12,44 273704 294288 07Mar2018 0:00:00   50,24 273704 312646 07Mar2018 0:00:00   12,44 273704 294292 07Mar2018 0:00:00   12,44 273704 294278 07Mar2018 0:00:00   12,44 273704 294295
I have some code which find parent child relations and output this: hist1 hist2 hist3 hist4 hist5 273704 272275       273704 273825 278642     273704 294288 272275     273704 273825 278642     273704 294292 283153     273704 294278 283167     273704 294296 283206     273704 294295 283191     273704 294288 272275     273704 312646 278642     273704 294292 283153     273704 294278 283167     273704 294295 283191

I want in addition the from_date and to_date and ratio to appear in the same manner .i.e.g resolved by the values on the input line :

Is that possible and how???? I suppose I need more arrays – but how to do that simple in my code?????

I will be happy if you could edit my program as included below thanks in advanve. Look forward to see it.

Eg.

hist1

hist2

hist3

hist4

hist5

hist1_fromdate	hist2_fromdate	hist3_fromdate	hist4_fromdate	hist5_fromdate
hist1_todate	hist2_todate	hist3_todate	hist4_todate	hist5_todate
hist1_ratio	hist2_ratio	hist3_ratio	hist4_ratio	hist5_ratio

 Here is my program:

data lineage(keep=hist1-hist5);

/* Read in SAS data set */

Set my_inputdata;

/* Create an array with enough elements to hold the maximum number of */

/* observations that will 'link' together.                           */

array hist(5);

   count=1;

   /* Put the value of CHILD variable into the array HIST */

   hist(count)=child;

/* Process through the entire data set while the value of COUNT is less   */

/* than 4, i.e. the upper boundary of the array minus 1.                 */

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

   /* Read in SAS data set again. Rename the variables to new names so     */

   /* they can be compared with the variable names coming in with the first */

   /* SET statement. The POINT= option allows you to access each observation */

   /* by observation number.                                                                          */

   set my_inputdata (rename=(child=child1 parent=parent1)) nobs=last point=i;

   /* As you step through each observation, compare the value of PARENT to the */

   /* value of CHILD1 (which is the new name given to the CHILD variable when */

   /* data set is brought in second time).                                     */

   if parent=child1 then do;

     count+1;

     /* populate array with value of CHILD1 */

     hist(count)=child1;

     child=child1;

     parent=parent1;

     i=1;

   end;

end;

/* increment COUNT */

   count+1;

   hist(count)=parent;

run;

[Reeza]

Where does 278642 come from?

Please make the example data and solutions align so we can understand the logic, ie the data shown in the output should be what you would get if you ran the solution on your shown input data. 

273704

273825

278642

You should also search recursive search or tree searches on here. 

The subgraph macro (I posted a link in your previous posts) will align the groups but not sure about the dates at this point. I'll try it when you post the updated data and answers to my questions above. 

---

@ANLYNG wrote:

Hi ,

I got a input dataset like this

FROM_DATE TO_DATE Ratio child parent 14Jan2011 0:00:00 30May2012 0:00:00 66,67 273704 272275 14Jan2011 0:00:00 30May2012 0:00:00 33,33 273704 273825 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294288 31May2012 0:00:00 06Mar2018 0:00:00 32,80 273704 273825 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294292 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294278 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294296 31May2012 0:00:00 06Mar2018 0:00:00 13,44 273704 294295 07Mar2018 0:00:00   12,44 273704 294288 07Mar2018 0:00:00   50,24 273704 312646 07Mar2018 0:00:00   12,44 273704 294292 07Mar2018 0:00:00   12,44 273704 294278 07Mar2018 0:00:00   12,44 273704 294295
I have some code which find parent child relations and output this: hist1 hist2 hist3 hist4 hist5 273704 272275       273704 273825 278642     273704 294288 272275     273704 273825 278642     273704 294292 283153     273704 294278 283167     273704 294296 283206     273704 294295 283191     273704 294288 272275     273704 312646 278642     273704 294292 283153     273704 294278 283167     273704 294295 283191

I want in addition the from_date and to_date and ratio to appear in the same manner .i.e.g resolved by the values on the input line :

Is that possible and how???? I suppose I need more arrays – but how to do that simple in my code?????

I will be happy if you could edit my program as included below thanks in advanve. Look forward to see it.

Eg.

hist1

hist2

hist3

hist4

hist5

hist1_fromdate	hist2_fromdate	hist3_fromdate	hist4_fromdate	hist5_fromdate
hist1_todate	hist2_todate	hist3_todate	hist4_todate	hist5_todate
hist1_ratio	hist2_ratio	hist3_ratio	hist4_ratio	hist5_ratio

 Here is my program:

data lineage(keep=hist1-hist5);

/* Read in SAS data set */

Set my_inputdata;

/* Create an array with enough elements to hold the maximum number of */

/* observations that will 'link' together.                           */

array hist(5);

   count=1;

   /* Put the value of CHILD variable into the array HIST */

   hist(count)=child;

/* Process through the entire data set while the value of COUNT is less   */

/* than 4, i.e. the upper boundary of the array minus 1.                 */

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

   /* Read in SAS data set again. Rename the variables to new names so     */

   /* they can be compared with the variable names coming in with the first */

   /* SET statement. The POINT= option allows you to access each observation */

   /* by observation number.                                                                          */

   set my_inputdata (rename=(child=child1 parent=parent1)) nobs=last point=i;

   /* As you step through each observation, compare the value of PARENT to the */

   /* value of CHILD1 (which is the new name given to the CHILD variable when */

   /* data set is brought in second time).                                     */

   if parent=child1 then do;

     count+1;

     /* populate array with value of CHILD1 */

     hist(count)=child1;

     child=child1;

     parent=parent1;

     i=1;

   end;

end;

/* increment COUNT */

   count+1;

   hist(count)=parent;

run;

---

[ANLYNG]

the numbers are ID's childid and parentid. I get these ids correctly as far I can see ?  but i want more information out (the other coloums in the input dataset)..

[Reeza]

Then can you make provide your data as data step so we can run it?

I don't see any drop/keep statement so I'm confused as to why they aren't kept by default with the code you've shown. 

Instructions on data step code is here:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

[Reeza]

NVM - its at the top of the data step:

(keep=hist1-hist5)

Try changing your KEEP statement to keep the variables of interest.

[ANLYNG]

When I keep all variables it does not output the variables (dates and ratio) for each id in the hierachi.