@Ksharp beautiful! That makes more sense than substring. I can't thank you enough. I have spent more time than I'd like to admit trying to figure this out. ... View more

@Ksharp  I'm hoping you can help me once again. I've been testing the code on various reports to ensure the results are what is expected and have hit a scenario which requires an additional condition. I have one report that should not have any parents because there are no aggregates in it and another report that has only one or two parents (aggregates). The condition would be that it needs to move up until length is less than current length and also that the parent is a substring of the current class. Here is what I'm looking for with the new group 31 added. data want; input group $ class $ class_length parent $; datalines; 68 1 1 . 68 11 2 1 68 1101 4 11 68 1102 4 11 68 1103 4 11 68 12 2 1 68 1201 4 12 68 1202 4 12 27 1 1 . 27 1101 4 1 27 2 1 . 27 2201 4 2 27 2202 4 2 31 1 1 . 31 2101 4 . 31 2102 4 . 31 220201 6 . 31 220202 6 . 31 22020201 8 220202 ; So with the original logic for group 31, class 220201 would end up with the parent 2102 but because 2102 is not a substring of 220201 then the parent should be blank. No matter how far up you move, 220201 has no parent. I hope this makes sense.    Please let me know if you know of a way to add this condition to the original code you supplied. Thanks in advance! ... View more

This is obviously mock data and the original data can have some 60K lines but for the most part, yes the data really looks like this. I'm working with a bunch of reports where each row represents a data point in a report with higher level classes (parent) being aggregates of the children below. Some reports take all of the class levels and other reports focus on varying levels of aggregates. The mock data I supplied here showcased the different scenarios I encountered. Thanks again. 🙂 ... View more

@Ksharp!!!! You did it! Wow! It works exactly as I had hoped. I am so impressed. This isn't the first time you saved the day: You solved a really complicated problem for me many years ago. Thank you again. You are a rock star! ... View more

@Kurt_Bremser the max class_length possible is 15. ... View more

Say you take class value 1103 from group 68 and you have to determine which class is the parent of it. 1) Look at the length of 1103 which is 4. 2) Move up the list until you reach a length that is less than 4 (so skip 1102 and 1101). 3) The first instance of a length less than 4 above 1103 is 11 with a length of 2. 4) Identify the parent of 1103 which is 11 because it is the first instance above 1103 with a length less than 4. I hope this example helps. ... View more

@mkeintz thanks for your code. I liked the approach. Unfortunatly I cannot make it work for my needs. I think this is a case where automation is not possible. ... View more

@FreelanceReinhard - you are right. This is not a logical rule. I need to go back to my client and convince them to either create a new order with the new data included else accept a numerical order. Thanks so much for your time. ... View more

@PGStats. This is certainly the closest a solution has come. If the 1-3-1 and 1-3-3 fell in position 7 and 8 under new_sort_order it would be perfect, but I'm realizing this custom sort is not going to be possible. Thank you for your suggestion and time. ... View more

@FreelanceReinhard Right, the rule then would be any new record should be inserted in numerical order so in your example, 3-5-11 would be inserted after 3-5-7. ... View more

@mkeintz This looks like an interesting approach and I am eager to try it, but I get an error (DATA STEP Component Object failure. Aborted during the COMPILATION phase.) From everything I read on google it would seem to point to a syntax error but unfortunately I have no experience with hash so am having trouble pin-pointing it. Any suggestions? ... View more

@ballardw In all cases where the key_var is new, it should be inserted in numeric order so 1-2-3 should come before 1-2-4. For existing key_var, they should be in the order specified by the sort_order variable. So the idea is to take the original report and manually insert the new points by examining the values and inserting them in numeric order. I have no control over the report design other than being the person who has to update it. Perhaps this is just something that has to be done on a case by case basis manually. Thanks for your assistance. ... View more

I think another approach may be to ignore the whole sort issue and take a step back before I even join the tables together. Table 1 is the original table: data have_1; input SORT_ORDER KEY_VAR $; CARDS; 1 1-2-7 2 1-2-4 3 1-2-5 4 1-2-8 5 1-2-9 ; run; Table 2 is a new KEY_VAR that needs to be added into the original table: data have_2; input KEY_VAR $; CARDS; 1-2-3 ; run; How can I insert the new KEY_VAR (1-2-3) from table have_2 before KEY_VAR 1-2-4 without moving any other observations from Table have_1. data want_1; input SORT_ORDER KEY_VAR $; CARDS; 1 1-2-7 . 1-2-3 2 1-2-4 3 1-2-5 4 1-2-8 5 1-2-9 ; run; So I insert the new var (1-2-3) based on the logic that 1-2-3 comes before 1-2-4. But I don't want to move the first obs (1-2-7) from its current position so I end up with: data want_final; input SORT_ORDER KEY_VAR $; CARDS; 1 1-2-7 2 1-2-3 3 1-2-4 4 1-2-5 5 1-2-8 6 1-2-9 ; run; I hope that makes more sense.       ... View more

Sorry, correction in last paragraph: Now the sort order should be ORANGES, BANANAS, APRICOTS, APPLES. ... View more