Append Excel file's sheets with illegal variable names

[Budu Gulo]

I want to append Sheet1-10 of the file my_data.xlsx. The problem is row 1 contains dates. One way to solve this problem is to put a letter in the beginning of each date using Excel; then use the code provided here https://www.statalist.org/forums/for...-file-s-sheets .

I believe Stata can provide more efficient solution. The question is, how?

[Clyde Schechter]

How have you been trying to import it? This data should not present a problem for -import excel- with the -firstrow- option specified--Stata will create a legal variable name that closely resembles those dates.

[Budu Gulo]

My bad! I didn't even try; just assumed it wouldn't be possible. Lesson learned-- always give a shot before posting! Thanks Clyde Schechter.

Now there is a new problem. Dates have become variable labels. How do I reshape the data? I want to have a date variable.

. describe

Contains data
  obs:            30                          
 vars:           525                          
 size:       144,060                          
---------------------------------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
---------------------------------------------------------------------------------------------------------------------------------------------
Name            str53   %53s                  Name
Code            str10   %10s                  Code
C               double  %10.0g                10/19/2016
D               double  %10.0g                10/20/2016
E               double  %10.0g                10/21/2016
F               double  %10.0g                10/24/2016
G               double  %10.0g                10/25/2016
H               double  %10.0g                10/26/2016
I               double  %10.0g                10/27/2016
J               double  %10.0g                10/28/2016
K               double  %10.0g                10/31/2016
L               double  %10.0g                 11/1/2016
M               double  %10.0g                 11/2/2016
N               double  %10.0g                 11/3/2016
O               double  %10.0g                 11/4/2016
P               double  %10.0g                 11/7/2016
Q               double  %10.0g                 11/8/2016
--more--

Example data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str53 Name str10 Code double(C D E F G H I J K L M N O P Q R S T U V W X Y Z)
"REN REDES ENERGETICAS NACIONAIS"                  "50776X(P)"   2.5071  2.5147  2.5081  2.5119  2.4657 2.4911  2.4798   2.493    2.51  2.5034  2.4543  2.4572 2.4515  2.4892  2.5081  2.4779   2.427  2.3817  2.3761   2.378  2.3761  2.3704  2.3836  2.3883
"REN REDES ENERGETICAS NACIONAIS - TOT RETURN IND" "50776X(RI)"  168.53  169.04   168.6  168.85  165.75 167.46   166.7  167.58  168.72  168.28  164.99  165.18  164.8  167.33   168.6  166.57  163.15   160.1  159.72  159.85  159.72  159.34  160.23  160.55
"REN REDES ENERGETICAS NACIONAIS - PRICE INDEX"    "50776X(PI)"    96.7      97    96.7    96.9    95.1   96.1    95.6    96.1    96.8    96.5    94.7    94.8   94.5      96    96.7    95.6    93.6    91.9    91.6    91.7    91.6    91.4    91.9    92.1
"SEMAPA"                                           "152499(P)"    11.91    11.8   11.78  11.795   11.77 11.665  11.675  11.735   11.72   11.52   11.42  11.415 11.355   11.49   11.58   11.56  11.655   11.79  11.755  11.645  11.655  11.675    11.9  11.855
"SEMAPA - TOT RETURN IND"                          "152499(RI)" 2044.05 2025.17 2021.74 2024.31 2020.02   2002 2003.72 2014.01 2011.44 1977.12 1959.95 1959.09 1948.8 1971.97 1987.41 1983.98 2000.28 2023.45 2017.45 1998.57 2000.28 2003.72 2042.33 2034.61
"SEMAPA - PRICE INDEX"                             "152499(PI)"   932.7   924.1   922.5   923.7   921.7  913.5   914.3     919   917.8   902.2   894.3   893.9  889.2   899.8   906.9   905.3   912.7   923.3   920.6     912   912.7   914.3   931.9   928.4
"SONAE COM LIMITED DATA"                           "289230(P)"    2.502     2.5   2.469   2.417     2.5   2.54   2.536   2.517    2.55   2.475   2.525    2.56   2.56     2.6     2.6     2.6   2.587    2.62   2.522   2.561   2.579    2.58    2.58    2.58
"SONAE COM LIMITED DATA - TOT RETURN IND"          "289230(RI)"   30.84   30.81   30.43   29.79   30.81   31.3   31.26   31.02   31.43    30.5   31.12   31.55  31.55   32.04   32.04   32.04   31.88   32.29   31.08   31.56   31.79    31.8    31.8    31.8
"SONAE COM LIMITED DATA - PRICE INDEX"             "289230(PI)"      25      25    24.7    24.2      25   25.4    25.4    25.2    25.5    24.8    25.2    25.6   25.6      26      26      26    25.9    26.2    25.2    25.6    25.8    25.8    25.8    25.8
"SONAE SGPS"                                       "741812(P)"     .711    .713    .715    .724     .73   .721    .723    .729    .725    .714    .699    .706   .698    .712    .718    .719    .751    .746    .769    .769    .768    .764    .761    .757
"SONAE SGPS - TOT RETURN IND"                      "741812(RI)"  644.92  646.73  648.55  656.71  662.15 653.99   655.8  661.25  657.62  647.64  634.03  640.38 633.13  645.83  651.27  652.18   681.2  676.67  697.53  697.53  696.62  692.99  690.27  686.64
"SONAE SGPS - PRICE INDEX"                         "741812(PI)"   282.4   283.2     284   287.6     290  286.4   287.2   289.6     288   283.6   277.7   280.5  277.3   282.8   285.2   285.6   298.3   296.3   305.5   305.5   305.1   303.5   302.3   300.7
end

[Clyde Schechter]

Your -dataex- did not actually include the variable labels, which are key to this particular problem. In the future, in that situation, remember to specify the -varlabel- option when running -dataex-. Anyway, here is a miniature version of your data set and code to do what you ask:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str53 Name str10 Code double(C D E F G)
"REN REDES ENERGETICAS NACIONAIS"                  "50776X(P)"   2.5071  2.5147  2.5081  2.5119  2.4657
"REN REDES ENERGETICAS NACIONAIS - TOT RETURN IND" "50776X(RI)"  168.53  169.04   168.6  168.85  165.75
"REN REDES ENERGETICAS NACIONAIS - PRICE INDEX"    "50776X(PI)"    96.7      97    96.7    96.9    95.1
"SEMAPA"                                           "152499(P)"    11.91    11.8   11.78  11.795   11.77
"SEMAPA - TOT RETURN IND"                          "152499(RI)" 2044.05 2025.17 2021.74 2024.31 2020.02
"SEMAPA - PRICE INDEX"                             "152499(PI)"   932.7   924.1   922.5   923.7   921.7
"SONAE COM LIMITED DATA"                           "289230(P)"    2.502     2.5   2.469   2.417     2.5
"SONAE COM LIMITED DATA - TOT RETURN IND"          "289230(RI)"   30.84   30.81   30.43   29.79   30.81
"SONAE COM LIMITED DATA - PRICE INDEX"             "289230(PI)"      25      25    24.7    24.2      25
"SONAE SGPS"                                       "741812(P)"     .711    .713    .715    .724     .73
"SONAE SGPS - TOT RETURN IND"                      "741812(RI)"  644.92  646.73  648.55  656.71  662.15
"SONAE SGPS - PRICE INDEX"                         "741812(PI)"   282.4   283.2     284   287.6     290
end
label var C "10/19/2016" 
label var D "10/20/2016" 
label var E "10/21/2016" 
label var F "10/24/2016" 
label var G "10/25/2016" 


foreach v of varlist C-G {
    local sif_date = daily(`"`:var label `v''"', "MDY")
    rename `v' x`sif_date'
}
reshape long x, i(Name Code) j(date)
format date %td

just assumed it wouldn't be possible

Not everything in Stata is easy, but almost nothing is impossible.

[Budu Gulo]

Thanks Clyde Schechter. Stata rocks, so do you!