reshape command in stata help

[Olivia Emma]

Hi

I have this data in Stata

* Example generated by -dataex-. For more info, type help dataex
clear
input strL PARAMETER int YEAR double(JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC) float id
"QV2M" 1981 16.78 17.46 18.07 18.86 17.82 16.24 14.34 14.28 14.59 15.99 16.72 17.27  1
"QV2M" 1982 17.03  17.4 18.13 18.43 17.58 15.26 14.22 13.37 14.34 15.01 15.99 16.66  2
"QV2M" 1983 16.17  17.7 18.55 18.37 17.46 14.77 13.67 12.94 13.67 15.69 15.99 16.54  3
"QV2M" 1984 16.54 17.21 17.64 17.82 17.58  16.6  15.2 14.71 15.38 15.99 16.66 16.78  4
"QV2M" 1985 17.21 16.48 17.94 18.01 17.76 15.38 14.59 14.34 14.53 15.62 16.66 16.97  5
"QV2M" 1986 16.91 17.15 17.27 18.55 17.94 15.75 13.79 13.73 14.16 14.95 16.36 16.17  6
"QV2M" 1987 16.72 17.58 18.19 18.68 18.37 15.56 15.56 15.38 15.32  16.3  17.4  17.4  7
"QV2M" 1988 17.88 18.86 18.98 19.04 18.86 16.97 15.08 14.95  15.5 16.48 17.46  17.4  8
"QV2M" 1989 16.78 17.94 17.82 18.74 18.49 16.42 14.53 14.95  15.5  16.3 17.33  17.4  9
"QV2M" 1990 17.58 18.19 19.23 19.29 18.31 15.99 14.65 14.77 15.44 15.87  17.4 17.58 10
"QV2M" 1991 17.88 18.49 18.98 18.62 18.37 16.78 14.65 14.53 15.01  15.5 16.72 17.15 11
"QV2M" 1992  17.4 18.07 18.86  18.8 17.64 15.14  14.1 13.67 14.77 15.99 16.97 17.27 12
"QV2M" 1993 17.33 18.01 18.31 19.04 18.01 15.44 14.53 14.59 15.99 16.85  17.7 18.07 13
"QV2M" 1994 18.07 18.01 18.74 19.17 18.25 15.38  14.4 14.65 15.14 16.36 17.52 17.64 14
"QV2M" 1995 18.13 18.49 19.04 19.35 18.62 16.54 15.32 15.38 15.87 16.36  17.4 17.82 15
"QV2M" 1996 18.31  18.8  18.8 19.04 18.68 15.99 14.47 14.53  15.2 15.93 16.91 17.46 16
"QV2M" 1997  17.7 18.19  19.1 18.19 17.64 14.71 14.53 14.89 15.56 16.66 17.64 18.13 17
"QV2M" 1998 18.86 19.47 20.14 20.39 18.62 17.03  15.2  15.2 15.81 16.48 17.82 18.01 18
"QV2M" 1999 18.19 18.68  19.1  18.8 18.49 16.91  15.2  15.5  15.5 15.93 17.46 18.13 19
"QV2M" 2000 18.49 18.43 19.04 19.23 18.68 15.69  14.4 14.59 15.32 16.48 17.27 17.88 20
"QV2M" 2001 18.19 18.74 19.23 19.35 18.62 15.81 14.65 14.22 14.77 16.42 17.09 18.07 21
"QV2M" 2002 18.19 18.62 19.41 19.29 18.86 16.54 15.38 15.44  15.5  16.6  17.7 18.25 22
"QV2M" 2003 18.49 19.47 19.47 19.35 18.31 16.24 15.62  15.2 16.11  17.4 18.19 18.68 23
"QV2M" 2004 18.92 19.29 19.47 18.92 17.15 15.75 14.95 15.44 16.36 17.21 17.82 18.37 24
"QV2M" 2005 18.74  19.9  19.9 19.84 17.64 15.32 14.89 14.59 15.69 16.78 18.13 18.25 25
"QV2M" 2006  19.1  19.1 18.98 19.53 18.31 17.09 14.95 13.98 15.38 16.72 17.82 18.07 26
"QV2M" 2007 18.74 19.04 19.29 19.59 19.23 16.05 14.83  15.2  16.3 16.78 17.88 18.13 27
"QV2M" 2008 18.07 18.62  19.1 19.04 18.98  16.3 15.26  15.5 16.54  17.4 18.13 18.19 28
"QV2M" 2009 18.62 18.98 19.53 19.29 19.17 16.42 14.65 14.89 16.24  16.3 17.82 18.37 29
"QV2M" 2010 18.86 19.53 19.41  20.2 18.37 16.05 14.59 14.95 15.81 17.03 18.07 18.19 30
"QV2M" 2011 18.19 18.43 18.92 19.23 18.55 16.05 14.53 14.95 16.11 16.97 17.52  17.7 31
"QV2M" 2012 17.64 18.19 19.29 19.29 18.13 16.17 14.77 14.71 15.87 16.85  17.7 18.13 32
"QV2M" 2013 18.86  19.1 19.29 19.41 18.13 15.75 14.22  14.1 14.95  16.3 17.33 17.64 33
"QV2M" 2014 18.01 18.62 18.92 19.23  18.8 16.05 14.47 14.34 15.62 16.54 17.46 17.58 34
"QV2M" 2015 17.76 18.55 19.23 19.17 18.98 15.62 15.26 15.08 15.69 16.72 17.58 17.94 35
"QV2M" 2016 18.25 19.41 19.59 19.96 18.49 15.99 15.56 15.32 16.24  17.4 18.43 18.62 36
"QV2M" 2017 18.68 19.04 19.59 19.65 18.43 16.85 14.59 14.89 15.44 17.21  17.7 18.13 37
"QV2M" 2018 18.13 19.04 18.49 18.55 18.37 16.72 15.44 15.26  16.6 17.58 18.37 18.43 38
"QV2M" 2019 18.55 19.23 19.17 19.78  18.8 15.87 15.69 15.56 16.78 17.33 18.74 18.62 39
"QV2M" 2020 18.68 19.53 19.78 19.47  19.1 16.05 14.65 14.59 15.26  16.6 18.01 18.31 40
"QV2M" 2021 18.31 18.92 18.74 19.41 18.68 17.33 15.38 15.44 16.78 17.46 18.37 18.43 41
"QV2M" 2022 18.49  18.8  19.1  19.1 18.37 16.54 14.59  14.4 15.93 16.91 17.88 18.19 42
end

I want to get the time series data from this so that I have Year Month and value of a variable please

how I can do this in stata please

[Olivia Emma]

Clyde Schechter

Can you guide me

Thanks

[Fredrick orwa]

local j=1
foreach var of varlist JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC {
ren `var' Jan`j'
local j=`j'+1
}
reshape long Jan,i(YEAR) j(Month)

Here wego.You can rename the variable Jan to whatever quantity it is. I don't think you need the variable Parameter since its the same across.Also You can use YEAR in place of id so I think you don't need id too but its your choice.

[Clyde Schechter]

I think this code is a little more transparent and provides a true Stata monthly date variable to index the time series:

rename (JAN-DEC) var=
reshape long var, i(id) j(MONTH) string
gen int mdate = monthly(MONTH+string(YEAR), "MY")
format mdate %tm
isid mdate, sort
keep mdate var

When this is done, you can -rename- var to something that actually describes what it is.

[Olivia Emma]

Thanks Clyde Schechter


----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input strL v1 str4 v2 str5(v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15)
"-BEGIN HEADER-"                                                                                                   ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"NASA/POWER CERES/MERRA2 Native Resolution Monthly and Annual "                                                    ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"Dates (month/day/year): 01/01/1981 through 12/31/2022 "                                                           ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"Location: Latitude  0.0   Longitude 0.0 "                                                                         ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"Elevation from MERRA-2: Average for 0.5 x 0.625 degree lat/lon region = 0.0 meters"                               ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"The value for missing source data that cannot be computed or is outside of the sources availability range: -999 " ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"Parameter(s): "                                                                                                   ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"QV2M     MERRA-2 Specific Humidity at 2 Meters (g/kg) "                                                           ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"-END HEADER-"                                                                                                     ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""     
"PARAMETER"                                                                                                        "YEAR" "JAN"   "FEB"   "MAR"   "APR"   "MAY"   "JUN"   "JUL"   "AUG"   "SEP"   "OCT"   "NOV"   "DEC"   "ANN"  
"QV2M"                                                                                                             "1981" "16.78" "17.46" "18.07" "18.86" "17.82" "16.24" "14.34" "14.28" "14.59" "15.99" "16.72" "17.27" "16.54"
"QV2M"                                                                                                             "1982" "17.03" "17.4"  "18.13" "18.43" "17.58" "15.26" "14.22" "13.37" "14.34" "15.01" "15.99" "16.66" "16.11"
"QV2M"                                                                                                             "1983" "16.17" "17.7"  "18.55" "18.37" "17.46" "14.77" "13.67" "12.94" "13.67" "15.69" "15.99" "16.54" "15.99"
"QV2M"                                                                                                             "1984" "16.54" "17.21" "17.64" "17.82" "17.58" "16.6"  "15.2"  "14.71" "15.38" "15.99" "16.66" "16.78" "16.48"
"QV2M"                                                                                                             "1985" "17.21" "16.48" "17.94" "18.01" "17.76" "15.38" "14.59" "14.34" "14.53" "15.62" "16.66" "16.97" "16.3"
"QV2M"                                                                                                             "1986" "16.91" "17.15" "17.27" "18.55" "17.94" "15.75" "13.79" "13.73" "14.16" "14.95" "16.36" "16.17" "16.05"
"QV2M"                                                                                                             "1987" "16.72" "17.58" "18.19" "18.68" "18.37" "15.56" "15.56" "15.38" "15.32" "16.3"  "17.4"  "17.4"  "16.85"
"QV2M"                                                                                                             "1988" "17.88" "18.86" "18.98" "19.04" "18.86" "16.97" "15.08" "14.95" "15.5"  "16.48" "17.46" "17.4"  "17.27"
"QV2M"                                                                                                             "1989" "16.78" "17.94" "17.82" "18.74" "18.49" "16.42" "14.53" "14.95" "15.5"  "16.3"  "17.33" "17.4"  "16.85"
"QV2M"                                                                                                             "1990" "17.58" "18.19" "19.23" "19.29" "18.31" "15.99" "14.65" "14.77" "15.44" "15.87" "17.4"  "17.58" "17.03"
"QV2M"                                                                                                             "1991" "17.88" "18.49" "18.98" "18.62" "18.37" "16.78" "14.65" "14.53" "15.01" "15.5"  "16.72" "17.15" "16.91"
"QV2M"                                                                                                             "1992" "17.4"  "18.07" "18.86" "18.8"  "17.64" "15.14" "14.1"  "13.67" "14.77" "15.99" "16.97" "17.27" "16.54"
"QV2M"                                                                                                             "1993" "17.33" "18.01" "18.31" "19.04" "18.01" "15.44" "14.53" "14.59" "15.99" "16.85" "17.7"  "18.07" "16.97"
"QV2M"                                                                                                             "1994" "18.07" "18.01" "18.74" "19.17" "18.25" "15.38" "14.4"  "14.65" "15.14" "16.36" "17.52" "17.64" "16.97"
"QV2M"                                                                                                             "1995" "18.13" "18.49" "19.04" "19.35" "18.62" "16.54" "15.32" "15.38" "15.87" "16.36" "17.4"  "17.82" "17.33"
"QV2M"                                                                                                             "1996" "18.31" "18.8"  "18.8"  "19.04" "18.68" "15.99" "14.47" "14.53" "15.2"  "15.93" "16.91" "17.46" "16.97"
"QV2M"                                                                                                             "1997" "17.7"  "18.19" "19.1"  "18.19" "17.64" "14.71" "14.53" "14.89" "15.56" "16.66" "17.64" "18.13" "16.91"
"QV2M"                                                                                                             "1998" "18.86" "19.47" "20.14" "20.39" "18.62" "17.03" "15.2"  "15.2"  "15.81" "16.48" "17.82" "18.01" "17.76"
"QV2M"                                                                                                             "1999" "18.19" "18.68" "19.1"  "18.8"  "18.49" "16.91" "15.2"  "15.5"  "15.5"  "15.93" "17.46" "18.13" "17.33"
"QV2M"                                                                                                             "2000" "18.49" "18.43" "19.04" "19.23" "18.68" "15.69" "14.4"  "14.59" "15.32" "16.48" "17.27" "17.88" "17.09"
"QV2M"                                                                                                             "2001" "18.19" "18.74" "19.23" "19.35" "18.62" "15.81" "14.65" "14.22" "14.77" "16.42" "17.09" "18.07" "17.09"
"QV2M"                                                                                                             "2002" "18.19" "18.62" "19.41" "19.29" "18.86" "16.54" "15.38" "15.44" "15.5"  "16.6"  "17.7"  "18.25" "17.46"
"QV2M"                                                                                                             "2003" "18.49" "19.47" "19.47" "19.35" "18.31" "16.24" "15.62" "15.2"  "16.11" "17.4"  "18.19" "18.68" "17.7"
"QV2M"                                                                                                             "2004" "18.92" "19.29" "19.47" "18.92" "17.15" "15.75" "14.95" "15.44" "16.36" "17.21" "17.82" "18.37" "17.46"
"QV2M"                                                                                                             "2005" "18.74" "19.9"  "19.9"  "19.84" "17.64" "15.32" "14.89" "14.59" "15.69" "16.78" "18.13" "18.25" "17.46"
"QV2M"                                                                                                             "2006" "19.1"  "19.1"  "18.98" "19.53" "18.31" "17.09" "14.95" "13.98" "15.38" "16.72" "17.82" "18.07" "17.4"
"QV2M"                                                                                                             "2007" "18.74" "19.04" "19.29" "19.59" "19.23" "16.05" "14.83" "15.2"  "16.3"  "16.78" "17.88" "18.13" "17.58"
"QV2M"                                                                                                             "2008" "18.07" "18.62" "19.1"  "19.04" "18.98" "16.3"  "15.26" "15.5"  "16.54" "17.4"  "18.13" "18.19" "17.58"
"QV2M"                                                                                                             "2009" "18.62" "18.98" "19.53" "19.29" "19.17" "16.42" "14.65" "14.89" "16.24" "16.3"  "17.82" "18.37" "17.52"
"QV2M"                                                                                                             "2010" "18.86" "19.53" "19.41" "20.2"  "18.37" "16.05" "14.59" "14.95" "15.81" "17.03" "18.07" "18.19" "17.58"
"QV2M"                                                                                                             "2011" "18.19" "18.43" "18.92" "19.23" "18.55" "16.05" "14.53" "14.95" "16.11" "16.97" "17.52" "17.7"  "17.27"
"QV2M"                                                                                                             "2012" "17.64" "18.19" "19.29" "19.29" "18.13" "16.17" "14.77" "14.71" "15.87" "16.85" "17.7"  "18.13" "17.21"
"QV2M"                                                                                                             "2013" "18.86" "19.1"  "19.29" "19.41" "18.13" "15.75" "14.22" "14.1"  "14.95" "16.3"  "17.33" "17.64" "17.09"
"QV2M"                                                                                                             "2014" "18.01" "18.62" "18.92" "19.23" "18.8"  "16.05" "14.47" "14.34" "15.62" "16.54" "17.46" "17.58" "17.15"
"QV2M"                                                                                                             "2015" "17.76" "18.55" "19.23" "19.17" "18.98" "15.62" "15.26" "15.08" "15.69" "16.72" "17.58" "17.94" "17.27"
"QV2M"                                                                                                             "2016" "18.25" "19.41" "19.59" "19.96" "18.49" "15.99" "15.56" "15.32" "16.24" "17.4"  "18.43" "18.62" "17.76"
"QV2M"                                                                                                             "2017" "18.68" "19.04" "19.59" "19.65" "18.43" "16.85" "14.59" "14.89" "15.44" "17.21" "17.7"  "18.13" "17.52"
"QV2M"                                                                                                             "2018" "18.13" "19.04" "18.49" "18.55" "18.37" "16.72" "15.44" "15.26" "16.6"  "17.58" "18.37" "18.43" "17.58"
"QV2M"                                                                                                             "2019" "18.55" "19.23" "19.17" "19.78" "18.8"  "15.87" "15.69" "15.56" "16.78" "17.33" "18.74" "18.62" "17.82"
"QV2M"                                                                                                             "2020" "18.68" "19.53" "19.78" "19.47" "19.1"  "16.05" "14.65" "14.59" "15.26" "16.6"  "18.01" "18.31" "17.52"
"QV2M"                                                                                                             "2021" "18.31" "18.92" "18.74" "19.41" "18.68" "17.33" "15.38" "15.44" "16.78" "17.46" "18.37" "18.43" "17.76"
"QV2M"                                                                                                             "2022" "18.49" "18.8"  "19.1"  "19.1"  "18.37" "16.54" "14.59" "14.4"  "15.93" "16.91" "17.88" "18.19" "17.33"
end

------------------ copy up to and including the previous line ------------------

I have this uncleaned data please in the 4th row I have the value of Latitude and Longitude I want to extract the value of Latitude and Longitude from the 4th row and want to make this as a variables please

Thanks

[Hemanshu Kumar]

Perhaps this?

gen latitude = regexs(1) if regexm(v1[4], "Latitude\s*(\d*\.\d*)")
gen longitude = regexs(1) if regexm(v1[4], "Longitude\s*(\d*\.\d*)")

[Clyde Schechter]

Here's how I would clean this data set:

frame put v1 in 4, into(lat_long)
frame lat_long {
    split v1, gen(token)
    local latitude = real(token3)
    local longitude = real(token5)
}

drop in 1/9
foreach v of varlist _all {
    rename `v' `=`v'[1]'
}
drop in 1
gen latitude = `latitude'
gen longitude = `longitude'

frame drop lat_long

rename (JAN-DEC) var=
reshape long var, i(YEAR) j(month) string
gen mdate = monthly(month+YEAR, "MY")
format mdate %tm
drop YEAR month
destring var ANN, replace
tsset mdate

I would also rename var to something that describes what that variable represents.

Added: Crossed with #2.

[Olivia Emma]

Perhaps this?

gen latitude = regexs(1) if regexm(v1[4], "Latitude\s*(\d*\.\d*)")
gen longitude = regexs(1) if regexm(v1[4], "Longitude\s*(\d*\.\d*)")

. gen latitude = regexs(1) if regexm(v1[4], "Latitude\s*(\d*\.\d*)")
(52 missing values generated)

. gen longitude = regexs(1) if regexm(v1[4], "Longitude\s*(\d*\.\d*)")
(52 missing values generated)


This is generating missing values please I want to have the numerical values of lat and long please

[Hemanshu Kumar]

When I run my lines on your extract in #5, I get string variables for latitude and longitude, with all their values "0.0". I don't get missing values. Can you double-check what you are doing?
If you want numeric variables, you can simply do:

gen latitude = real(regexs(1)) if regexm(v1[4], "Latitude\s*(\d*\.\d*)")
gen longitude = real(regexs(1)) if regexm(v1[4], "Longitude\s*(\d*\.\d*)")

where I have used real() to convert the string into numbers.

[Olivia Emma]

When I run my lines on your extract in #5, I get string variables for latitude and longitude, with all their values "0.0". I don't get missing values. Can you double-check what you are doing?
If you want numeric variables, you can simply do:

gen latitude = real(regexs(1)) if regexm(v1[4], "Latitude\s*(\d*\.\d*)")
gen longitude = real(regexs(1)) if regexm(v1[4], "Longitude\s*(\d*\.\d*)")

where I have used real() to convert the string into numbers.

I am doing this please

import delimited "POWER_Point_Monthly_19810101_20221231_000d00N_000 d00E_UTC.csv", clear


gen latitude = real(regexs(1)) if regexm(v1[4], "Latitude\s*(\d*\.\d*)")
gen longitude = real(regexs(1)) if regexm(v1[4], "Longitude\s*(\d*\.\d*)")



----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input strL v1 str4 v2 str5(v3 v4 v5 v6 v7 v8 v9 v10 v11 v12 v13 v14 v15) float(latitude longitude)
"-BEGIN HEADER-"                                                                                                   ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"NASA/POWER CERES/MERRA2 Native Resolution Monthly and Annual "                                                    ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"Dates (month/day/year): 01/01/1981 through 12/31/2022 "                                                           ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"Location: Latitude  0.0   Longitude 0.0 "                                                                         ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"Elevation from MERRA-2: Average for 0.5 x 0.625 degree lat/lon region = 0.0 meters"                               ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"The value for missing source data that cannot be computed or is outside of the sources availability range: -999 " ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"Parameter(s): "                                                                                                   ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"QV2M     MERRA-2 Specific Humidity at 2 Meters (g/kg) "                                                           ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"-END HEADER-"                                                                                                     ""     ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      ""      . .
"PARAMETER"                                                                                                        "YEAR" "JAN"   "FEB"   "MAR"   "APR"   "MAY"   "JUN"   "JUL"   "AUG"   "SEP"   "OCT"   "NOV"   "DEC"   "ANN"   . .
"QV2M"                                                                                                             "1981" "16.78" "17.46" "18.07" "18.86" "17.82" "16.24" "14.34" "14.28" "14.59" "15.99" "16.72" "17.27" "16.54" . .
"QV2M"                                                                                                             "1982" "17.03" "17.4"  "18.13" "18.43" "17.58" "15.26" "14.22" "13.37" "14.34" "15.01" "15.99" "16.66" "16.11" . .
"QV2M"                                                                                                             "1983" "16.17" "17.7"  "18.55" "18.37" "17.46" "14.77" "13.67" "12.94" "13.67" "15.69" "15.99" "16.54" "15.99" . .
"QV2M"                                                                                                             "1984" "16.54" "17.21" "17.64" "17.82" "17.58" "16.6"  "15.2"  "14.71" "15.38" "15.99" "16.66" "16.78" "16.48" . .
"QV2M"                                                                                                             "1985" "17.21" "16.48" "17.94" "18.01" "17.76" "15.38" "14.59" "14.34" "14.53" "15.62" "16.66" "16.97" "16.3"  . .
"QV2M"                                                                                                             "1986" "16.91" "17.15" "17.27" "18.55" "17.94" "15.75" "13.79" "13.73" "14.16" "14.95" "16.36" "16.17" "16.05" . .
"QV2M"                                                                                                             "1987" "16.72" "17.58" "18.19" "18.68" "18.37" "15.56" "15.56" "15.38" "15.32" "16.3"  "17.4"  "17.4"  "16.85" . .
"QV2M"                                                                                                             "1988" "17.88" "18.86" "18.98" "19.04" "18.86" "16.97" "15.08" "14.95" "15.5"  "16.48" "17.46" "17.4"  "17.27" . .
"QV2M"                                                                                                             "1989" "16.78" "17.94" "17.82" "18.74" "18.49" "16.42" "14.53" "14.95" "15.5"  "16.3"  "17.33" "17.4"  "16.85" . .
"QV2M"                                                                                                             "1990" "17.58" "18.19" "19.23" "19.29" "18.31" "15.99" "14.65" "14.77" "15.44" "15.87" "17.4"  "17.58" "17.03" . .
"QV2M"                                                                                                             "1991" "17.88" "18.49" "18.98" "18.62" "18.37" "16.78" "14.65" "14.53" "15.01" "15.5"  "16.72" "17.15" "16.91" . .
"QV2M"                                                                                                             "1992" "17.4"  "18.07" "18.86" "18.8"  "17.64" "15.14" "14.1"  "13.67" "14.77" "15.99" "16.97" "17.27" "16.54" . .
"QV2M"                                                                                                             "1993" "17.33" "18.01" "18.31" "19.04" "18.01" "15.44" "14.53" "14.59" "15.99" "16.85" "17.7"  "18.07" "16.97" . .
"QV2M"                                                                                                             "1994" "18.07" "18.01" "18.74" "19.17" "18.25" "15.38" "14.4"  "14.65" "15.14" "16.36" "17.52" "17.64" "16.97" . .
"QV2M"                                                                                                             "1995" "18.13" "18.49" "19.04" "19.35" "18.62" "16.54" "15.32" "15.38" "15.87" "16.36" "17.4"  "17.82" "17.33" . .
"QV2M"                                                                                                             "1996" "18.31" "18.8"  "18.8"  "19.04" "18.68" "15.99" "14.47" "14.53" "15.2"  "15.93" "16.91" "17.46" "16.97" . .
"QV2M"                                                                                                             "1997" "17.7"  "18.19" "19.1"  "18.19" "17.64" "14.71" "14.53" "14.89" "15.56" "16.66" "17.64" "18.13" "16.91" . .
"QV2M"                                                                                                             "1998" "18.86" "19.47" "20.14" "20.39" "18.62" "17.03" "15.2"  "15.2"  "15.81" "16.48" "17.82" "18.01" "17.76" . .
"QV2M"                                                                                                             "1999" "18.19" "18.68" "19.1"  "18.8"  "18.49" "16.91" "15.2"  "15.5"  "15.5"  "15.93" "17.46" "18.13" "17.33" . .
"QV2M"                                                                                                             "2000" "18.49" "18.43" "19.04" "19.23" "18.68" "15.69" "14.4"  "14.59" "15.32" "16.48" "17.27" "17.88" "17.09" . .
"QV2M"                                                                                                             "2001" "18.19" "18.74" "19.23" "19.35" "18.62" "15.81" "14.65" "14.22" "14.77" "16.42" "17.09" "18.07" "17.09" . .
"QV2M"                                                                                                             "2002" "18.19" "18.62" "19.41" "19.29" "18.86" "16.54" "15.38" "15.44" "15.5"  "16.6"  "17.7"  "18.25" "17.46" . .
"QV2M"                                                                                                             "2003" "18.49" "19.47" "19.47" "19.35" "18.31" "16.24" "15.62" "15.2"  "16.11" "17.4"  "18.19" "18.68" "17.7"  . .
"QV2M"                                                                                                             "2004" "18.92" "19.29" "19.47" "18.92" "17.15" "15.75" "14.95" "15.44" "16.36" "17.21" "17.82" "18.37" "17.46" . .
"QV2M"                                                                                                             "2005" "18.74" "19.9"  "19.9"  "19.84" "17.64" "15.32" "14.89" "14.59" "15.69" "16.78" "18.13" "18.25" "17.46" . .
"QV2M"                                                                                                             "2006" "19.1"  "19.1"  "18.98" "19.53" "18.31" "17.09" "14.95" "13.98" "15.38" "16.72" "17.82" "18.07" "17.4"  . .
"QV2M"                                                                                                             "2007" "18.74" "19.04" "19.29" "19.59" "19.23" "16.05" "14.83" "15.2"  "16.3"  "16.78" "17.88" "18.13" "17.58" . .
"QV2M"                                                                                                             "2008" "18.07" "18.62" "19.1"  "19.04" "18.98" "16.3"  "15.26" "15.5"  "16.54" "17.4"  "18.13" "18.19" "17.58" . .
"QV2M"                                                                                                             "2009" "18.62" "18.98" "19.53" "19.29" "19.17" "16.42" "14.65" "14.89" "16.24" "16.3"  "17.82" "18.37" "17.52" . .
"QV2M"                                                                                                             "2010" "18.86" "19.53" "19.41" "20.2"  "18.37" "16.05" "14.59" "14.95" "15.81" "17.03" "18.07" "18.19" "17.58" . .
"QV2M"                                                                                                             "2011" "18.19" "18.43" "18.92" "19.23" "18.55" "16.05" "14.53" "14.95" "16.11" "16.97" "17.52" "17.7"  "17.27" . .
"QV2M"                                                                                                             "2012" "17.64" "18.19" "19.29" "19.29" "18.13" "16.17" "14.77" "14.71" "15.87" "16.85" "17.7"  "18.13" "17.21" . .
"QV2M"                                                                                                             "2013" "18.86" "19.1"  "19.29" "19.41" "18.13" "15.75" "14.22" "14.1"  "14.95" "16.3"  "17.33" "17.64" "17.09" . .
"QV2M"                                                                                                             "2014" "18.01" "18.62" "18.92" "19.23" "18.8"  "16.05" "14.47" "14.34" "15.62" "16.54" "17.46" "17.58" "17.15" . .
"QV2M"                                                                                                             "2015" "17.76" "18.55" "19.23" "19.17" "18.98" "15.62" "15.26" "15.08" "15.69" "16.72" "17.58" "17.94" "17.27" . .
"QV2M"                                                                                                             "2016" "18.25" "19.41" "19.59" "19.96" "18.49" "15.99" "15.56" "15.32" "16.24" "17.4"  "18.43" "18.62" "17.76" . .
"QV2M"                                                                                                             "2017" "18.68" "19.04" "19.59" "19.65" "18.43" "16.85" "14.59" "14.89" "15.44" "17.21" "17.7"  "18.13" "17.52" . .
"QV2M"                                                                                                             "2018" "18.13" "19.04" "18.49" "18.55" "18.37" "16.72" "15.44" "15.26" "16.6"  "17.58" "18.37" "18.43" "17.58" . .
"QV2M"                                                                                                             "2019" "18.55" "19.23" "19.17" "19.78" "18.8"  "15.87" "15.69" "15.56" "16.78" "17.33" "18.74" "18.62" "17.82" . .
"QV2M"                                                                                                             "2020" "18.68" "19.53" "19.78" "19.47" "19.1"  "16.05" "14.65" "14.59" "15.26" "16.6"  "18.01" "18.31" "17.52" . .
"QV2M"                                                                                                             "2021" "18.31" "18.92" "18.74" "19.41" "18.68" "17.33" "15.38" "15.44" "16.78" "17.46" "18.37" "18.43" "17.76" . .
"QV2M"                                                                                                             "2022" "18.49" "18.8"  "19.1"  "19.1"  "18.37" "16.54" "14.59" "14.4"  "15.93" "16.91" "17.88" "18.19" "17.33" . .
end

------------------ copy up to and including the previous line ------------------

Listed 52 out of 52 observations

[Hemanshu Kumar]

What version of Stata are you using? I am on Stata 18. I tried using my code with version control for Stata 16.1 or even Stata 17, and I get missing values as well. Perhaps that is the reason. Let me try and see how to make the code work on earlier versions of Stata.

[Hemanshu Kumar]

This seems to work on Stata 16:

gen latitude = real(regexs(1)) if regexm(v1[4], "Latitude[ ]*([0-9]*\.[0-9]*)")
gen longitude = real(regexs(1)) if regexm(v1[4], "Longitude[ ]*([0-9]*\.[0-9]*)")