"type mismatch" in collapse

[William Mitchell]

Dear all,

I'm getting the error message

type mismatch (r109)

using the collapse function

collapse proc_date, by(id_no)

- and I'm not sure how/why it's happening.

Both variables are string (so should be compatible?) and even when I use the substring command to make them string variables with the same no. of characters, I get the same error message.

Any help would be appreciated, Thanks.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str43 id_no str19 proc_date str12 gender str22 ethnicity
"000049ec671d4da491f12738eb517ff6Right"       "2013-05-12 00:00:00" "Female" "Not Hispanic or Latino"
"000049ec671d4da491f12738eb517ff6Right"       "2013-05-12 00:00:00" "Female" "Not Hispanic or Latino"
"00005f63385542f78f87378a11a10870Left"        "2008-06-20 00:00:00" "Male"   "Unknown"               
"00005f63385542f78f87378a11a10870Left"        "2008-06-20 00:00:00" "Male"   "Unknown"               
"00005f63385542f78f87378a11a10870Right"       "2014-03-31 00:00:00" "Male"   "Unknown"               
"00005f63385542f78f87378a11a10870Right"       "2014-03-31 00:00:00" "Male"   "Unknown"               
"00007888af6d4b7b975439621db012a4Bilateral"   "2015-01-28 00:00:00" "Male"   "Not Hispanic or Latino"
"00007888af6d4b7b975439621db012a4Bilateral"   "2015-01-28 00:00:00" "Male"   "Not Hispanic or Latino"
"0000a51342d244a2b84fe59bd5168ab1Unspecified" "2017-04-23 00:00:00" "Female" "Not Hispanic or Latino"
"0000aaa4fad54b06a8ef489cd60522c1Left"        "2012-01-20 00:00:00" "Female" "Unknown"               
"0000b0f2465f43168be73a076a8a6d8fRight"       "2016-01-04 00:00:00" "Male"   "Unknown"               
"0000b8b8d04046408e96445ac5694055Left"        "2017-12-02 00:00:00" "Female" "Not Hispanic or Latino"
"0000b8b8d04046408e96445ac5694055Left"        "2017-12-02 00:00:00" "Female" "Not Hispanic or Latino"
"0000b8b8d04046408e96445ac5694055Right"       "2017-12-09 00:00:00" "Female" "Not Hispanic or Latino"
"0000b8b8d04046408e96445ac5694055Right"       "2017-12-09 00:00:00" "Female" "Not Hispanic or Latino"
"0000ca7c12494668b13bb0a591b1744bLeft"        "2017-04-09 00:00:00" "Female" "Not Hispanic or Latino"
"0000ca7c12494668b13bb0a591b1744bLeft"        "2017-04-09 00:00:00" "Female" "Not Hispanic or Latino"
"0000ca7c12494668b13bb0a591b1744bRight"       "2017-03-26 00:00:00" "Female" "Not Hispanic or Latino"
"0000ca7c12494668b13bb0a591b1744bRight"       "2017-03-26 00:00:00" "Female" "Not Hispanic or Latino"
"0001173042a24cb28834472bd087e9ccLeft"        "2018-01-21 00:00:00" "Female" "Unknown"               
"0001173042a24cb28834472bd087e9ccLeft"        "2018-01-21 00:00:00" "Female" "Unknown"               
"0001173042a24cb28834472bd087e9ccRight"       "2018-01-07 00:00:00" "Female" "Unknown"               
"0001173042a24cb28834472bd087e9ccRight"       "2018-01-07 00:00:00" "Female" "Unknown"               
"00011cd755264e7bb2ddd39de53446f1Left"        "2015-08-18 00:00:00" "Female" "Unknown"               
"00011cd755264e7bb2ddd39de53446f1Left"        "2015-08-18 00:00:00" "Female" "Unknown"               
"00015005aa9f4bc49c8bb482b57dbd9aUnspecified" "2014-08-10 00:00:00" "Female" "Not Hispanic or Latino"
"00015005aa9f4bc49c8bb482b57dbd9aUnspecified" "2014-05-04 00:00:00" "Female" "Not Hispanic or Latino"
"00015005aa9f4bc49c8bb482b57dbd9aUnspecified" "2014-05-04 00:00:00" "Female" "Not Hispanic or Latino"
"00015005aa9f4bc49c8bb482b57dbd9aUnspecified" "2014-08-10 00:00:00" "Female" "Not Hispanic or Latino"
"000161ef797e4809a3e61f98b6d80749Left"        "2018-04-13 00:00:00" "Male"   "Unknown"               
"000161ef797e4809a3e61f98b6d80749Left"        "2018-04-13 00:00:00" "Male"   "Unknown"               
"000161ef797e4809a3e61f98b6d80749Right"       "2018-03-09 00:00:00" "Male"   "Unknown"               
"000161ef797e4809a3e61f98b6d80749Right"       "2018-03-09 00:00:00" "Male"   "Unknown"               
"00016b03a339429fa622dfdb33d3a276Left"        "2016-11-26 00:00:00" "Female" "Not Hispanic or Latino"
"00016b03a339429fa622dfdb33d3a276Left"        "2016-11-26 00:00:00" "Female" "Not Hispanic or Latino"
"00016b03a339429fa622dfdb33d3a276Right"       "2016-12-23 00:00:00" "Female" "Not Hispanic or Latino"
"00016b03a339429fa622dfdb33d3a276Right"       "2016-12-23 00:00:00" "Female" "Not Hispanic or Latino"
"0001df54663c4ed093efc3c5e2feddebRight"       "2016-11-06 00:00:00" "Male"   "Not Hispanic or Latino"
"0001df54663c4ed093efc3c5e2feddebUnspecified" "2016-11-06 00:00:00" "Male"   "Not Hispanic or Latino"
"0001df54663c4ed093efc3c5e2feddebUnspecified" "2016-11-06 00:00:00" "Male"   "Not Hispanic or Latino"
"0001e755c5494aef8ca4cce722b4c605Unspecified" "2018-03-04 00:00:00" "Female" "Unknown"               
"000200f4bc614b18be0bb587673373d4Right"       "2015-11-04 00:00:00" "Male"   "Hispanic or Latino"    
"000219008c0a4648a9ab8720d0687409Unspecified" "2015-06-25 00:00:00" "Female" "Not Hispanic or Latino"
"0002584e0f8841fa89b1104e788db1aeLeft"        "2014-04-01 00:00:00" "Male"   "Not Hispanic or Latino"
"0002584e0f8841fa89b1104e788db1aeLeft"        "2014-04-01 00:00:00" "Male"   "Not Hispanic or Latino"
"0002584e0f8841fa89b1104e788db1aeRight"       "2014-04-29 00:00:00" "Male"   "Not Hispanic or Latino"
"0002584e0f8841fa89b1104e788db1aeRight"       "2014-04-29 00:00:00" "Male"   "Not Hispanic or Latino"
"0002b5f2000b438dbb37e4b7726352dcUnspecified" "2013-12-27 00:00:00" "Female" "Unknown"               
"0002c05f7a1d404dadb97665231c9bcbUnspecified" "2017-09-05 00:00:00" "Female" "Not Hispanic or Latino"
"0002c05f7a1d404dadb97665231c9bcbUnspecified" "2017-09-05 00:00:00" "Female" "Not Hispanic or Latino"
"0002f9a2e3ad4ee38c8d805a27e7cf34Left"        "2018-10-06 00:00:00" "Female" "Not Hispanic or Latino"
"0002f9a2e3ad4ee38c8d805a27e7cf34Right"       "2018-12-01 00:00:00" "Female" "Not Hispanic or Latino"
"00033b742ef8486eaf7007b505c8eceaLeft"        "2018-05-28 00:00:00" "Female" "Not Hispanic or Latino"
"00033b742ef8486eaf7007b505c8eceaUnspecified" "2018-05-28 00:00:00" "Female" "Not Hispanic or Latino"
"00035621f3184a61bc90d6c199a1e5b2Left"        "2016-10-07 00:00:00" "Female" "Not Hispanic or Latino"
"00035621f3184a61bc90d6c199a1e5b2Left"        "2016-10-07 00:00:00" "Female" "Not Hispanic or Latino"
"00035621f3184a61bc90d6c199a1e5b2Left"        "2016-10-07 00:00:00" "Female" "Not Hispanic or Latino"
"00035621f3184a61bc90d6c199a1e5b2Right"       "2016-09-30 00:00:00" "Female" "Not Hispanic or Latino"
"00035621f3184a61bc90d6c199a1e5b2Right"       "2016-09-30 00:00:00" "Female" "Not Hispanic or Latino"
"00035621f3184a61bc90d6c199a1e5b2Right"       "2016-09-30 00:00:00" "Female" "Not Hispanic or Latino"
"00035621f3184a61bc90d6c199a1e5b2Right"       "2016-09-30 00:00:00" "Female" "Not Hispanic or Latino"
"000368da38bd4705b919dd62e234aa53Right"       "2014-01-17 00:00:00" "Female" "Unknown"               
"000368da38bd4705b919dd62e234aa53Right"       "2014-01-17 00:00:00" "Female" "Unknown"               
"0003714e7da3401da19133b6312e1d70Left"        "2018-09-27 00:00:00" "Female" "Not Hispanic or Latino"
"0003714e7da3401da19133b6312e1d70Left"        "2018-09-27 00:00:00" "Female" "Not Hispanic or Latino"
"0003714e7da3401da19133b6312e1d70Right"       "2018-09-13 00:00:00" "Female" "Not Hispanic or Latino"
"0003714e7da3401da19133b6312e1d70Right"       "2018-09-13 00:00:00" "Female" "Not Hispanic or Latino"
"000373e24d824a4096cce8d849a56e83Bilateral"   "2016-11-01 00:00:00" "Male"   "Unknown"               
"000373e24d824a4096cce8d849a56e83Unspecified" "2016-11-01 00:00:00" "Male"   "Unknown"               
"00038bbf9583401c9917c2029ea594b3Left"        "2018-02-17 00:00:00" "Female" "Not Hispanic or Latino"
"00038bbf9583401c9917c2029ea594b3Right"       "2018-02-02 00:00:00" "Female" "Not Hispanic or Latino"
"00038bbf9583401c9917c2029ea594b3Unspecified" "2018-02-17 00:00:00" "Female" "Not Hispanic or Latino"
"00038bbf9583401c9917c2029ea594b3Unspecified" "2018-02-02 00:00:00" "Female" "Not Hispanic or Latino"
"00039fc00f714c6a9117d8874a594966Right"       "2018-02-18 00:00:00" "Female" "Not Hispanic or Latino"
"00039fc00f714c6a9117d8874a594966Unspecified" "2018-02-18 00:00:00" "Female" "Not Hispanic or Latino"
"0003b7986fd148929a859e7f3b32fd5aLeft"        "2018-01-26 00:00:00" "Female" "Unknown"               
"0003b7986fd148929a859e7f3b32fd5aLeft"        "2018-01-26 00:00:00" "Female" "Unknown"               
"0003b7986fd148929a859e7f3b32fd5aRight"       "2018-02-11 00:00:00" "Female" "Unknown"               
"0003b7986fd148929a859e7f3b32fd5aRight"       "2018-02-11 00:00:00" "Female" "Unknown"               
"0003c10fa97a47f2b4a39c2dfa58321eRight"       "2014-11-09 00:00:00" "Female" "Not Hispanic or Latino"
"0003c10fa97a47f2b4a39c2dfa58321eRight"       "2014-11-09 00:00:00" "Female" "Not Hispanic or Latino"
"0003c6470e664ec08e3527e4387ac23bUnspecified" "2017-05-03 00:00:00" "Female" "Unknown"               
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-06-20 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-06-20 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-06-20 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-06-20 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-06-20 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-06-20 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-05-23 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-06-20 00:00:00" "Female" "Not Hispanic or Latino"
"0003fbf4c7794c18adfd9dd96e2b98f0Unspecified" "2016-06-20 00:00:00" "Female" "Not Hispanic or Latino"
end

[Wouter Wakker]

The default statistic calculated by collapse is (mean), so you're asking for the mean of a string variable and Stata cannot make sense of that.

Maybe this is rather what you're looking for.

collapse (firstnm) proc_date, by(id_no)

[Nick Cox]

collapse by default produces means, and Stata won't try to take means of string variables. Identifiers being string is fine.

Your data example implies that your times are notional. That being so, you can get a daily date out of something like

gen daily_date = daily(word(proc_date, 1), "YMD")
format daily_date %td

and then you can push that through collapse to get mean dates for each identifier. If that's not what you want, you need something else. (Mean dates are not crazy, but I wonder if you want something quite different, such as getting rid of duplicates.)

collapse is a command; substr() is a function: this distinction is more than just small pedantry over terminology, as in Stata commands and functions are quite different beasts with different behaviour and documentation

[William Mitchell]

Thanks Nick and Wouter,

I'm actually only trying to collapse long data so that I can get n= values for a "figure 1" flowchart, for a paper.

So I'm really only concerned with collapsing the "duplicate" id_no rows/observations. The proc_date variable was just a "matching" string variable I thought sensible (and necessary?) to use, to collapse the id_no variables, and find out what my real n= value was.

Sorry I didn't state that in my original post. Thanks for clarifying. Will.