数据整理问题

you only need to change the numbers in local order "" for whatever you want.

local order "4 2 5 7 10 9 6 1 3 13"
local nw : word count `order'
gen _keep = .
forv i=1/`nw' {
        local w : word `i' of `order'
        replace _keep = 1 if num == `w'
}
keep if _keep == 1
drop _keep
tab num, matrow(num)
tab name num [fw=value], matrow(name) matcell(value)
drop _all
svmat name
gen name=string(name1)
drop name1
svmat value
local nrow = rowsof(num)
local nn = _N+1
set obs `nn'
gen _v=1
replace _v=-9 in l
local vs ""
forv i=1/`nrow' {
        local ii = num[`i',1]
        local iii : word `i' of `order'
        rename value`i' _value`ii'
        replace _value`ii' = . if _value`ii' == 0
        local vs "`vs' _value`iii'"
        replace _value`ii' = `ii' in l
}
order name `vs'
replace name="name/num" in l
sort _v, stable
drop _v
export excel format.xlsx, replace missing(".(missing)")

jjjj6666 发表于 2014-3-12 10:56
you only need to change the numbers in local order "" for whatever you want.

local order "4 2 5 7 ...

哭了，我name一共有130000+个，svmat用不了怎么办。。我不是很理解svmat commandcommand，为什么我name增加了10002，10003，25000之后还是只有10000和10001？
value也用不了svmat的，跳出来都是matrix xxx not found。。因为数字带小数。。
只有num可以。
怎么办。。。

are the variables: name num and value integer variables?

jjjj6666 发表于 2014-3-13 06:01
are the variables: name num and value integer variables?

value has decimals

it's ok with variable value being decimal as long as name and num are integers.  Then try the reshape way.

jjjj6666 发表于 2014-3-13 07:36
it's ok with variable value being decimal as long as name and num are integers.  Then try the reshap ...

拜谢！最后请教一个问题。这一段不是很懂，
foreach vm of var num* {
    local jj = substr("`vm'",4,100)
    replace v`ii' = value`jj' if `vm' == `ii'
}
为什么要substr并且从第4个开始？

I modified, this is much simpler, try this one

local order "4 2 5 7 10 9 6 1 3 13"
local no : word count `order'
reshape wide value, i(name) j(num)
local nn = _N+1
set obs `nn'
local vs ""
forv i=1/`no' {
local ii : word `i' of `order'
local vs "`vs' value`ii'"
replace value`ii' = `ii' in l
}
keep name `vs'
gen _v=1
replace _v=-9 in l
sort _v, stable
drop _v
rename name name1
gen name=string(name1)
drop name1
replace name="name/num" in 1
order name `vs'
export excel format3.xlsx, replace missing(".(missing)")