uncollapse dataset

[tim bradshaw]

Hi there

I have the following data manipulation problem and would be grateful for any advice.

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(year sex) float agegrp double(cases population)
2018 1 1  3 25
2018 2 1  9 25
2019 1 2  7 25
2019 2 2 11 25
end

,
My goal is to "uncollapse" the data so that I have 100 rows (one for each person in the "population"), 25 of which have values 2018, 1, and 1 for year, sex and agegrp, respectively. Of those, 3 are flagged as cases (binary variable).

The next 25 have values 2018, 2, and 1 for year, sex and agegrp, respecitively. Of those, 9 are flagged as cases (binary variable).

And so on.

I hope that makes sense and I've described the problem clearly enough.

Thanks

[Clyde Schechter]

gen long obs_no = _n
expand population
by obs_no, sort: gen byte is_a_case = _n <= cases
drop obs_no

[tim bradshaw]

Magic. Thanks so much, Clyde.