replace command leads to type mismatch

[Nico Stappen]

Hey there,

I've got a little problem with my do-file

import excel "/Users/nicolasstappen/Desktop/Nico/Studium/Master/5. Semester/Masterarbeit/Daten/Stata analyse /vollständige Daten 29.11.xlsx", sheet("FDI Flows") firstrow case(lower)


codebook

drop indicator // no information and/or variation
drop measure // no information and/or variation

replace location="belgium" if location=="BEL"
replace location="france" if location=="FRA"
replace location="germany" if location=="DEU"
replace location="ireland" if location=="IRL"
replace location="italy" if location=="ITA"
replace location="netherlands" if location=="NLD"
replace location="poland" if location=="POL"
replace location="spain" if location=="ESP"
replace location="sweden" if location=="SWE"
replace location="united kingdom" if location=="GBR"
replace location="european union" if location=="EU"


replace period=201703 if time=="2017-Q3"
replace period=201704 if time=="2017-Q4"
replace period=201801 if time=="2018-Q1"
replace period=201802 if time=="2018-Q2"
replace period=201803 if time=="2018-Q3"
replace period=201804 if time=="2018-Q4"
replace period=201901 if time=="2019-Q1"
replace period=201902 if time=="2019-Q2"
replace period=201903 if time=="2019-Q3"
replace period=201904 if time=="2019-Q4"
replace period=202001 if time=="2020-Q1"
replace period=202002 if time=="2020-Q2"
replace period=202003 if time=="2020-Q3"
replace period=202004 if time=="2020-Q4"
replace period=202101 if time=="2021-Q1"
replace period=202102 if time=="2021-Q2"
replace period=202103 if time=="2021-Q3"
replace period=202104 if time=="2021-Q4"
replace period=202201 if time=="2022-Q1"
replace period=202202 if time=="2022-Q2"


gen q=0

replace q=1 if time=="2018-Q1" | time=="2019-Q1" | time=="2020-Q1" | time=="2021-Q1" | time=="2022-Q1"
replace q=2 if time=="2018-Q2" | time=="2019-Q2" | time=="2020-Q2" | time=="2021-Q2" | time=="2022-Q2"
replace q=3 if time=="2017-Q3" | time=="2018-Q3" | time=="2019-Q3" | time=="2020-Q3" | time=="2021-Q3"
replace q=4 if time=="2017-Q4" | time=="2018-Q4" | time=="2019-Q4" | time=="2020-Q4" | time=="2021-Q4"

my data:

* Example generated by -dataex-. For more info, type help dataex
clear
input str11 location str7 time double(inwardvalues outwardvalues) str6 period
"AUS" "2021-Q1"  2772.8126173489  -6161.472024033 "202101"
"AUS" "2021-Q2"  1281.2617348855 -3038.6781825009 "202102"
"AUS" "2021-Q3"  5936.1622230567  10889.973713857 "202103"
"AUS" "2021-Q4"   17526.09838528  6406.3086744273 "202104"
"AUS" "2022-Q1"      42558.59375   89590.56712963 "202201"
"AUS" "2022-Q2"  3375.7961783439   8353.968367566 "202202"
"CAN" "2017-Q3"  7543.6612576843  13012.157844773 "201703"
"CAN" "2017-Q4"  6007.3869425314  13822.395664972 "201704"
"CAN" "2018-Q1"  11560.019857264  7479.4560969833 "201801"
"CAN" "2018-Q2"  6298.1813863899  14920.564971144 "201802"
"CAN" "2018-Q3"  9387.3686779569   22527.15680125 "201803"
"CAN" "2018-Q4"   10407.95073914  13109.104426742 "201804"
"CAN" "2019-Q1"  9316.5240878674  25213.456032027 "201901"
"CAN" "2019-Q2"  16311.492386209  18070.873187792 "201902"
"CAN" "2019-Q3"   12867.39239578   13083.68135682 "201903"
"CAN" "2019-Q4"  11646.375489751  23008.731118839 "201904"
"CAN" "2020-Q1"  8594.7127199523  7181.7141003579 "202001"
"CAN" "2020-Q2"   7854.563691694  12763.745616463 "202002"
"CAN" "2020-Q3"  7526.5416880406    15087.5873983 "202003"
"CAN" "2020-Q4" -801.81227020579  11492.593138831 "202004"
"CAN" "2021-Q1"  21606.777070443  9228.6431580375 "202101"
"CAN" "2021-Q2"  9600.7696027124  15888.114385082 "202102"
"CAN" "2021-Q3"  16363.286224172  21509.026000798 "202103"
"CAN" "2021-Q4"  12672.404137774  51633.448454009 "202104"
"CAN" "2022-Q1"  15772.528619946  15232.932438724 "202201"
"CAN" "2022-Q2"  17234.575131855  27050.548808916 "202202"
"JPN" "2017-Q3"  3444.6300192453  34193.216473132 "201703"
"JPN" "2017-Q4"   2508.668551395  35605.948190137 "201704"
"JPN" "2018-Q1"  5203.3678993424  37951.914705961 "201801"
"JPN" "2018-Q2"  2843.6623197039  34804.897077079 "201802"
"JPN" "2018-Q3"  1088.7457081362  34341.420759188 "201803"
"JPN" "2018-Q4"  1616.4624045481  38480.842581037 "201804"
"JPN" "2019-Q1"  3061.6280064817  90740.873705302 "201901"
"JPN" "2019-Q2"   2353.837748682  47623.972346085 "201902"
"JPN" "2019-Q3"  2643.8538582652  38524.237650641 "201903"
"JPN" "2019-Q4"  7419.7467009305  48245.878323981 "201904"
"JPN" "2020-Q1"  4419.7323776067  52403.815027144 "202001"
"JPN" "2020-Q2"  3434.9428690906  12259.559909275 "202002"
"JPN" "2020-Q3"  4902.2259604786  31081.074537253 "202003"
"JPN" "2020-Q4"  1223.4876227117  24386.224001268 "202004"
"JPN" "2021-Q1"  15115.335985753  53732.742712214 "202101"
"JPN" "2021-Q2"  1153.6039403618  32680.315052526 "202102"
"JPN" "2021-Q3"  4633.2709202635  36550.841561131 "202103"
"JPN" "2021-Q4"  5411.9911166188  32290.640767857 "202104"
"JPN" "2022-Q1"  6474.4188033887  15314.195844594 "202201"
"JPN" "2022-Q2"  2308.1689101325  26767.978202935 "202202"
"NOR" "2021-Q1" -619.56584996799  4207.1815166153 "202101"
"NOR" "2021-Q2"  6431.5893615783  11084.560321248 "202102"
"NOR" "2021-Q3"  4274.1081301286  58.080661118547 "202103"
"NOR" "2021-Q4"  3482.3953907932  6744.5731245999 "202104"
"NOR" "2022-Q1" -6422.6061996406 -2003.2998451785 "202201"
"NOR" "2022-Q2"   10341.37015401  5235.3690918747 "202202"
"CHE" "2017-Q3" -7321.5775547993 -6810.7043104588 "201703"
"CHE" "2017-Q4"  20579.150094961 -4137.1291453489 "201704"
"CHE" "2018-Q1" -58700.463264238 -7624.2664731052 "201801"
"CHE" "2018-Q2" -8804.0923243489  25719.942758674 "201802"
"CHE" "2018-Q3" -37188.077295439  3514.7359918542 "201803"
"CHE" "2018-Q4"  8980.4511276125  38677.009282561 "201804"
"CHE" "2019-Q1" -13706.221277539 -16986.513697239 "201901"
"CHE" "2019-Q2" -8375.6586488985  -13395.47134723 "201902"
"CHE" "2019-Q3"  23742.439657611  27151.440345094 "201903"
"CHE" "2019-Q4"  -92867.37973361 -49803.649937322 "201904"
"CHE" "2020-Q1" -32213.757095589 -3979.5354171466 "202001"
"CHE" "2020-Q2" -2465.1034562141 -3272.8113365196 "202002"
"CHE" "2020-Q3" -19429.689530039  7079.6614995454 "202003"
"CHE" "2020-Q4"  11922.046721337  -27291.21465799 "202004"
"CHE" "2021-Q1"  14629.936787375  2860.8062611811 "202101"
"CHE" "2021-Q2"  4782.0433414661 -5610.1660483589 "202102"
"CHE" "2021-Q3" -6096.2199905151 -7164.8625444729 "202103"
"CHE" "2021-Q4" -1217.4560207178   4835.183490344 "202104"
"CHE" "2022-Q1"  2326.4542336917 -16180.393057275 "202201"
"CHE" "2022-Q2"  7194.2490771263 -16832.922313146 "202202"
"TUR" "2017-Q3"          2837.66           103.76 "201703"
"TUR" "2017-Q4"          3274.16           819.41 "201704"
"TUR" "2018-Q1"          2228.15          1145.31 "201801"
"TUR" "2018-Q2"          2872.15           936.77 "201802"
"TUR" "2018-Q3"          3122.14           725.81 "201803"
"TUR" "2018-Q4"          4312.76           846.68 "201804"
"TUR" "2019-Q1"          2930.41           505.78 "201901"
"TUR" "2019-Q2"          2170.13           984.56 "201902"
"TUR" "2019-Q3"          1968.12           582.59 "201903"
"TUR" "2019-Q4"          2593.76           897.37 "201904"
"TUR" "2020-Q1"          2487.35           563.56 "202001"
"TUR" "2020-Q2"            642.3           388.92 "202002"
"TUR" "2020-Q3"          2060.74          1089.61 "202003"
"TUR" "2020-Q4"           2543.5          1189.37 "202004"
"TUR" "2021-Q1"          2168.77           788.99 "202101"
"TUR" "2021-Q2"          2185.67           905.68 "202102"
"TUR" "2021-Q3"          4426.15          1149.78 "202103"
"TUR" "2021-Q4"          3585.37          2127.73 "202104"
"TUR" "2022-Q1"          1915.01          1234.87 "202201"
"TUR" "2022-Q2"          3503.98          1238.47 "202202"
"USA" "2017-Q3"            74236           110175 "201703"
"USA" "2017-Q4"            69702            38535 "201704"
"USA" "2018-Q1"            33819           -43887 "201801"
"USA" "2018-Q2"            -5428           -60833 "201802"
"USA" "2018-Q3"           107366            56040 "201803"
"USA" "2018-Q4"            80658           -80340 "201804"
"USA" "2019-Q1"            67787           -27132 "201901"
"USA" "2019-Q2"            67918            50084 "201902"
end

and this error occurs when I try to run it:

. replace period=201703 if time=="2017-Q3"
type mismatch


Hope somebody of the more experienced stata users can help me out.

Thanks in Advance and kind regards

Nico

[Carlo Lazzaro]

Nico:
I cannot replicate the error you experienced:

. set obs 1
Number of observations (_N) was 0, now 1.

. g time="2017-Q3"

. g period=.
(1 missing value generated)

.  replace period=201703 if time=="2017-Q3"
(1 real change made)

. . list

     +------------------+
     |    time   period |
     |------------------|
  1. | 2017-Q3   201703 |
     +------------------+

.

Have you already ruled out the presence of leading/trailing blanks?

[Nico Stappen]

Hey Carlo,

thank you for your help!! That is strange. Im using Stata 17.0 BE on a mac and the error still occurs. Could it be because of using mac?
"Have you already ruled out the presence of leading/trailing blanks?" - What do you mean with that?

I always put the command "clear all" before trying it..

kind regards
Nico

[Carlo Lazzaro]

Nico:
see:

[FN] String functions
(View complete PDF manual entry)


Functions

strtrim(s)
Description: s without leading and trailing blanks (ASCII space character char(32)); equivalent to strltrim(strrtrim(s))

strtrim(" this ") = "this"
Domain s: strings
Range: strings without leading or trailing blanks

stritrim(s)
Description: s with multiple, consecutive internal blanks (ASCII space character char(32)) collapsed to one blank

stritrim("hello there") = "hello there"
Domain s: strings
Range: strings with no multiple, consecutive internal blanks

strltrim(s)
Description: s without leading blanks (ASCII space character char(32))

strltrim(" this") = "this"
Domain s: strings
Range: strings without leading blanks

strrtrim(s)
Description: s without trailing blanks (ASCII space character char(32))

strrtrim("this ") = "this"
Domain s: strings
Range: strings without trailing blanks


Remarks

Use functions ustrtrim(), ustrltrim(), and ustrrtrim() to remove Unicode whitespace and blank characters.

[Hemanshu Kumar]

Your period variable is a string. So you need to enclose the value in quotes:

replace period = "201703" if time=="2017-Q3"

That said, there are much easier ways of achieving your task, instead of so many individual replace commands:

replace period = subinstr(time,"-Q","0",1)

and ultimately, if you want your period variable to be numeric and not string, just do

destring period, replace

[Nick Cox]

There are some absolutes here. One is that this kind of error does not depend on whether the platform is Macintosh, Windows or anything else.

Another is that type mismatch means EITHER you fed a command something numeric (a variable, a value, or an expression) where a string is expected OR you fed something string (same expansion) where a numeric is expected.

A third is simplest of all. In your data example in #1 period is a string variable and you fed it a numeric value. You need double quotes around the string value. .

That was the error.

Carlo Lazzaro could not reproduce it because he first created period as a numeric variable.

Leading or trailing blanks are not an issue where type mismatch is the problem.

But, Nico, a long time ago, as it now seems, in one of your earlier threads it was explained that holding monthly dates as numbers like 201703 is a bad idea in Stata, and holding them as strings is even worse. Numeric dates like 201712 and 201801 illustrate the problem as there is a jump of 89 at the end of a year compared with 11 jumps of 1 from say 201702 to 201703. That is useless for graphs and useless for analysis. A string equivalent can't be used in graphs or statistical calculation and has only one use, for tabulations.

In short, better practice has already been explained, which is to use Stata monthly dates.

[Carlo Lazzaro]

Thanks Nick!
Lesson learnt: Carlo, read all the details before typing an incomplete reply!

[Nick Cox]

My post #6 crossed with Hemanshu Kumar #5, which covers most of the same points.

But note that destring would not really help in this case. A string date of "201712" is not much more use as a number 201712, as explained in my post.

[William Lisowski]

Let me add to the advice from Nick Cox in post #6.

Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already done so, you are well advised to read the detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF. It's a long read and there is a lot there. But it will acquaint you with the philosophy of Stata's datetime variables, the functions available to create and transform them, and how to calculate with them. You won't remember everything, but at the end you will probably be able to remember what functions are called for when the need for one arises, and then you can rely on the help datetime documentation to refresh your memory about the details. The time you spend doing this will be amply and soon repaid.

All Stata manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

In particular, if you use numbers like 201703 to indicate the third quarter of 2017, then Stata will think that 201801 is 97 quarters later than 201704.

[Nico Stappen]

Thanks a lot for all your help Problem solved!