Calculate weights in proc sql

[SeanZ]

I have a dataset with variables date, firm id, dummy and asset. What I want to achieve is for each date, each firm id, calculate a weight for each observation according to asset when dummy=0, and do the same for dummy=1. A simple example is as below

date     firm id     dummy     asset     weight

1          A               0          50          50/(50+50)=0.5

1          A               0          50          50/(50+50)=0.5

1          A               1          20          20/(20+80)=0.2

1          A               1          80          80/(20+80)=0.8

I want to get the weight shown above. How can I use proc sql to get the results? Or other ways?

Thanks.

[Jagadishkatam · Accepted solution]

Hi,

Please try the below code

data have;

    input date firm_id$ dummy asset;

cards;

1          A               0          50         

1          A               0          50         

1          A               1          20         

1          A               1          80         

;

proc sql;

    create table want as select date,firm_id,dummy,asset,asset/sum(asset) as weight from have

    group by date,firm_id,dummy;

quit;

Thanks,

Jagadish

[SeanZ]

Thanks. I am going to try it, but it seems to be correct.