Reshape: (note: it20180624 not found)

[Patrick Gisler]

Hello all, I am trying to reshape my data from wide format to long format the variable I am trying to reshape is a daily date however its not continuous. The data is imported from an excel file where I rewrote the date with the following format it(date) so 22/06/2018 would be it20180622.

However, it seems like when I am trying to reshape the data, stata is looking vor values that dont even exit and display them as "(note: it20180624 not found)" see below for more information.

 reshape long it, i(c_id v_id) j(year)
(note: j = 20180622 20180624 20180626 20180628 20180702 20180704 20180706 20180708 20180710 20180712 20180716 2
> 0180718 20180720 20180724 20180726 20180728 20180730 20180732 20180800 20180802 20180804 20180806 20180808 20
> 180810 20180812 20180814 20180816 20180820 20180822 20180824 20180828 20180830 20180832 20180904 20180906 201
> 80908 20180910 20180912 20180914 20180916 20180918 20180920 20180924 20180926 20180928 20181000 20181002 2018
> 1004 20181008 20181010 20181012 20181016 20181018 20181020 20181022 20181024 20181026 20181028 20181030 20181
> 032 20181100 20181102 20181104 20181106 20181108 20181112 20181114 20181116 20181120 20181122 20181124 201811
> 26 20181128 20181130 20181204 20181206 20181208 20181210 20181212 20181214 20181216 20181218 20181220 2018122
> 4 20181226 20181228 20181232 20190100 20190102 20190104 20190108 20190110 20190112 20190114 20190116 20190118
>  20190120 20190122 20190124 20190128 20190130 20190132 20190200 20190204 20190206 20190208 20190212 20190214
> 20190216 20190218 20190220 20190222 20190224 20190226 20190228 20190300 20190304 20190306 20190308 20190312 2
> 0190314 20190316 20190320 20190322 20190324 20190326 20190328 20190400 20190402 20190404 20190408 20190410 20
> 190412 20190416 20190418 20190420 20190422 20190424 20190426 20190428 20190430 20190500 20190502 20190504 201
> 90506 20190508 20190510 20190512 20190514 20190516 20190520 20190522 20190524 20190528 20190530 20190532 2019
> 0604 20190606 20190608 20190610 20190612 20190614 20190616 20190618 20190620 20190624 20190626 20190628 20190
> 700 20190702 20190704 20190708 20190710 20190712 20190716 20190718 20190720 20190722 20190724 20190726 201907
> 28 20190730 20190732 20190800 20190802 20190804 20190806 20190808 20190812 20190814 20190816 20190820 2019082
> 2 20190824 20190826 20190828 20190830 20190902 20190904 20190906 20190908 20190910 20190912 20190916 20190918
>  20190920 20190924 20190926 20190928 20190930 20191000 20191002 20191004 20191008 20191010 20191012 20191014
> 20191016 20191018 20191020 20191022 20191024 20191028 20191030 20191032 20191100 20191104 20191106 20191108 2
> 0191112 20191114 20191116 20191118 20191120 20191122 20191124 20191126 20191128 20191202 20191204 20191206 20
> 191208 20191210 20191212 20191216 20191218 20191220 20191224 20191226 20191228)
(note: it20180624 not found)
(note: it20180708 not found)
(note: it20180728 not found)
(note: it20180732 not found)
(note: it20180800 not found)
(note: it20180804 not found)
(note: it20180812 not found)
(note: it20180832 not found)
(note: it20180908 not found)
op. sys. refuses to provide memory
r(909);

describe

Contains data
  obs:        13,830                        
 vars:           401                        
 size:    44,394,300                        
---------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
---------------------------------------------------------------------------------------------------------------
c_id            int     %10.0g                c_id
companyticker   str16   %16s                  company ticker
v_id            byte    %10.0g                v_id
variable        str21   %21s                  variable
grp_id          int     %10.0g                grp_id
it20180622      double  %10.0g                it20180622
it20180625      double  %10.0g                it20180625
it20180626      double  %10.0g                it20180626
it20180627      double  %10.0g                it20180627
it20180628      double  %10.0g                it20180628
it20180629      double  %10.0g                it20180629
it20180702      double  %10.0g                it20180702
it20180703      double  %10.0g                it20180703
it20180704      double  %10.0g                it20180704
it20180705      double  %10.0g                it20180705
it20180706      double  %10.0g                it20180706
it20180709      double  %10.0g                it20180709
it20180710      double  %10.0g                it20180710
it20180711      double  %10.0g                it20180711
it20180712      double  %10.0g                it20180712
it20180713      double  %10.0g                it20180713
it20180716      double  %10.0g                it20180716
it20180717      double  %10.0g                it20180717
it20180718      double  %10.0g                it20180718
it20180719      double  %10.0g                it20180719
it20180720      double  %10.0g                it20180720
it20180723      double  %10.0g                it20180723
it20180724      double  %10.0g                it20180724

Would appreciate the help thanks.

[William Lisowski]

Welcome to Statalist.

The problem is that when Stata converts your 8-digit suffixes to the numeric variable year, it is creating a float or some other type of value that cannot hold an 8-digit number without loss of precision. Consider the following example of this sort of conversion.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 date_s
"20180622"
"20180625"
"20180626"
"20180627"
"20180628"
end
generate date_n = real(date_s)
format %9.0f date_n
describe
list, clean

. describe

Contains data
  obs:             5                          
 vars:             2                          
------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
------------------------------------------------------------------------------------------------
date_s          str8    %9s                  
date_n          float   %9.0f                
------------------------------------------------------------------------------------------------
Sorted by:
     Note: Dataset has changed since last saved.

. list, clean

         date_s     date_n  
  1.   20180622   20180622  
  2.   20180625   20180624  
  3.   20180626   20180626  
  4.   20180627   20180628  
  5.   20180628   20180628

So you need to add the string option to your reshape wide command so that year is created as a string variable.

Then, after the reshape you DO NOT want to convert that string directly to a number using destring. The result will be useless for Stata calculations, because the difference between 20180629 and 20180702 will be 73 days, not 3 days.

Instead you want to convert it to a Stata Internal Format daily date value.

generate day = daily(year,"YMD")
format day %td

Stata's "date and time" variables are complicated and there is a lot to learn. If you have not already read the very detailed Chapter 24 (Working with dates and times) of the Stata User's Guide PDF, do so now. If you have, it's time for a refresher. After that, the help datetime documentation will usually be enough to point the way. You can't remember everything; even the most experienced users end up referring to the help datetime documentation or back to the manual for details. But at least you will get a good understanding of the basics and the underlying principles. An investment of time that will be amply repaid.

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

[Patrick Gisler]

Hey William,

Thank you very much. That helped indeed, I will also go through the manual again.

hope you have a great rest of the week.

Patrick