Creating weighted percentiles for several years

[Marc Peters]

Hello!

I have time-series data on the inflation rate and the expenditure weight of several price components for several years. The data is in long-format (- see the extract below).

I want to create weighted percentiles of the inflation rates of all the components for each year. I think it would help me most if the 10th, the 25th, (and so on) percentile are each stored in a new variable.

I have trouble figuring out what to do exactly. Creating the percentiles with the egen command allows me to create percentiles by date, but doesn't allow me to use weights. Using the _pctile command allows me to use weights and specify the percentiles I want, but I have issues with creating a loop to compute it for each date and then storing the results in new variables.

Could somebody help? That would be greatly appreciated!!

Best regards,
Marc

* Example generated by -dataex-. For more info, type help dataex
clear
input float date str5 CP double(infl weight)
455 "_CP08" -1.4 21.6
455 "_CP01" .9 185.03
455 "_CP05" .9 83.52
455 "_CP03" 1 88.83
455 "_CP09" 1.3 102.4
455 "_CP07" 1.5 156.14
455 "_CP12" 1.5 63.92
455 "_CP11" 2.1 85.64
455 "_CP10" 2.2 3.75
455 "_CP04" 2.8 158.44
455 "_CP02" 3.5 43.66
455 "_CP06" 4.6 7.08
456 "_CP08" -1.2 21.5
456 "_CP01" .9 184.24
456 "_CP05" .9 82.85
456 "_CP03" 1 87.76
456 "_CP09" 1.2 102.17
456 "_CP12" 1.4 63.69
456 "_CP07" 1.4 156.87
456 "_CP11" 2.1 85.74
456 "_CP10" 2.2 3.79
456 "_CP04" 2.6 160.45
456 "_CP02" 3.4 43.74
456 "_CP06" 4.6 7.18
457 "_CP08" -1 21.5
457 "_CP05" .9 82.85
457 "_CP01" .9 184.24
457 "_CP03" .9 87.76
457 "_CP09" 1.2 102.17
457 "_CP07" 1.2 156.87
457 "_CP12" 1.4 63.69
457 "_CP11" 2.1 85.74
457 "_CP10" 2.3 3.79
457 "_CP04" 2.4 160.45
457 "_CP02" 3.3 43.74
457 "_CP06" 4.5 7.18
458 "_CP08" -1 21.5
458 "_CP05" .8 82.85
458 "_CP03" .9 87.76
458 "_CP01" 1.1 184.24
458 "_CP07" 1.1 156.87
458 "_CP09" 1.1 102.17
458 "_CP12" 1.3 63.69
458 "_CP11" 2 85.74
458 "_CP04" 2.3 160.45
458 "_CP10" 2.4 3.79
458 "_CP02" 3.2 43.74
458 "_CP06" 4.5 7.18
459 "_CP08" -.9 21.5
459 "_CP05" .9 82.85
459 "_CP03" .9 87.76
459 "_CP07" 1 156.87
459 "_CP09" 1.1 102.17
459 "_CP01" 1.3 184.24
459 "_CP12" 1.3 63.69
459 "_CP11" 2.1 85.74
459 "_CP04" 2.2 160.45
459 "_CP10" 2.4 3.79
459 "_CP02" 3 43.74
459 "_CP06" 4.4 7.18
460 "_CP08" -.8 21.5
460 "_CP03" .9 87.76
460 "_CP05" .9 82.85
460 "_CP07" 1 156.87
460 "_CP09" 1 102.17
460 "_CP12" 1.2 63.69
460 "_CP01" 1.4 184.24
460 "_CP11" 2.1 85.74
460 "_CP04" 2.1 160.45
460 "_CP10" 2.5 3.79
460 "_CP02" 3 43.74
460 "_CP06" 4.4 7.18
461 "_CP08" -.7 21.5
461 "_CP07" .9 156.87
461 "_CP05" .9 82.85
461 "_CP03" .9 87.76
461 "_CP09" 1 102.17
461 "_CP12" 1.2 63.69
461 "_CP01" 1.6 184.24
461 "_CP04" 1.9 160.45
461 "_CP11" 2.1 85.74
461 "_CP10" 2.5 3.79
461 "_CP02" 2.9 43.74
461 "_CP06" 4.3 7.18
462 "_CP08" -.8 21.5
462 "_CP05" .9 82.85
462 "_CP07" .9 156.87
462 "_CP03" 1 87.76
462 "_CP09" 1 102.17
462 "_CP12" 1.1 63.69
462 "_CP01" 1.7 184.24
462 "_CP04" 1.8 160.45
462 "_CP11" 2.1 85.74
462 "_CP10" 2.5 3.79
462 "_CP02" 2.8 43.74
462 "_CP06" 4.4 7.18
463 "_CP08" -.8 21.5
463 "_CP07" .7 156.87
463 "_CP05" .9 82.85
463 "_CP09" .9 102.17
end
format %tm date
[/CODE]

[Clyde Schechter]

levelsof date, local(dates)

foreach n of numlist 10 25 50 75 90 {
    gen p`n' = .
}

foreach d of local dates {
    pctile temp = infl [aweight = weight] if date == `d', nq(4)
    replace p25 = temp[1] if date == `d'
    replace p50 = temp[2] if date == `d'
    replace p75 = temp[3] if date == `d'
    drop temp
    pctile temp = infl [aweight = weight] if date == `d', nq(10)
    replace p10 = temp[1] if date == `d'
    replace p90 = temp[9] if date == `d'
    drop temp
}

Edit: You didn't say what kind of weight you are using. In this code, I have assumed they are aweights. Evidently change the code accordingly if that assumption is wrong.

[Marc Peters]

Thanks so much!