SAS: Collapsing and weighted averages calculations

Question

I have a SAS programming problem which I can't solve on my own and I'm thankful for any imput.

I want to collapse data in a dataset by variable and summarize/average two variables based on the weights given by another variable and substract them from each other:

Example data

number   flag     volume   measure1  measure2
1         A         1         2         2        
2         B         2         4         5
3         A         5         8         20
4         B         10        4         1
5         A         9         10        11
6         B         5         2         9
7         A         4         11        23
8         B         3         1         8

Now: I want the volume-weighted average of measure1 and two, then calculate measure1-measure2. All this then grouped by the flags A and B:

Number Flag      Volume       VolWeightMeasure1      VolWeightMeasure2      FinalMeasure
1        A        19        ((1/19)*2)+((5/19)*8)+...     ...            (VolWeightMeasure1-VolWeightMeasure2)
2        B        20        ((2/20)*5)+((10/20)*1)+...    ...            (VolWeightMeasure1-VolWeightMeasure2)

So basically collapsing but with volume weighted measures and then deducting the two. Thank you for any input!

Best

Answer 1

proc sql;
   select flag,sum_volume,sum1/sum_volume as volweightmeasure1,sum2/sum_volume as volweightmeasure2,
          calculated volweightmeasure1-calculated volweightmeasure2 as finalmeasure
   from (select flag,sum(volume) as sum_volume, sum(volume*measure1) as sum1, sum(volume*measure2) as sum2 from  have group by flag);
quit;