help with rowlast or rowfirst

[Daniel Sutcliffe]

Hello

In my data, I have a number of columns beginning with gp_code_* ( aroound 100, but only a few of the columns are included in the sample data below)

Only one of these columns is populated per row, so I have been trying to use rowfirst () to create a new variable , which will include the fist non missing value.

The syntax is egen gp_coalesce = rowfirst( gp_code* ). However, I get the following error code: type mismatch r(109);

I think it might be because the missing values are spaces perhaps - but I have tried to replace with . but that hasn't worked.

when I type egen nmiss = rowmiss( gp_code_*) it seems to recognise when a variable is missing.

does anyone have any advice as to how I can amend my data so I can use rowfirst()?



dataex CCGcode GPcode gp_code_1805 gp_code_1806 gp_code_1807 gp_code_1808 gp_code_1809 gp_code_1810 gp_code_1811 gp_code_1812 gp_code_1901 gp_code_1902 gp_code_1903 if pick

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 CCGcode str14 GPcode str6(gp_code_1805 gp_code_1806 gp_code_1807 gp_code_1808 gp_code_1809 gp_code_1810 gp_code_1811 gp_code_1812 gp_code_1901 gp_code_1902 gp_code_1903)
"01K     " "A82007" "" "" "" "" "" "" ""       ""       "" "" ""
"01H     " "A82019" "" "" "" "" "" "" ""       ""       "" "" ""
"01H     " "A82021" "" "" "" "" "" "" ""       ""       "" "" ""
"00D     " "A83015" "" "" "" "" "" "" ""       ""       "" "" ""
"00D     " "A83054" "" "" "" "" "" "" ""       ""       "" "" ""
"03K     " "B81045" "" "" "" "" "" "" ""       ""       "" "" ""
"03F     " "B81104" "" "" "" "" "" "" ""       ""       "" "" ""
"03H     " "B81620" "" "" "" "" "" "" ""       ""       "" "" ""
"03D     " "B82035" "" "" "" "" "" "" ""       ""       "" "" ""
"02R     " "B83015" "" "" "" "" "" "" ""       ""       "" "" ""
"02R     " "B83038" "" "" "" "" "" "" ""       ""       "" "" ""
"02R     " "B83641" "" "" "" "" "" "" ""       ""       "" "" ""
"15F     " "B86009" "" "" "" "" "" "" ""       ""       "" "" ""
"15F     " "B86056" "" "" "" "" "" "" ""       ""       "" "" ""
"15F     " "B86068" "" "" "" "" "" "" ""       ""       "" "" ""
"03R     " "B87002" "" "" "" "" "" "" ""       ""       "" "" ""
"15M     " "C81006" "" "" "" "" "" "" ""       ""       "" "" ""
"04V     " "C82061" "" "" "" "" "" "" ""       ""       "" "" ""
"04V     " "C82600" "" "" "" "" "" "" ""       ""       "" "" ""
"04V     " "C82656" "" "" "" "" "" "" ""       ""       "" "" ""
"04D     " "C83037" "" "" "" "" "" "" ""       ""       "" "" ""
"02Q     " "C84008" "" "" "" "" "" "" ""       ""       "" "" ""
"02Q     " "C84101" "" "" "" "" "" "" ""       ""       "" "" ""
"04M     " "C84120" "" "" "" "" "" "" ""       ""       "" "" ""
"03N     " "C88078" "" "" "" "" "" "" ""       ""       "" "" ""
"06H     " "D81084" "" "" "" "" "" "" ""       ""       "" "" ""
"06F     " "E81057" "" "" "" "" "" "" ""       ""       "" "" ""
"06F     " "E81077" "" "" "" "" "" "" ""       ""       "" "" ""
"06P     " "E81617" "" "" "" "" "" "" ""       ""       "" "" ""
"06K     " "E82021" "" "" "" "" "" "" ""       ""       "" "" ""
"06N     " "E82603" "" "" "" "" "" "" ""       ""       "" "" ""
"07P     " "E84699" "" "" "" "" "" "" ""       ""       "" "" ""
"08G     " "E86028" "" "" "" "" "" "" ""       ""       "" "" ""
"08Y     " "E87755" "" "" "" "" "" "" ""       ""       "" "" ""
"06T     " "F81094" "" "" "" "" "" "" ""       "F81115" "" "" ""
"99E     " "F81168" "" "" "" "" "" "" ""       ""       "" "" ""
"07G     " "F81197" "" "" "" "" "" "" ""       ""       "" "" ""
"06T     " "F81213" "" "" "" "" "" "" ""       ""       "" "" ""
"08F     " "F82624" "" "" "" "" "" "" ""       ""       "" "" ""
"08H     " "F83686" "" "" "" "" "" "" ""       ""       "" "" ""
"08M     " "F84009" "" "" "" "" "" "" ""       ""       "" "" ""
"09P     " "G81013" "" "" "" "" "" "" ""       ""       "" "" ""
"09D     " "G81054" "" "" "" "" "" "" ""       ""       "" "" ""
"09D     " "G81642" "" "" "" "" "" "" ""       ""       "" "" ""
"09W     " "G82133" "" "" "" "" "" "" ""       ""       "" "" ""
"10A     " "G82147" "" "" "" "" "" "" ""       ""       "" "" ""
"10D     " "G82175" "" "" "" "" "" "" ""       ""       "" "" ""
"99J     " "G82733" "" "" "" "" "" "" ""       ""       "" "" ""
"07Q     " "G84007" "" "" "" "" "" "" ""       ""       "" "" ""
"08L     " "G85035" "" "" "" "" "" "" ""       ""       "" "" ""
"08Q     " "G85052" "" "" "" "" "" "" ""       ""       "" "" ""
"08L     " "G85055" "" "" "" "" "" "" ""       ""       "" "" ""
"08L     " "G85727" "" "" "" "" "" "" ""       ""       "" "" ""
"09N     " "H81006" "" "" "" "" "" "" ""       ""       "" "" ""
"99H     " "H81611" "" "" "" "" "" "" "H81028" ""       "" "" ""
"09Y     " "H81658" "" "" "" "" "" "" ""       ""       "" "" ""
"09X     " "H82003" "" "" "" "" "" "" ""       ""       "" "" ""
"09X     " "H82010" "" "" "" "" "" "" ""       ""       "" "" ""
"09H     " "H82050" "" "" "" "" "" "" ""       ""       "" "" ""
"09G     " "H82076" "" "" "" "" "" "" ""       ""       "" "" ""
"11J     " "J81009" "" "" "" "" "" "" ""       ""       "" "" ""
"11J     " "J81064" "" "" "" "" "" "" ""       ""       "" "" ""
"11A     " "J82017" "" "" "" "" "" "" ""       ""       "" "" ""
"10K     " "J82023" "" "" "" "" "" "" ""       ""       "" "" ""
"99M     " "J82206" "" "" "" "" "" "" ""       ""       "" "" ""
"10K     " "J82216" "" "" "" "" "" "" ""       ""       "" "" ""
"10J     " "J82639" "" "" "" "" "" "" ""       ""       "" "" ""
"12D     " "J83009" "" "" "" "" "" "" ""       ""       "" "" ""
"99N     " "J83010" "" "" "" "" "" "" ""       ""       "" "" ""
"15A     " "K81047" "" "" "" "" "" "" ""       ""       "" "" ""
"15A     " "K81605" "" "" "" "" "" "" ""       ""       "" "" ""
"04G     " "K83039" "" "" "" "" "" "" ""       ""       "" "" ""
"12D     " "K84012" "" "" "" "" "" "" ""       ""       "" "" ""
"10Q     " "K84020" "" "" "" "" "" "" ""       ""       "" "" ""
"10Q     " "K84062" "" "" "" "" "" "" ""       ""       "" "" ""
"15C     " "L81034" "" "" "" "" "" "" ""       ""       "" "" ""
"11N     " "L82003" "" "" "" "" "" "" ""       ""       "" "" ""
"11N     " "L82042" "" "" "" "" "" "" ""       ""       "" "" ""
"99P     " "L83030" "" "" "" "" "" "" ""       ""       "" "" ""
"99Q     " "L83666" "" "" "" "" "" "" ""       ""       "" "" ""
"05T     " "M81017" "" "" "" "" "" "" ""       ""       "" "" ""
"05T     " "M81047" "" "" "" "" "" "" ""       ""       "" "" ""
"05T     " "M81058" "" "" "" "" "" "" ""       ""       "" "" ""
"06D     " "M81073" "" "" "" "" "" "" ""       ""       "" "" ""
"15E     " "M85671" "" "" "" "" "" "" ""       ""       "" "" ""
"05C     " "M87014" "" "" "" "" "" "" ""       ""       "" "" ""
"99A     " "N82009" "" "" "" "" "" "" ""       ""       "" "" ""
"12F     " "N85044" "" "" "" "" "" "" ""       ""       "" "" ""
"12F     " "N85616" "" "" "" "" "" "" ""       ""       "" "" ""
"14L     " "P84020" "" "" "" "" "" "" ""       ""       "" "" ""
"01G     " "P87019" "" "" "" "" "" "" ""       ""       "" "" ""
"01W     " "P88006" "" "" "" "" "" "" ""       ""       "" "" ""
"02A     " "P91631" "" "" "" "" "" "" ""       ""       "" "" ""
"07X     " "Y00057" "" "" "" "" "" "" ""       ""       "" "" ""
"09H     " "Y00351" "" "" "" "" "" "" ""       ""       "" "" ""
"00K     " "Y00527" "" "" "" "" "" "" ""       ""       "" "" ""
"05L     " "Y01057" "" "" "" "" "" "" ""       ""       "" "" ""
"15E     " "Y02571" "" "" "" "" "" "" ""       ""       "" "" ""
"00Q     " "Y02657" "" "" "" "" "" "" ""       ""       "" "" ""
"06A     " "Y02757" "" "" "" "" "" "" ""       ""       "" "" ""
end

------------------ copy up to and including the previous line ------------------

[Nick Cox]

Missing strings means empty strings; if you had spaces and no other characters that would not be missing to Stata.

The problem is Stata's side and seems to have been lurking (or least undocumented) since 2004! As you want a string variable as result, you need to specify a string variable type.

This works for me with your data example:

egen str gp_coalesce = rowfirst( gp_code*)

[Ken Chui]

Perhaps use a loop?

gen wanted = ""
foreach x of varlist gp_code_1805 - gp_code_1903{
    replace wanted = `x' if `x' != ""
}

Edit: Oh... didn't know the "str" trick in #2! Thanks, Nick!

[Joro Kolev]

I do know of this feature from previous threads, e.g., https://www.statalist.org/forums/for...or-string-data

but to be honest I do not understand neither where is the problem, nor the comments by Nick Cox in #2 above.

Typically we do not have to specify the variable type -- we might want to (for higher precision say), but if we do not Stata assumes a data type (which might turn out to be insufficient for out purposes).

This is the only example I am familiar with where something does not work if you do not specify the data type, but works if you do.

[Nick Cox]

The code in question is visible on (e.g.)

viewsource _growfirst.ado

and in any case

set trace on

shows where the problem arises.

The rowfirst() code defaults to trying to create a float if the user does not specify a type. For string variable input that results in a type mismatch error.

Being able to specify a variable type is usually a matter of spelling out that you want a double or long, but that is not the issue here.

Smarter code would detect string variable input and default otherwise.

Even smarter code would check for a mix of numeric and string variables.

I've already sent in a report to StataCorp.