I suggest you get industry totals via proc summary, as that's what it is mean for.
Then use the output of proc summary (data set need below) in combination with the original data set to get what you want.
Something like:
proc summary data=have noprint nway;
class date ind;
var ret
weight mktcap;
output out=need sum=_wgted_total sumwgt=_total_wgt;
run;
%let min_ind=10;
%let max_ind=90;
%let begdate=01jan2013;
%let enddate=31dec2019;
data want (drop=_:);
set need (in=inneed)
have (in=inhave);
array wgtsum {&min_ind:&max_ind,%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_;
array sumwgt {&min_ind:&max_ind,%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_;
array freq {&min_ind:&max_ind,%sysevalf("&begdate"d):%sysevalf("&enddate"d)} _temporary_;
if inneed then do;
wgtsum{ind,date}=_wgted_total;
sumwgt{ind,date}=_total_wgt;
freq{ind,date}=_freq_;
end;
if inhave=1;
industry_ret=wgtsum{ind,date}/sumwgt{ind,date};
if freq{ind,date}>1 then
indret_except_this_firm= (wgtsum{ind,date}-msrp*weight)/(sumwgt{ind,date}-weight);
run;
The proc summary code says to classify the statistics by DATE and IND. The variable to be "summarized" is RET, weighted by MKTCAP. You can specify many statistics to be put in the output data set. Here I've requested only the weighted sum of RET, and the sum of the weights. I.e. the numerator and denominator of RET for each DATE*IND combination.
In the middle section set the macro variables to known upper and lower limints of industry code, and date range.
And the DATA WNAT step merely sticks the summary output into 2-way matrices. These are then used when HAVE is re-read.
This program is untested, since you haven't provided a sample data set in sas data step form.
... View more
