Create a variable based on a hierarchy

Hello friends,

I have a dataset where each services offer by providers is in one row. However my goal is that each provider represents one row.

I would like to create a variable that represents the LEAD grantee of my provider. Note that providers can have more than one grantee. For example provider 113 has 2 grantees (112 and 74). The lead provider is chosen based on a hierarchy of the Fund variable. I need to chose the lead grantee in the following order. First PartD grantees, PartC, PartB and PartA. So for provider 113 the lead grantee should be Grantee 112 since it has part C Funding. For provider 100 the lead grantee should be 114 since it has part D funding. There is one more thing. If the provider equals the grantee, that grantee should be the lead grantee for the provider regardless of the Fund variable. For example, the lead grantee for provider 90 should be grantee 90. We call these type of providers, grantee-providers.To summarize the lead grantee should be chosen in the following order: Grantee-provider, Part C, Part D, Part B, Part A.

It is my first time working with a dataset like this. It is very confusing to me.


Services Grantee Fund Provider

1 112 PartC 113
2 74 PartB 113
2 112 PartA 113

1 113 PartC 100
2 74 PartB 100
2 114 PartD 100

3 90 PartD 90
3 90 PartA 90
1 105 PartD 90

Thank you in advance,