Excel-related macros are dropped when variables or observations are dropped

[Sophie Andrews]

After running the `describe` option on an Excel dataset, the macros `r(worksheet_[N])` are created. If you drop something in your dataset, by running something like `drop in 1` or `drop B`, that macro is deleted from memory. Why is this?

Here is reproducible example:


. import excel using "https://www.exceldemy.com/wp-content/uploads/2023/12/Project-Management-Sample-Data.xlsx", describe

Sheet | Range
------------------------+------------------------
Project Management Data | B2:H52

. di r(worksheet_1)
Project Management Data

. drop in 1
(1 observation deleted)

. di r(worksheet_1)
.

[daniel klein]

Form

help whatsnew15

13. drop and keep now store the number of observations dropped in r(N_drop) and the number of variables dropped in r(k_drop). This change means that these
commands now clear results previously stored in r() by other commands. The old behavior is preserved under version control.

[Nick Cox]

Good question, but this is designed behaviour.

drop is itself an r-class command that leaves r-class results in its wake. See the help for drop or run

return list

afterwards. It's part of the definition that r-class commands overwrite any r-class results left over from a previous r-class command. If you wish to keep their contents, you would need to copy them to local macros or scalars or use notes. There are other possibilities, but those seem the most appealing that I can think of.

One reason for this can be seen by thinking about successive uses of summarize. You can only see the last r-class resuls from the last summarize. If summarize just added to all previous r-class results, how could you distinguish them?

[Sophie Andrews]

Thanks Daniel! I wonder if this could be remedied in a future version, since it disrupts looping through Excel sheets. There are workarounds, of course (storing the r() macros in a separate local which is not removed), but this seems like suboptimal default behavior. [posted this before I saw Nick's reply -- thanks Nick!]

[daniel klein]

I didn't like that particular change either. Simplest workaround: type

version 15 : drop ...