Hello Statalisters,

I have a dataset where I am trying to calculate the proportion variable representing the proportion of neighboring states who have adopted a policy during Covid. The variables of interest are mandate_order_dates (matched to daily_cases_date and coded 0/1), sumofborderstates which is a rowtotal variable adding together the number of bordering states. What I'd like to do is code prop_neighbors with an equation that gives a 1 for states where all surrounding states have adopted the same policy, a .75 for states that have at least two bordering states that adopted, and 0 otherwise. Where I'm running into a problem is capturing the daily_cases_date and the dummy (mandate_order_dates) inside the equation. There is also a series of dummy variables that I used to create the sumofborderstates variable but I don't think I need to use those in order to create this proportion variable. A sample of the data is provided below:

Code:
* Example generated by -dataex-. For more info, type help dataex
clear
input float(daily_cases_date mandate_order_dates sumofborderstates) str20 statename float prop_neighbors
22135 0 4 "Alabama" .
22136 0 4 "Alabama" .
22137 0 4 "Alabama" .
22138 0 4 "Alabama" .
22139 0 4 "Alabama" .
22140 0 4 "Alabama" .
22141 0 4 "Alabama" .
22142 0 4 "Alabama" .
22143 0 4 "Alabama" .
22144 0 4 "Alabama" .
22145 0 4 "Alabama" .
22146 0 4 "Alabama" .
22147 0 4 "Alabama" .
22148 0 4 "Alabama" .
22149 0 4 "Alabama" .
22150 0 4 "Alabama" .
22151 0 4 "Alabama" .
22152 0 4 "Alabama" .
22153 0 4 "Alabama" .
22154 0 4 "Alabama" .
22155 0 4 "Alabama" .
22156 0 4 "Alabama" .
22157 0 4 "Alabama" .
22158 0 4 "Alabama" .
22159 0 4 "Alabama" .
22160 0 4 "Alabama" .
22161 0 4 "Alabama" .
22162 0 4 "Alabama" .
22163 0 4 "Alabama" .
22164 0 4 "Alabama" .
22165 0 4 "Alabama" .
22166 0 4 "Alabama" .
22167 0 4 "Alabama" .
22168 0 4 "Alabama" .
22169 0 4 "Alabama" .
22170 0 4 "Alabama" .
22171 0 4 "Alabama" .
22172 0 4 "Alabama" .
22173 0 4 "Alabama" .
22174 0 4 "Alabama" .
22175 0 4 "Alabama" .
22176 0 4 "Alabama" .
22177 0 4 "Alabama" .
22178 0 4 "Alabama" .
22179 0 4 "Alabama" .
22180 0 4 "Alabama" .
22181 0 4 "Alabama" .
22182 0 4 "Alabama" .
22183 0 4 "Alabama" .
22184 0 4 "Alabama" .
22185 0 4 "Alabama" .
end
format %td daily_cases_date
I believe this "might" need to happen in a series of steps or possibly a loop but I've never built one. Any assistance is greatly appreciated.