"Expanding" a count variable within a panel dataset

[MJ Bolton]

Hello. My PI has asked me to create new rows from two existing variables (see num and den below). Using the first row (time "1") for id "1" as an example: he would like the counts of the num and den as separate rows.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(id time num den)
1 1 2 5
1 2 4 5
1 3 1 5
1 4 2 5
1 5 0 5
2 1 5 5
2 2 1 5
2 3 5 5
2 4 3 5
2 5 2 5
3 1 0 5
3 2 0 5
3 3 3 5
3 4 1 5
3 5 4 5
4 1 2 5
4 2 3 5
4 3 5 5
4 4 3 5
4 5 2 5
5 1 4 5
5 2 5 5
5 3 2 5
5 4 3 5
5 5 4 5
end

The result for the first row would look something like this:

id time num den
1 1 0 1
1 1 0 1
1 1 0 1
1 1 1 1
1 1 1 1

I don't think this is a dummy variable transformation as additional rows that expand the panel are needed. Thank you in advance for your suggestions.

[Joseph Coveney]

There are probably better ways, but you can try something like the following.

.ÿ
.ÿversionÿ15.1

.ÿ
.ÿclearÿ*

.ÿ
.ÿquietlyÿinputÿbyte(idÿtimeÿnumÿden)

.ÿ
.ÿtempfileÿcrosscheck

.ÿquietlyÿsaveÿ`crosscheck'

.ÿ
.ÿ*
.ÿ*ÿBeginÿhere
.ÿ*
.ÿ
.ÿ//ÿNumerators
.ÿpreserve

.ÿ
.ÿquietlyÿdropÿifÿ!num

.ÿquietlyÿexpandÿnum

.ÿquietlyÿreplaceÿnumÿ=ÿ1

.ÿkeepÿidÿtimÿnum

.ÿ
.ÿbysortÿidÿtim:ÿgenerateÿlongÿdiscardÿ=ÿ_n

.ÿ
.ÿtempfileÿnumerators

.ÿquietlyÿsaveÿ`numerators'

.ÿ
.ÿ//ÿDenominators
.ÿrestore

.ÿ
.ÿquietlyÿdropÿifÿ!den

.ÿquietlyÿexpandÿden

.ÿquietlyÿreplaceÿdenÿ=ÿ1

.ÿkeepÿidÿtimÿden

.ÿ
.ÿbysortÿidÿtim:ÿgenerateÿlongÿdiscardÿ=ÿ_n

.ÿ
.ÿmergeÿ1:1ÿidÿtimeÿdiscardÿusingÿ`numerators',ÿnogenerateÿnoreport

.ÿdropÿdiscard

.ÿmvencodeÿnumÿden,ÿmv(0)
ÿÿÿÿÿÿÿÿÿnum:ÿ59ÿmissingÿvaluesÿrecoded

.ÿ
.ÿorderÿidÿtimÿnumÿden

.ÿsortÿidÿtimÿnumÿden

.ÿlistÿifÿidÿ==ÿ1ÿ&ÿtimÿ==ÿ1,ÿnoobs

ÿÿ+-----------------------+
ÿÿ|ÿidÿÿÿtimeÿÿÿnumÿÿÿdenÿ|
ÿÿ|-----------------------|
ÿÿ|ÿÿ1ÿÿÿÿÿÿ1ÿÿÿÿÿ0ÿÿÿÿÿ1ÿ|
ÿÿ|ÿÿ1ÿÿÿÿÿÿ1ÿÿÿÿÿ0ÿÿÿÿÿ1ÿ|
ÿÿ|ÿÿ1ÿÿÿÿÿÿ1ÿÿÿÿÿ0ÿÿÿÿÿ1ÿ|
ÿÿ|ÿÿ1ÿÿÿÿÿÿ1ÿÿÿÿÿ1ÿÿÿÿÿ1ÿ|
ÿÿ|ÿÿ1ÿÿÿÿÿÿ1ÿÿÿÿÿ1ÿÿÿÿÿ1ÿ|
ÿÿ+-----------------------+

.ÿ
.ÿ//ÿCrossÿcheck
.ÿcollapseÿ(sum)ÿnum_sÿ=ÿnumÿden_sÿ=ÿden,ÿby(idÿtime)

.ÿmergeÿ1:1ÿidÿtimeÿusingÿ`crosscheck',ÿassert(match)ÿnogenerateÿnoreport

.ÿassertÿnum_sÿ==ÿnum

.ÿassertÿden_sÿ==ÿden

.ÿ
.ÿexit

endÿofÿdo-file


.

Strikes me as an unusual request.

[MJ Bolton]

Thank you. This approach works for the request!