Drop - last observation in each row

[Amir Tal]

Hi Everyone,

Is there a way (command/code) in Stata to drop the last "row-wise varlist" in each row (observation) instead of dropping the variable name?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 var11 str15 var12 str28 var13 str14 var18 str18 var19 str13 var110 str10(var111 var112) str2 var113
"85111" "Adelschlag"      "Eichstätter"                 ""               ""                   ""              ""           ""           ""  
"83404" "Ainring"         "Bundesstraße"                "innerorts"      "50"                 ""              ""           ""           ""  
"83404" "Ainring"         "Bundesstraße"                "innerorts"      "50"                 ""              ""           ""           ""  
"83404" "Ainring"         "Ulrichshögler"               "50"             ""                   ""              ""           ""           ""  
"83544" "Albaching"       "Kreisstraße"                 "Kreisstraße"   "außerorts"         "70"            ""           ""           ""  
"82239" "Alling"          "Bundesstraße"                "Eichenau"       "Bundesstraße"      "außerorts"    "80"         ""           ""  
"83352" "Altenmarkt"      "Bundesstraße"                "außerorts"     "100"                ""              ""           ""           ""  
"86972" "Altenstadt"      "Franz-Josef-Strauß-Straße/" ""               ""                   ""              ""           ""           ""  
"84503" "Altötting"      "Fabrikstraße"                ""               ""                   ""              ""           ""           ""  
"84503" "Altötting"      "Staatsstraße"                "außerorts"     "80"                 ""              ""           ""           ""  
"82346" "Andechs"         "Herrschinger"                 "Staatsstraße"  "innerorts"          "50"            ""           ""           ""  
"82346" "Andechs"         "Kreissstraße"                "Strecke"        "Andechs-Rothenfeld" "Kreisstraße"  "außerorts" "100"        ""  
"82387" "Antdorf"         "Iffeldorfer"                  "50"             ""                   ""              ""           ""           ""  
"86561" "Aresing"         "St"                           "-"              "Aresing"            "Staatsstraße" "außerorts" "100"        ""  
"84544" "Aschau"          "a.Inn"                        ""               ""                   ""              ""           ""           ""  
"83229" "Aschau"          "i."                           "Kiesgrube"      "Fellerer"           "Staatsstraße" "außerorts" "100"        ""  
"83229" "Aschau"          "i."                           "Stein"          "Staatsstraße"      "außerorts"    "70"         ""           ""  
"85609" "Aschheim"        "Eichendorffstraße"           ""               ""                   ""              ""           ""           ""  
"83623" "Ascholding"      "Staatsstraße"                ""               ""                   ""              ""           ""           ""  
"84072" "Au"              "(Dellnhausen)"                "50"             ""                   ""              ""           ""           ""  
"84072" "Au"              "(Hinkirchen)"                 "60"             ""                   ""              ""           ""           ""  
"85107" "Baar-Ebenhausen" "Münchener"                  "Straße"        "innerorts"          "50"            ""           ""           ""  
"83043" "Bad"             "Aibling"                      "50"             ""                   ""              ""           ""           ""  
"83043" "Bad"             "Aibling"                      "30"             ""                   ""              ""           ""           ""  
"83043" "Bad"             "Aibling"                      "innerorts"      "50"                 ""              ""           ""           ""  
"83043" "Bad"             "Aibling"                      "Am"             "Brand"              "sonstige"      "Straße"    "außerorts" "60"
"83043" "Bad"             "Aibling"                      "18"             "sonstige"           "Straße"       "innerorts"  "50"         ""  
"83093" "Bad"             "Endorf"                       "Staatsstraße"  "außerorts"         "100"           ""           ""           ""  
"83093" "Bad"             "Endorf"                       "Hartmannsberg"  "Staatsstraße"      "außerorts"    "70"         ""           ""  
"83075" "Bad"             "Feilnbach"                    "ST"             "2089"               "Staatsstraße" "innerorts"  "50"         ""  
"83670" "Bad"             "Heilbrunn"                    "50"             ""                   ""              ""           ""           ""  
"83435" "Bad"             "Reichenhall"                  "HEM-Tankstelle" "Bundesstraße"      "außerorts"    "60"         ""           ""  
"83435" "Bad"             "Reichenhall"                  "Nesselgraben"   "Staatsstraße"      "außerorts"    "60"         ""           ""  
"83435" "Bad"             "Reichenhall"                  "-"              "Feuerwehrhaus"      "Staatsstraße" "innerorts"  "50"         ""  
"83646" "Bad"             "Tölz"                        "50"             ""                   ""              ""           ""           ""  
"83646" "Bad"             "Tölz"                        "Bundesstraße"  "innerorts"          "50"            ""           ""           ""  
"83646" "Bad"             "Tölz"                        ""               ""                   ""              ""           ""           ""  
"83707" "Bad"             "Wiessee"                      "Bayersäge"     "Bundesstraße"      "außerorts"    "70"         ""           ""  
"83707" "Bad"             "Wiessee"                      "innerorts"      "50"                 ""              ""           ""           ""  
end

I am using a complicated dataset, and I am trying to rearrange vars. What I need is to drop the last observation in each row. I could not do this using, for example, drop var113, since the observations of var113 are by error listed under var112, var111, var110...etc.

Thank you.

[William Lisowski]

The words "drop" and "observation" have very specific meanings to Stata. An "observation" is what you are calling "row" (Stata is not Excel), not a single value. And the drop command will either remove a list of variables (what you might call "columns") from all observations (rows) or remove a list of observations ("rows"). As you recognize, you cannot drop one of your variables, and of course you do not want to drop any of your observations.

The best you can hope to do, it seems to me, is replace the final non-missing value in each row with a missing value (that is, the empty string).

ds
local vars `r(varlist)'
* add one more variable in case the last real variable is not missing
generate str dummy = ""
local numvar : word count `vars'
forvalues i = 1/`numvar' {
    local j = `i'+1
    local v1 : word `i' of `vars'
    local v2 : word `j' of `vars' dummy
    replace `v1' = "" if `v2'==""
}
drop dummy

Let's look at the first 5 observations.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 var11 str15 var12 str28 var13 str14 var18 str18 var19 str13 var110 str10(var111 var112) str2 var113
"85111" "Adelschlag" ""               ""             ""           "" "" "" ""
"83404" "Ainring"    "Bundesstraße"  "innerorts"    ""           "" "" "" ""
"83404" "Ainring"    "Bundesstraße"  "innerorts"    ""           "" "" "" ""
"83404" "Ainring"    "Ulrichshögler" ""             ""           "" "" "" ""
"83544" "Albaching"  "Kreisstraße"   "Kreisstraße" "außerorts" "" "" "" ""
end

[Amir Tal]

Thanks William! Very clear explanation. This what I am looking for. Works well.

[Nick Cox]

Stata's data model is rectangular. You can drop a variable if you don't want it, but you can't drop a variable only in certain observations, even if it is missing in those observations, as in #1.

[Amir Tal]

Thank you Nick Cox for your reply.