Generating a new variable for a specific dataset

[George Mane]

Dear STATA Forum,

I have the following data and I want to generate a new variable "SDG_L1_C_Merged" that equals the max value in the row of other variables "SDG_L1_C*", or if missing, to show 0.

I hope you can help,

thank you,

G

* Example generated by -dataex-. For more info, type help dataex
clear
input str3(reporter partner) str6 HS6Code int Year float(SDG_L1_CA110    SDG_L1_CA120    SDG_L1_CA130    SDG_L1_CA140    SDG_L1_CA150    SDG_L1_CA190    SDG_L1_CA210    SDG_L1_CA220    SDG_L1_CA310    SDG_L1_CA330    SDG_L1_CA400    SDG_L1_CA420    SDG_L1_CA490    SDG_L1_CA410    SDG_L1_CA320)
"EUN" "WLD" "150910" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "150990" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "151000" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "151110" 2013 . . 12 . 12 12 12 12 12 12 . 12 . 12 12
"EUN" "WLD" "151190" 2013 . . 12 . 12 12 12 12 12 12 . 12 . 12 12
"EUN" "WLD" "151211" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "151219" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "151221" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "151229" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "151311" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "151319" 2013 . .  . .  .  .  .  .  .  . .  . .  .  .
"EUN" "WLD" "151321" 2013 . . 12 . 12 12 12 12 12 12 . 12 . 12 12
"EUN" "WLD" "151329" 2013 . . 12 . 12 12 12 12 12 12 . 12 . 12 12
end

[Nick Cox]

Perhaps

egen wanted = rowmax(SDG_L1_CA*)
replace wanted = 0 if wanted == .

[George Mane]

Dear Nick,

That works perfectly.

Also following a previous post of yours (https://www.statalist.org/forums/for...value-in-a-row)

it worked as well:

gen wanted = 0
unab xvars : SDG_L1_C*

quietly foreach x of local xvars {
replace wanted = `x' if `x' != .
}

Thank you for your help!

[Nick Cox]

The two solutions are not the same. The code you cite gives the value of the last non-missing variable (or extended missing value if met later) in an observation. That is the same as the maximum if and only if the non-missing values are constant -- with possible complications if you have extended missing values.

It can be reduced to

gen wanted = 0

quietly foreach x of var SDG_L1_C* {
replace wanted = `x' if `x' != .
}