Keep variable if letter appears in the substring of the first row

[Francois Durant]

Hi,

I would like to keep the "value*" variables in my dataset only if the letter "M" appears in the substring of the first row. Annual, quarterly and monthly observations are mixed, and I only want to keep monthly ones (format yyyyMmm). Here is what I have:

* Example generated by -dataex-. To install: ssc install dataex
clear
input double(valueAQ valueAR valueAS) str6 valueAT str7(valueAU valueAV valueAW) str6 valueAX str7 valueAY str11 valueAZ str7 valueBA str16 valueBB str7 valueBC
 2011  2012  2013 "2013Q1" "2013M01" "2013M02" "2013M03" "2013Q2" "2013M04" "2013M05"     "2013M06" "2013Q3"           "2013M07"
    .     .     . ""       ""        ""        ""        ""       ""        ""            ""        ""                 ""       
 4.75     4     3 "3.75"   "3.75"    "3.75"    "3.75"    "3.75"   "3.75"    "3.75"        "3.75"    "3.5"              "3.5"    
    . 10.25  9.75 "10"     "10"      "10"      "10"      "10"     "10"      "10"          "10"      "9.75"             "10"     
  9.5   9.5   9.5 "9.5"    "9.5"     "9.5"     "9.5"     "9.5"    "9.5"     "9.5"         "9.5"     "9.5"              "9.5"    
    8     8  7.75 "8"      "8"       "8"       "8"       "8"      "8"       "8"           "8"       "8.5"              "8"      
 4.25     3   2.5 "3"      "3"       "3"       "3"       "2.75"   "3"       "2.75"        "2.75"    "2.5"              "2.75"   
 5.25     5  4.75 "4.75"   "5"       "4.75"    "4.75"    "4.75"   "4.75"    "4.75"        "4.75"    "4.75"             "4.75"   
  4.5   4.5   4.5 "4.5"    "4.5"     "4.5"     "4.5"     "4.5"    "4.5"     "4.5"         "4.5"     "4.5"              "4.5"    
   .5    .5    .5 ".5"     ".5"      ".5"      ".5"      ".5"     ".5"      ".5"          ".5"      ".5"               ".5"     
    5     5     5 "5"      "5"       "5"       "5"       "5"      "5"       "5"           "5"       "5"                "5"      
   45    30  23.5 "30"     "30"      "30"      "30"      "23.5"   "30"      "28.5"        "23.5"    "23.5"             "23.5"   
   11    11    11 "11"     "11"      "11"      "11"      "11"     "11"      "11"          "11"      "11"               "11"     
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
   11  7.25    10 "7.25"   "7.25"    "7.25"    "7.25"    "8"      "7.5"     "8"           "8"       "9"                "8.5"    
  .22   .03   .02 ".01"    ".03"     ".01"     ".01"     ".01"    ".01"     ".02"         ".01"     ".02"              ".02"    
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
    .     .  5.75 "5.75"   "5.75"    "5.75"    "5.75"    "5.75"   "5.75"    "5.75"        "5.75"    "5.75"             "5.75"   
    1     1     1 "1"      "1"       "1"       "1"       "1"      "1"       "1"           "1"       "1"                "1"      
 5.25     5   4.5 "5"      "5"       "5"       "5"       "5"      "5"       "5"           "5"       "5"                "5"      
   .5    .5    .5 ".5"     ".5"      ".5"      ".5"      ".5"     ".5"      ".5"          ".5"      ".5"               ".5"     
    .     .     . ""       ""        ""        ""        ""       ""        ""            ""        ""                 ""       
 4.75  4.25  3.25 "3.25"   "4"       "3.75"    "3.25"    "3.25"   "3.25"    "3.25"        "3.25"    "3.25"             "3.25"   
   20     4     2 "3"      "4"       "4"       "3"       "3"      "3"       "3"           "3"       "3"                "3"      
    5     5  3.75 "5"      "5"       "5"       "5"       "4"      "5"       "5"           "4"       "4"                "4"      
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
  .75   .05   .05 ".05"    ".05"     ".05"     ".05"     ".05"    ".05"     ".05"         ".05"     ".05"              ".05"    
  .75     .     . ""       ""        ""        ""        ""       ""        ""            ""        ""                 ""       
  6.8     5  6.25 "5"      "5"       "5"       "5"       "4.25"   "5"       "4.25"        "4.25"    "6.25"             "4.25"   
    .     .     . ""       ""        ""        ""        ""       ""        ""            ""        ""                 ""       
    1     .     . ""       ""        ""        ""        ""       ""        ""            "1"       "1"                "1"      
   .5    .5    .5 ".5"     ".5"      ".5"      ".5"      ".5"     ".5"      ".5"          ".5"      ".5"               ".5"     
   14    12    20 "12"     "12"      "12"      "12"      "18"     "12"      "14"          "18"      "20"               "20"     
 6.75  5.25  3.75 "4.5"    "5.25"    "4.75"    "4.5"     "4"      "4.5"     "4.25"        "4"       "3.75"             "4"      
 12.5    15    16 "15"     "15"      "15"      "15"      "16"     "15"      "16"          "16"      "16"               "16"     
  5.5     5     5 "5"      "5"       "5"       "5"       "5.25"   "5.25"    "5.25"        "5.25"    "5.25"             "5.25"   
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
  5.5  5.25     5 "5"      "5.25"    "5.25"    "5"       "5"      "5"       "5"           "5"       "5"                "5"      
  5.5     7     7 "7"      "7"       "7"       "7"       "7"      "7"       "7"           "7"       "7"                "7"      
    7  5.75     3 "5"      "5.5"     "5.25"    "5"       "4.25"   "4.75"    "4.5"         "4.25"    "3.6"              "4"      
4.125 5.375 5.375 "5.375"  "5.375"   "5.375"   "5.375"   "5.375"  "5.375"   "5.375"       "5.375"   "5.375"            "5.375"  
  8.5     8   7.5 "7.5"    "7.75"    "7.75"    "7.5"     "7.25"   "7.5"     "7.25"        "7.25"    "7.5"              "7.25"   
    6  5.75   7.5 "5.75"   "5.75"    "5.75"    "5.75"    "6"      "5.75"    "5.75"        "6"       "7.25"             "6.5"    
    6     6     6 "6"      "6"       "6"       "6"       "6"      "6"       "6"           "6"       "6"                "6"      
 2.75     2     1 "1.75"   "1.75"    "1.75"    "1.75"    "1.25"   "1.75"    "1.657142878" "1.25"    "1.25"             "1.25"   
    8  6.25  6.25 "6.25"   "6.25"    "6.25"    "6.25"    "6.25"   "6.25"    "6.25"        "6.25"    "6.25"             "6.25"   
  .05   .05     . ".05"    ".05"     ".05"     ".05"     ""       ".05"     ""            ""        ""                 ""       
 2.25     4   3.5 "4"      "4"       "4"       "4"       "4"      "4"       "4"           "4"       "3.75"             "4"      
  7.5   5.5   5.5 "5.5"    "5.5"     "5.5"     "5.5"     "5.5"    "5.5"     "5.5"         "5.5"     "5.5"              "5.5"    
   18    11   8.5 "9.5"    "11"      "9.5"     "9.5"     "8.5"    "9.5"     "8.5"         "8.5"     "8.5"              "8.5"    
 3.25  2.75   2.5 "2.75"   "2.75"    "2.75"    "2.75"    "2.5"    "2.75"    "2.5"         "2.5"     "2.5"              "2.5"    
13.61  2.64  4.17 "2.98"   "3.05"    "2.83"    "2.98"    "3.2"    "2.88"    "2.96"        "3.2"     "4.25"             "4.09"   
    3     3     3 "3"      "3"       "3"       "3"       "3"      "3"       "3"           "3"       "3"                "3"      
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
  5.4   4.9  4.65 "4.9"    "4.9"     "4.9"     "4.9"     "4.65"   "4.9"     "4.9"         "4.65"    "4.65"             "4.65"   
  4.5   4.5   3.5 "4"      "4.5"     "4.5"     "4"       "4"      "4"       "4"           "4"       "3.75"             "4"      
  9.5   4.5   3.5 "4.5"    "4.5"     "4.5"     "4.5"     "3.5"    "3.5"     "3.5"         "3.5"     "3.5"              "3.5"    
12.25 13.25  10.5 "12.5"   "12.5"    "12.5"    "12.5"    "10.5"   "11.5"    "11.5"        "10.5"    "10.5"             "10.5"   
    7     8     8 "8"      "8"       "8"       "8"       "8"      "8"       "8"           "8"       "8"                "8"      
  2.5   2.5   2.5 "2.5"    "2.5"     "2.5"     "2.5"     "2.5"    "2.5"     "2.5"         "2.5"     "2.5"              "2.5"    
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
   12    12    12 "12"     "12"      "12"      "12"      "12"     "12"      "12"          "12"      "12"               "12"     
 1.75   1.5   1.5 "1.5"    "1.5"     "1.5"     "1.5"     "1.5"    "1.5"     "1.5"         "1.5"     "1.5"              "1.5"    
 7.75  6.75  6.25 "6.25"   "6.75"    "6.75"    "6.25"    "6.25"   "6.25"    "6.25"        "6.25"    "6.25"             "6.25"   
 7.25   5.5     6 "5.5"    "5.5"     "5.5"     "5.5"     "5.5"    "5.5"     "5.5"         "5.5"     "5.5"              "5.5"    
 4.25  4.25     4 "4.25"   "4.25"    "4.25"    "4.25"    "4.25"   "4.25"    "4.25"        "4.25"    "4.25"             "4.25"   
  4.5   3.5   3.5 "3.5"    "3.5"     "3.5"     "3.5"     "3.5"    "3.5"     "3.5"         "3.5"     "3.5"              "3.5"    
  4.5  4.25   2.5 "3.25"   "4"       "3.75"    "3.25"    "2.75"   "3.25"    "3"           "2.75"    "2.5"              "2.5"    
  4.5   4.5   4.5 "4.5"    "4.5"     "4.5"     "4.5"     "4.5"    "4.5"     "4.5"         "4.5"     "4.5"              "4.5"    
    6  5.25     4 "5.25"   "5.25"    "5.25"    "5.25"    "5.25"   "5.25"    "5.25"        "5.25"    "4.5"              "5"      
    8  8.25   5.5 "8.25"   "8.25"    "8.25"    "8.25"    "8.25"   "8.25"    "8.25"        "8.25"    "5.5"              "8.25"   
    7   7.5     7 "7.5"    "7.5"     "7.5"     "7.5"     "7"      "7.5"     "7.5"         "7"       "7"                "7"      
   15    14    14 "14"     "14"      "14"      "14"      "14"     "14"      "14"          "14"      "14"               "14"     
  .25   .25   .25 ".25"    ".25"     ".25"     ".25"     ".25"    ".25"     ".25"         ".25"     ".25"              ".25"    
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
 9.75 11.25   9.5 "11.75"  "11.5"    "11.75"   "11.75"   "11"     "11.75"   "11.25"       "11"      "11"               "11"     
    .    20    10 "20"     "20"      "20"      "20"      "15"     "17"      "17"          "15"      "12"               "15"     
  .18   .18 .6375 ".61"    ".63"     ".61"     ".59"     ".64"    ".63"     ".65"         ".64"     ".666666666666667" ".63"    
    .     .     . ""       ""        ""        ""        ""       ""        ""            ""        ""                 ""       
  5.5     5     5 "5"      "5"       "5"       "5"       "5"      "5"       "5"           "5"       "5"                "5"      
    9     9    10 "9"      "9"       "9"       "9"       "9"      "9"       "9"           "9"       "10"               "9"      
 1.91  1.14     1 "1"      "1"       "1"       "1"       "1"      "1"       "1"           "1"       "1"                "1"      
  .25   .25   .25 ".25"    ".25"     ".25"     ".25"     ".25"    ".25"     ".25"         ".25"     ".25"              ".25"    
  9.8   6.5   5.5 "6.5"    "6.5"     "6.5"     "6.5"     "6.5"    "6.5"     "6.5"         "6.5"     "6.1"              "6.1"    
 3.25  2.75  2.25 "2.75"   "2.75"    "2.75"    "2.75"    "2.5"    "2.75"    "2.5"         "2.5"     "2.5"              "2.5"    
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
    .     .     . ""       ""        ""        ""        ""       ""        ""            ""        ""                 ""       
    5     5   3.5 "4.5"    "4.75"    "4.5"     "4.5"     "3.5"    "4"       "3.5"         "3.5"     "3.5"              "3.5"    
   .5    .5    .5 ".5"     ".5"      ".5"      ".5"      ".5"     ".5"      ".5"          ".5"      ".5"               ".5"     
 .125  .125  .125 ".125"   ".125"    ".125"    ".125"    ".125"   ".125"    ".125"        ".125"    ".125"             ".125"   
 8.75     9     . "9.25"   "9.25"    "9.25"    "9.25"    "9.25"   "9.25"    "9.25"        "9.25"    ""                 ""       
    .     .    12 "12"     "12"      "12"      "12"      "12"     "12"      "12"          "12"      "12"               "12"     
   15     9     7 "8"      "9"       "9"       "8"       "7"      "8"       "7"           "7"       "7"                "7"      
 3.25     3   2.5 "2.75"   "3"       "3"       "2.75"    "2.75"   "2.75"    "2.75"        "2.75"    "2.5"              "2.75"   
end

Thanks

[Ali Atia]

foreach var of varlist value*{
    if strpos("`=`var'[1]'","M")==0 drop `var'
}

[Francois Durant]

Thank you so much!

I am now trying to do the opposite: keep only annual values "yyyy" and drop quarterly and monthly variables. I have tried the following but I'm doing something wrong. please help:

foreach var of varlist value*{
    if strpos("`=`var'[1]'","M")==1 drop `var'
    if strpos("`=`var'[1]'","Q")==1 drop `var'
}

[Ali Atia]

You want !=0 rather than ==1. The -strpos- function returns the position of a character in a string rather than a dummy value. Also, it's better to do it in one line since if you drop a variable with the first line the second line will return an error:

foreach var of varlist value*{
    if strpos("`=`var'[1]'","M")!=0 | strpos("`=`var'[1]'","Q")!=0 drop `var'
}

[Francois Durant]

perfect, thank you so much again!

[Francois Durant]

Other very similar problem. I am trying to drop variables that are empty and have a "(B)" randomly placed in them. Here is the data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str15 A str3(U AC) str17 AD str3 AQ
"Country"         ""    ""    "1986"              ""   
"Australia"       ""    ""    "13.41667"          ""   
"Austria"         ""    ""    ".."                ""   
"Belgium"         ""    ""    "8.625833999999999" ""   
"Canada"          "(B)" ""    "9.145541"          ""   
"Chile"           ""    ""    ".."                ""   
"Colombia"        ""    ""    ".."                ""   
"Czech Republic"  ""    ""    ".."                ""   
"Denmark"         ""    ""    ".."                ""   
"Finland"         ""    ""    ".."                ""   
"France"          ""    "(B)" "9.119166"          ""   
"Germany"         ""    ""    "6.158333"          ""   
"Greece"          ""    ""    ".."                ""   
"Hungary"         ""    ""    ".."                ""   
"Iceland"         ""    ""    ".."                ""   
"Ireland"         ""    ""    "11.40265"          ""   
"Israel"          ""    ""    ".."                ""   
"Italy"           ""    ""    ".."                ""   
"Japan"           ""    ""    ".."                ""   
"Korea"           ""    ""    ".."                ""   
"Latvia"          ""    ""    ".."                ""   
"Lithuania"       ""    ""    ".."                ""   
"Luxembourg"      ""    ""    ".."                ""   
"Mexico"          ""    ""    ".."                ""   
"Netherlands"     ""    ""    "6.3175"            ""   
"New Zealand"     ""    ""    "16.4475"           ""   
"Norway"          ""    ""    "13.29917"          ""   
"Poland"          ""    ""    ".."                ""   
"Portugal"        ""    ""    ".."                ""   
"Slovak Republic" ""    ""    ".."                ""   
"Slovenia"        ""    ""    ".."                ""   
"Spain"           ""    ""    "11.35417"          ""   
"Sweden"          ""    ""    ".."                "(B)"
"Switzerland"     ""    ""    "4.229917"          ""   
"United Kingdom"  ""    ""    "10.135"            ""   
"United States"   ""    ""    "7.6825"            ""   
"Euro"            ""    ""    "8.8103"            ""   
end

Here is what I have tried:

local variables *
foreach var of varlist `variables'{
    if strpos(`var',"(B)")!=0 drop `var'
}

[Ali Atia]

foreach var of varlist *{
    sort `var'
    if `var'[_N]=="(B)" drop `var'
}

[Nick Cox]

These problems arise because metadata have been imported as if data. If possible, use the options of e.g. import excel to start at the first row of data proper.

[Francois Durant]

I have actually imported this way:

import excel "OECD_Interest_Rates.xlsx", clear sheet("Data") cellrange("A1")

[Nick Cox]

So, you should not start with A1 when row 1 includes metadata. I am not in general a fan of menus, but db import excel is my usual way into using that command whenever people throw Excel files at me. You get to see the top of the worksheet and can control what is set on one side as metadata.

If worksheets are consistent in layout, then the first time you use the dialog box indicates the syntax you need for others.