Fill in dataset

[Anne Todd]

I have an odd task. My data is below. Basically I want to get everything shifted so that it appears under v2 and v3, so that it looks more like a table. So v2 would have "yellow" for behavior, v3 would have "green" for behavior, and so on. The actual data is far larger so some type of loop would be ideal--I could do it manually with this small example but I have something like 100 total variables and 20 types under v1.

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 v1 str3 v2 str4 v3 str6 v4 str5 v5 str6 v6 str4 v7 str5(v8 v9)
"test_score" "red" "blue" ""       ""      ""       ""     ""      ""     
"behavior"   ""    ""     "yellow" "green" ""       ""     ""      ""     
"school"     ""    ""     ""       ""      "orange" "pink" ""      ""     
"teach"      ""    ""     ""       ""      ""       ""     "white" "black"
end

[Mike Lacy]

For data like your synthetic example, you could do this something like this:

egen strL allvar = concat(v2-v9), punct(" ")
replace allvar = stritrim(allvar)
split allvar, gen(newv) parse(" ")
keep v1 newv*

This kind of approach might not yield what you want for the data you actually have. Also, I don't understand the relevance of "20 types under v1," so I have ignored that part of your question.

[Anne Todd]

Thanks Mike Lacy , this at least gives me something useful to work from. Sorry for the confusing statement about v1. I just meant that there are a lot more observations, as well as a lot more variables.