Hi everyone, Below is small part of the dataset that I'm working with. The only difference is that my dataset contains many more company_Nm, branch_id, product_id and date. Company_Nm Branch_ID City Product_ID Date Sale Competitors_sale AAA AAA1 Gainesville XX Jan-11 10000 AAA AAA2 Yreka XX Jan-11 8000 AAA AAA3 Yreka XX Jan-11 5000 AAA AAA4 San Francisco XX Jan-11 4000 AAA AAA5 Los Angeles XX Jan-11 7000 AAA AAA6 Donalsonville XX Jan-11 12000 AAA AAA7 Savannah XX Jan-11 80000 BBB BBB1 Yreka XX Jan-11 1000 BBB BBB2 San Francisco XX Jan-11 1000 BBB BBB3 Los Angeles XX Jan-11 1000 BBB BBB4 Donalsonville XX Jan-11 1000 BBB BBB5 Savannah XX Jan-11 1000 For each specific Company_nm and branch_id,each row, I want to find other branches located in the same city, let's say competitors. Then I need to take the average of sales among the competitors for the same product_id and date in that row, and put the value in the competitor_sale column. Previously, I picked only one branch_id as a pilot and did the same procedure for that. I may be able to extend my code to all rows by do loop, but I'm not sure what's the most efficient way. Below is my code, even though I'm think it's so ugly and inefficient. data AAA1; set have; if branch_id='AAA1'; run; proc sql; create table comp1 as select * from have where city in (Select city from AAA1); quit; proc sql; create comp2 as select * from comp1 where company_nm not in (Select company_nm from AAA1); quit; proc sql ; create table comp_rate as select product_id, date, mean(rate) as Ave_comp format = 5.3 from comp2 group by product_id, date; quit; data want; merge AAA1 comp_rate; by product_id date; run; I really appreciate any and all suggestions.
... View more