topic Re: the ancestors for each children - and more data describing them in SAS Programming

the ancestors for each children - and more data describing them

ANLYNG — Mon, 03 Sep 2018 14:57:38 GMT

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;

/* increment COUNT */

count+1;

hist(count)=parent;

run;

Re: the ancestors for each children - and more data describing them

Reeza — Mon, 03 Sep 2018 18:25:30 GMT

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;

Re: the ancestors for each children - and more data describing them

ANLYNG — Mon, 03 Sep 2018 18:30:34 GMT

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)..

Re: the ancestors for each children - and more data describing them

Reeza — Mon, 03 Sep 2018 18:46:30 GMT

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-data-AKA-generate/ta-p/258712

Re: the ancestors for each children - and more data describing them

Reeza — Mon, 03 Sep 2018 18:49:52 GMT

NVM - its at the top of the data step:

(keep=hist1-hist5)

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

Re: the ancestors for each children - and more data describing them

ANLYNG — Tue, 04 Sep 2018 09:55:22 GMT

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