Drop variables under certain conditions

[Ina Diak]

Dear All,

I am facing difficulties in dropping observations of some 300 variables and 140 of them have a common suffix. All these 140 variables end by _xt and I want to drop observations for all the 300 variables if at least one the 140 variables ending by _xt has a value <= 5. The following command does not work : drop if *_xt <=5 .

Thank you in advance!

[Andrew Musau]

In Stata, you either drop observations or drop variables. There is no such thing as dropping observations of a particular variable only.

foreach var of varlist *{
    if regexm("`var'", "$_xt") & `var'<=0.5{
       drop `var'
    }
}

[William Lisowski]

I think what is wanted is to drop observations where the minimum of the 140 *_xt variables is 5 or less. From that perspective then, the following example demonstrates the technique.

. set obs 1000
number of observations (_N) was 0, now 1,000

. set seed 666

. forvalues i = 1/140  {
  2.         generate v`i'_xt = runiform(1,1000)
  3. }

. egen todrop = rowmin(*_xt)

. drop if todrop<=5
(425 observations deleted)

[Ina Diak]

In Stata, you either drop observations or drop variables. There is no such thing as dropping observations of a particular variable only.

foreach var of varlist *{
if regexm("`var'", "$_xt") & `var'<=0.5{
drop `var'
}
}

Thank you Andrew. Indeed I want to drop observations for all the variables in the dataset if one or more of the 140 ending by _xt has a value <= 5. I tried the command you shared but it does not work. I received the following message : type mismatch r(109)

Thanks

[Andrew Musau]

Then William Lisowski's solution should work if your variables ending in _xt are numerical. Your error suggests that you have string variables ending in _xt. If these are numerical variables, see

help destring

Otherwise, here is a way to achieve what you want in the presence of both strings and numerical variables ending in _xt.

ds *_xt
local xtvars=r(varlist)
ds, has(type numeric)
local nvars=r(varlist)
local sxtvars: list xtvars-nvars
local nxtvars: list xtvars-sxtvars
egen todrop = rowmin(`nxtvars')
drop if todrop<=5

[Ina Diak]

I think what is wanted is to drop observations where the minimum of the 140 *_xt variables is 5 or less. From that perspective then, the following example demonstrates the technique.

. set obs 1000
number of observations (_N) was 0, now 1,000

. set seed 666

. forvalues i = 1/140 {
2. generate v`i'_xt = runiform(1,1000)
3. }

. egen todrop = rowmin(*_xt)

. drop if todrop<=5
(425 observations deleted)

Thank you William,
Instead, Indeed I want to drop observations for all the variables in the dataset if one or more of the 140 ending by _xt has a value <= 5.

[Ina Diak]

Then William Lisowski's solution should work if your variables ending in _xt are numerical. Your error suggests that you have string variables ending in _xt. If these are numerical variables, see

help destring

Otherwise, here is a way to achieve what you want in the presence of both strings and numerical variables ending in _xt.

ds *_xt
local xtvars=r(varlist)
ds, has(type numeric)
local nvars=r(varlist)
local sxtvars: list xtvars-nvars
local nxtvars: list xtvars-sxtvars
egen todrop = rowmin(`nxtvars')
drop if todrop<=5

Thank you Andrew, this code works perfectly.

[Andrew Musau]

Hi Ina, thanks for the closure. I realized that the macro subtraction is not necessary. For future reference, the following is sufficient.

ds *_xt, has(type numeric)
local nxtvars=r(varlist)
egen todrop = rowmin(`nxtvars')
drop if todrop<=5

[Nick Cox]

This should work too:

ds *_xt, has(type numeric)
egen todrop = rowmin(`r(varlist)')
drop if todrop<=5