Sum child to parent, with multiple levels

Originally Posted by ChemistB

Not sure what you are asking. Do your first and second table correlate? I don't think so.
Is the ID in the 1st column of table 2 equal the Parent ID in Table 1?
So looking at table 1,
Parent ID 3 has 2 children?, 1,8 and 9 have 1? What if the Parent ID is blank?
Sorry, a little slow this morning.

Yes, they correlate :-)

To take it from the top :-)

ID 1 as it has no parent is the top level and is parent for 9 and 2 (9 is directly under 1 and 9 has 2 under it) therefore I want the sum of Col 2 for 1, 9 and 2 - does it make sense

To explain parent ID 3, then yes it has to childs 5 and 7, but ID 3 has 8 as its parent ID so 5 and 7 is under 3 and 3 i under 8 equals 3 childs under 8 (3 directly related and 5 and 7 related to 3) here I need the sum Col 2 from 8, 3, 5 and 7

Does it still make sense :-)

To specify data further then if ParentID is blank then we are at the top and "just" need to sum all related child record, also some ID will not have any child records

Thanks, it works as described, but not the quickest, my actual data is roughly 100.000 rows where 60.000 is children - if anyone knows of a better performance way of doing the same then it will be appreciated and even more if it can handle more than 2 levels :-)

Where's the data come from? I'm guessing it's not from Excel. This would be fairly trivial to do with SQL in the database from which I'm assuming it originated (unless it's access).

Originally Posted by Kyle123

Where's the data come from? I'm guessing it's not from Excel. This would be fairly trivial to do with SQL in the database from which I'm assuming it originated (unless it's access).

It is actually from SAS, so SQL would be useable :-)

Have a look here http://yazispring.wordpress.com/2013...nd-sql-server/

Originally Posted by Kyle123

Have a look here http://yazispring.wordpress.com/2013...nd-sql-server/

OK I tried doing it in SAS, but when I use the macro mentioned I only get two tables like this

LEVEL_1

Please Login or Register  to view this content.

LEVEL_2

Please Login or Register  to view this content.

I'm afraid I can't help. SAS ain't my thing, I can do this in SQL server, but have never really used SAS

found another solution in SAS - not really helpfull in this forum, but thanks for your assistance anyway.

Glad you got it sorted Excel doesn't really do hierarchical data