Reshape dataset with format "variable:value"

[Yusuv Oemer]

Hello everyone,

I have a dataset in the format "variable : value" (see also the following example).

	v1	v2	v3	v4	...
1	observation1	variable1:value11	variable3:value13	variable4:value14	...
2	observation2	variable2:value22	variable6:value26	.	...
3	observation3	variable1:value31	variable2:value32	variable5:value35	...
...	...	...	...	...

whenever a variable is missing for one observation, the variable is not shown in the row belonging to the observation.


Now I prefer a dataset with the follwing structure:

	identifier	variable1	variable2	variable3	variable4	variable5	variable6
1	observation1	value11	.	value13	value 14	.	.
2	observation2	.	value22	.	.	.	value26
3	observation3	value31	value32	.	.	value35	.

How can I reshape the dataset using Stata?

I would appreciate your help!
Yusuv

[Hemanshu Kumar]

Yusuv Oemer welcome to Statalist! Please go through the FAQ to understand how to post questions more effectively on this forum. In particular, you should create an extract of your data using the command -dataex-, and post that here so that others can work with it and suggest answers suitable for your situation.

[Hemanshu Kumar]

That said, here is the basic idea for your code:

clear
input byte v1 str17(v2 v3 v4)
1    "variable1:value11" "variable3:value13" "variable4:value14"
2    "variable2:value22" "variable6:value26" ""
3    "variable1:value31" "variable2:value32" "variable5:value35"
end

rename v1 identifier

reshape long v, i(identifier) j(num)
drop if missing(v)
split v, parse(:)
drop v num

reshape wide v2, i(identifier) j(v1) string
rename v2* *

li , noobs

which produces:

  +----------------------------------------------------------------------------+
  | identi~r   variab~1   variab~2   variab~3   variab~4   variab~5   variab~6 |
  |----------------------------------------------------------------------------|
  |        1    value11               value13    value14                       |
  |        2               value22                                     value26 |
  |        3    value31    value32                          value35            |
  +----------------------------------------------------------------------------+