rearranging my dataset

[Zayna Ejaz]

Hi
I am fairly new to Stata, currently completing my dissertation on Stata 17.
I am struggling to arrange my dataset when I import my data from excel to stata (I have 132 observations).
Stuck on the date variable.

I used these codes:

. tostring date, replace
date was int now str5

. gen day=substr(date,1,2)

. gen month=substr(date, 4, 2)

. gen year=substr(date, 7, 4)
(132 missing values generated)

. destring day, replace
day: all characters numeric; replaced as byte

. destring month, replace
month: all characters numeric; replaced as byte

. destring year, replace
year: all characters numeric; replaced as byte
(132 missing values generated)

. gen time=mdy(month, day, year)
(132 missing values generated)

. gen newdate=qofd(time)
(132 missing values generated)

. format newdate %tq

. tsset newdate, q

Time variable: newdate, . to .
Delta: 1 quarter

The problem is that when I generate year variable, I have 132 missing values generated, so when I destring year, generate time... The missing values generated are not letting me continue.
I have been at this for many days, this is my last hope at the moment.
Thank you in advance!

[Daniel Schaefer]

It seems pretty clear that your problem starts with:

gen year=substr(date, 7, 4)

This line doesn't find a valid substring for all 132 observations. Should that be a 3 rather than a 4 for the last argument? I think you have a more fundamental issue though, which is that you probably don't need to manipulate dates by converting them into strings.

If you want advice on how to best work with dates, would you please generate a data example with the -dataex- command, then paste it into a subsequent post? Also, please wrap any code, output, or data in CODE tags (see the # symbol in the editor).

Edit: I don't think that should be a 3 for the last argument, but it's difficult to tell exactly what is happening without a data example.

[Zayna Ejaz]

Thank you for your response.

This is my data:

input str5 date double(gdp cpi ir sm) str2(day month) str1 year
"10958" 1.101873e+12  60.94285 13.51   3639.6 "10" "58" ""
"11048"  1.09731e+12 61.522254 13.83     3544 "11" "48" ""
"11139" 1.089546e+12 62.127996 12.65   3159.4 "11" "39" ""
"11231" 1.079836e+12 63.049776 11.72   3256.8 "11" "31" ""
"11323" 1.064426e+12    64.867  9.92   3495.7 "11" "23" ""
"11413" 1.069389e+12 65.341059   8.9   3465.9 "11" "13" ""
"11504" 1.070821e+12 65.736107  8.59  3387.88 "11" "04" ""
"11596" 1.072797e+12 65.630761  7.67  3512.36 "11" "96" ""
"11688" 1.073579e+12 65.894127  7.49  3412.14 "11" "88" ""
"11779"  1.07489e+12 66.236503  5.85   3387.7 "11" "79" ""
"11870" 1.080662e+12 66.526205  5.69  3297.88 "11" "70" ""
"11962"  1.08681e+12 66.815907  7.36  3350.44 "11" "62" ""
"12054" 1.093765e+12 67.289966  5.36  3602.44 "12" "54" ""
"12144" 1.103723e+12 67.421649  4.79  3966.37 "12" "44" ""
"12235" 1.114242e+12 67.685014   4.9  3990.61 "12" "35" ""
"12327" 1.119052e+12  68.02739  4.11  4321.43 "12" "27" ""
"12419" 1.135542e+12 67.658678  5.64  4329.62 "12" "19" ""
"12509" 1.152136e+12 67.421649  5.69  4025.25 "12" "09" ""
"12600" 1.166865e+12 67.790361     5  4354.18 "12" "00" ""
"12692" 1.175379e+12 68.001053  5.69  4213.61 "12" "92" ""
"12784" 1.186304e+12 68.685805  8.06  4313.61 "12" "84" ""
"12874" 1.186783e+12 69.238873  7.19  4527.15 "12" "74" ""
"12965" 1.188347e+12 69.370556  6.19  4529.75 "12" "65" ""
"13057" 1.193217e+12 69.396892  5.78   4689.1 "13" "57" ""
"13149" 1.194038e+12 69.686595     5  4970.83 "13" "49" ""
"13240" 1.202352e+12 70.239663  4.75  5044.07 "13" "40" ""
"13331"  1.21274e+12 70.345009     4  5291.07 "13" "31" ""
"13423" 1.222507e+12 70.766395     3  5927.03 "13" "23" ""
"13515" 1.238634e+12 71.161443     3  5850.22 "13" "15" ""
"13605" 1.252626e+12 71.372136  3.25  6437.74 "13" "05" ""
"13696"  1.26745e+12 71.556492  3.25  7040.23 "13" "96" ""
"13788" 1.279723e+12 71.503819  4.25  6699.44 "13" "88" ""
"13880" 1.297798e+12 71.898868  4.75   7558.5 "13" "80" ""
"13970" 1.298491e+12 72.083224  4.75  7366.89 "13" "70" ""
"14061"  1.31031e+12 72.162233   5.5   5614.5 "14" "61" ""
"14153" 1.328139e+12 72.293916     5  6485.94 "14" "53" ""
"14245" 1.352229e+12 72.451936  4.75  6597.79 "14" "45" ""
"14335" 1.363541e+12 73.242033   4.5  7010.07 "14" "35" ""
"14426" 1.384876e+12 73.742428   4.5  6957.72 "14" "26" ""
"14518" 1.404357e+12 74.005794  4.75  8413.75 "14" "18" ""
"14610" 1.426823e+12 74.374506  5.25  9462.39 "14" "10" ""
"14701" 1.443782e+12 75.032921  5.75  10195.5 "14" "01" ""
"14792" 1.458465e+12 75.744008  5.75 10377.92 "14" "92" ""
"14884" 1.460962e+12  76.27074  5.75  8876.09 "14" "84" ""
"14976"   1.4687e+12  76.42876     5     7608 "14" "76" ""
"15066" 1.472658e+12 77.719252   4.5   7736.4 "15" "66" ""
"15157" 1.471677e+12 77.798262   3.5  6838.56 "15" "57" ""
"15249" 1.480637e+12 77.087174  2.25  7688.41 "15" "49" ""
"15341" 1.502613e+12 77.613906     2   7851.5 "15" "41" ""
"15431" 1.511469e+12 78.772715   2.5  7145.61 "15" "31" ""
"15522"  1.52455e+12 79.615486  2.75  6180.42 "15" "22" ""
"15614" 1.532914e+12 80.010535  2.75  6614.54 "15" "14" ""
"15706" 1.541396e+12 81.090334     3  6343.29 "15" "06" ""
"15796" 1.539123e+12 80.958652  3.25  6983.14 "15" "96" ""
"15887" 1.544874e+12 81.274691  2.75  7421.13 "15" "87" ""
"15979" 1.555533e+12 81.406373  2.75  8220.89 "15" "79" ""
"16071" 1.566737e+12 81.827759  2.25  8585.93 "16" "71" ""
"16162" 1.585347e+12 82.749539     2  8545.58 "16" "62" ""
"16253" 1.604061e+12 82.907559  2.25  8668.29 "16" "53" ""
"16345" 1.615585e+12 83.276271   2.5  9246.65 "16" "45" ""
"16437" 1.621176e+12 83.565973   2.5  9612.38 "16" "37" ""
"16527" 1.632938e+12 84.329734   2.5  9902.77 "16" "27" ""
"16618" 1.652719e+12 85.067158  2.75 11011.83 "16" "18" ""
"16710"  1.66906e+12 85.119831  3.25 11296.29 "16" "10" ""
"16802" 1.682596e+12 85.541217  3.75 12110.61 "16" "02" ""
"16892" 1.683465e+12 86.489334  4.25 11612.87 "16" "92" ""
"16983" 1.688166e+12 86.542007  4.25 11761.27 "16" "83" ""
"17075" 1.694895e+12 86.278641  4.25 12909.54 "17" "75" ""
"17167" 1.705736e+12 87.095075  4.25  13165.5 "17" "67" ""
"17257" 1.722263e+12 88.385568  4.25 13906.57 "17" "57" ""
"17348" 1.729494e+12 88.385568   4.5 14098.89 "17" "48" ""
"17440" 1.731459e+12 88.359231  4.25 13833.06 "17" "40" ""
"17532" 1.732786e+12 88.648933   3.5 13350.13 "17" "32" ""
"17623" 1.739053e+12 90.466157     3 14467.03 "17" "23" ""
"17714" 1.753314e+12 91.414274     3  11752.9 "17" "14" ""
"17806" 1.732984e+12 90.044772   1.5   8987.7 "17" "06" ""
"17898" 1.693824e+12  89.75507    .5  8720.39 "17" "98" ""
"17988"  1.67534e+12 90.518831   .25 10374.91 "17" "88" ""
"18079" 1.682878e+12 90.624177   .25 11394.96 "18" "79" ""
"18171" 1.702503e+12  90.75586   .25 11746.11 "18" "71" ""
"18263" 1.723041e+12 91.203582   .25 12037.73 "18" "63" ""
"18353" 1.732057e+12 91.782987    .5 11294.42 "18" "53" ""
"18444" 1.744332e+12 92.283382     1 12368.65 "18" "44" ""
"18536" 1.763825e+12 92.810113     1 13443.22 "18" "36" ""
"18628" 1.777148e+12 93.573874     1  14116.1 "18" "28" ""
"18718"  1.78061e+12 94.864367     1 13300.87 "18" "18" ""
"18809" 1.805176e+12 95.048723     1 11623.84 "18" "09" ""
"18901" 1.819392e+12 95.312088     1  11841.7 "18" "01" ""
"18993" 1.820558e+12  95.75981     1 12392.18 "18" "93" ""
"19084" 1.826496e+12 96.365552     1 11596.56 "19" "84" ""
"19175" 1.828984e+12 96.207532     1 12317.46 "19" "75" ""
"19267" 1.832766e+12 96.207532     1 12433.53 "19" "67" ""
"19359" 1.849206e+12 96.628918     1  12749.9 "19" "59" ""
"19449" 1.859938e+12 97.102976     1 12129.11 "19" "49" ""
"19540" 1.875096e+12 97.313669     1 12787.19 "19" "40" ""
"19632" 1.894795e+12 97.102976     1 13621.55 "19" "32" ""
"19724" 1.897892e+12 97.972083     1 14335.31 "19" "24" ""
"19814" 1.915226e+12 99.262576     1 15146.01 "19" "14" ""
"19905" 1.933594e+12 99.341585     1 14960.51 "19" "05" ""
"19997" 1.946974e+12 98.972873     1 14632.44 "19" "97" ""
end
format %tdnn/dd/CCYY date

I tried 3 for the last argument again when generating year and it still has 132 missing values generated.

[Andrew Musau]

If you imported these data from Excel and Stata did the date conversion, then do not turn the SIF values to strings again.

destring date, replace
format date %td
gen day= day(date)
gen month= month(date)
gen year= year(date)

But make sure that Stata converted the dates correctly. Cross-check with the displayed dates in Excel.

[Zayna Ejaz]

Thank you for this Andrew.

Do I need to create a time variable as well using this command?

gen time=mdy(month, day, year)

[Zayna Ejaz]

I have included the time variable and typed these codes:

gen time=mdy(month, day, year)

gen inflation=100*(p-L4.p)
time variable not set
r(111);

tsset date, q

Time variable: date, 4699q3 to 7689q4, but with gaps
        Delta: 1 quarter

. gen inflation=100*(p-L4.p)
(132 missing values generated)

When I generate inflation first, error inflation variable not set then I set date quarterly again. I generate inflation variable again, I have 132 missing values for this specific variable.
Maybe it could be my inflation code?

[Andrew Musau]

If you want quarterly dates:

destring date, replace
format date %td
gen quarter= qofd(date)
format quarter %tq
tsset quarter

[Zayna Ejaz]

thank you very much, it worked!