Dear All,

I would like some help editing a dataset I have, which I have included an example of below:

v103 v106 v109 v112 v115 v118
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17
14 41 22 20 23 17

This is considered one group of data points.


What I would like the table to look like is this:

v103 v106 v109 v112 v115 v118
14 0 0 0 0 0
14 0 0 0 0 0
14 0 0 0 0 0
0 41 0 0 0 0
0 41 0 0 0 0
0 41 0 0 0 0
0 0 22 0 0 0
0 0 22 0 0 0
0 0 22 0 0 0
0 0 0 20 0 0
0 0 0 20 0 0
0 0 0 20 0 0
0 0 0 0 23 0
0 0 0 0 23 0
0 0 0 0 23 0
0 0 0 0 0 17
0 0 0 0 0 17
0 0 0 0 0 17

The issue is this is a very big dataset with 13,176 rows and 36 columns in total, and one column could have several groups of the first table shown above. I have looked at resources online, which only explain how to delete all the variables using drop. I did find an old Statalist post with a similar question, but the post asked about dropping missing values while I'm trying to drop a certain number of values.

Does anyone have any suggestions or advice on how to approach this problem? Thank you very much in advance!

Lastly, I'm using Stata 15.1.



Best,
Helen