How do I calculate the value-weighted value

[Phil_NZ]

Hi SAS community,

I have the data of annual market capitalization (ann_mar_cap) of some firms in some years. I also sorted the datafile by year already (column Type indicates distinc firms).

Type	year	obs_amihud	amihud         ann_mar_cap
320864	1991	217	        0.0000298897   .
321406	1991	120	        0.0001126239   . 
320864	1992	216	        0.0000513143   .
321406	1992	72	        0.0002507506   .
320864	1993	185	        0.0001213274   .
321406	1993	11	        0.0004665429   .
320864	1994	143	        0.0001321269   99236
321406	1994	7	        0.0006057594   104592
321716	1994	3	        0.0000357925   131664

And I also attached the dataset as below.

My columns of interests are amihud and ann_mar_cap. what I need is to calculate the value-weighted amihud. 

To calculate it, I need to calculate the sum of ann_mar_cap for all firm( column Type) each year of this country, after that, I divide the ann_market_cap of each firm for the ann_mar_cap of this country at year t-1. After that, I need to multiple the amihud with this quotient. The promising result is as below.

Type	year	obs_amihud	amihud         ann_mar_cap       total_cap          value_w
320864	1991	217	        0.0000298897   .                 .                  .
321406	1991	120	        0.0001126239   .                 .                  .
320864	1992	216	        0.0000513143   .                 .                  . 
321406	1992	72	        0.0002507506   .                 .                  .
320864	1993	185	        0.0001213274   .                 .                  .
321406	1993	11	        0.0004665429   .                 .                  .
320864	1994	143	        0.0001321269   99236             335492             .
321406	1994	7	        0.0006057594   104592            335492             . 
321716	1994	3	        0.0000357925   131664            335492             .
131712	1995	0	        .              .                 1461874            .
320356	1995	45	        0.0000552666   736183            1461874            0.000121
320599	1995	0	        .              211901            1461874            .
320678	1995	0	        .              329055            1461874            .
320864	1995	21	        0.0001192145   95202             1461874            0.000034
321406	1995	0	        .              89533             1461874            .
321716	1995	4	        0.0001505341   94929             1461874            0.000042
321718	1995	0	        .              .                 1461874            .
321721	1995	0	        .              .                 1461874            .

Can you please suggest me the code of doing so?

Many thanks.

[PaigeMiller]

The first value in the value_w column is 0.000121. What values in the table are multiplied or divided to produce that number? Show us the exact calculation that results in 0.000121.

[Phil_NZ]

Hi @PaigeMiller , this is calculated by multiplying 736183/335492 * 0.000055266

Many thanks.

[smantha · Accepted solution]

data have;
input Type year obs_amihud amihud ann_mar_cap;
datalines;
320864	1991	217	        0.0000298897    
321406	1991	120	        0.0001126239     
320864	1992	216	        0.0000513143    
321406	1992	72	        0.0002507506    
320864	1993	185	        0.0001213274    
321406	1993	11	        0.0004665429    
320864	1994	143	        0.0001321269   99236
321406	1994	7	        0.0006057594   104592
321716	1994	3	        0.0000357925   131664
;

proc means data=have noprint;
class year;
var ann_mar_cap;
output out=annual_mar_cap  sum=/autoname;
run;

proc sql;
create table inter as
select a.*,b.* from
have a
left join
annual_mar_cap b
on a.year = b.year
order by type,year;
quit;

data want;
set inter;
by type year;
lag_country_ann_mar=lag(ann_mar_cap_sum);
if first.type then lag_country_ann_mar=.;
value_w = amihud*ann_mar_cap/lag_country_ann_mar;
run;