Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# Re: st: Creating a dummy variable under certain conditions

 From Abhimanyu Arora To statalist@hsphsun2.harvard.edu Subject Re: st: Creating a dummy variable under certain conditions Date Mon, 4 Jun 2012 10:38:43 +0200

```No problem, it seems you have yet another condition.

try these two commands replacing the last line in the earlier solution
(replace new_viol_dummy=0 if ceil(_seq/5)!=floor(_seq/5) & _seq!=1)

replace new_viol_dummy=0 if ceil(_seq/6)!=floor(_seq/6) & _seq!=1
replace new_viol_dummy=0 if viol==0

On Mon, Jun 4, 2012 at 10:22 AM, sebas nicaise <sebasnicaise@hotmail.com> wrote:
> Dear Abhimanyu,
>
> First of all I am pleased with your quick responses and thoughts.
> However, The code still does not work :)
> When I enter : bys gvkey: gen new_viol_dummy=L.viol+L2.viol+L3.viol+L4.viol==0
> tspell new_viol_dummy
> replace new_viol_dummy=0 if ceil(_seq/5)!=floor(_seq/5) & _seq!=1
>
> I get:
> date gvkey viol new_viol_dummy
> 1997q1 1004 0 0
> 1997q2 1004 0 0
> 1997q3 1004 0 0
> 1997q4 1004 0 0
> 1998q1 1004 0 1
> 1998q2 1004 0 0
> 1998q3 1004 0 0
> 1998q4 1004 0 0
> 1999q1 1004 0 1
> 1999q2 1004 0 0
>
> It is important that new viol dummy can only be 1 if viol is 1 and in the previous 4 quarters viol is 0.
>
>> Date: Sun, 3 Jun 2012 22:09:08 +0200
>> Subject: Re: st: Creating a dummy variable under certain conditions
>> From: abhimanyu.arora1987@gmail.com
>> To: statalist@hsphsun2.harvard.edu
>>
>> Hi Sebas
>> On further thoughts this is an improvement again using Nick's
>>
>> bys gvkey: gen new_viol_dummy=L.viol+L2.viol+L3.viol+L4.viol==0
>> tspell new_viol_dummy
>> replace new_viol_dummy=0 if ceil(_seq/5)!=floor(_seq/5) & _seq!=1
>>
>> Cheers
>>
>> Abhimanyu
>>
>> On Sun, Jun 3, 2012 at 9:37 PM, Abhimanyu Arora
>> <abhimanyu.arora1987@gmail.com> wrote:
>> > I think this should solve Sebas' problem
>> >
>> > bys gvkey: gen new_viol_dummy=L.viol+L2.viol+L3.viol+L4.viol==0
>> >
>> > After this Nick's -tsspell- (from SSC) can come to the rescue
>> >
>> > tspell new_viol_dummy
>> >
>> > bys gvkey:replace new_viol_dummy=0 if inrange(_seq,2,4)
>> >
>> >
>> >
>> > On Sun, Jun 3, 2012 at 9:06 PM, Nick Cox <njcoxstata@gmail.com> wrote:
>> >> It makes no sense to work with L.gvkey as this just means the previous
>> >> panel identifier, which is constant within a panel . This has already
>> >> been pointed out earlier in the thread. Referring to L.gvkey was a
>> >> slip that was quickly corrected.
>> >>
>> >> Nick
>> >>
>> >> On Sun, Jun 3, 2012 at 7:59 PM, sebas nicaise <sebasnicaise@hotmail.com> wrote:
>> >>> Still not working :(
>> >>>
>> >>>
>> >>>
>> >>> When I use this :
>> >>>
>> >>> bys gvkey gen new_viol_dummy=L.gvkey+L2.gvkey+L3.gvkey+L4.gvkey==0
>> >>>
>> >>>
>> >>> bys gvkey:replace new_viol_dummy=0 if new_viol_dummy[_n-1]==1
>> >>>
>> >>>
>> >>> I get this:
>> >>>
>> >>> date gvkey viol new_viol_dummy
>> >>> 1997q1 1004 0 0
>> >>> 1997q2 1004 0 0
>> >>> 1997q3 1004 0 0
>> >>> 1997q4 1004 0 0
>> >>> 1998q1 1004 0 1
>> >>> 1998q2 1004 0 0
>> >>> 1998q3 1004 0 1
>> >>> 1998q4 1004 0 0
>> >>> 1999q1 1004 0 1
>> >>> 1999q2 1004 0 0
>> >>> 1999q3 1004 0 1
>> >>> 1999q4 1004 0 0
>> >>> 2000q1 1004 0 1
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>> Could you have another look at it :)?
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>
>> >>>> Date: Sun, 3 Jun 2012 20:24:33 +0200
>> >>>> Subject: Re: st: Creating a dummy variable under certain conditions
>> >>>> From: abhimanyu.arora1987@gmail.com
>> >>>> To: statalist@hsphsun2.harvard.edu
>> >>>>
>> >>>> Now I see, you have an additional condition, Sebastian.
>> >>>>
>> >>>> to either Nick's or my solution with L. (not the F. one)
>> >>>> -bys gvkey:replace new_viol_dummy=0 if new_viol_dummy[_n-1]==1
>> >>>>
>> >>>>
>> >>>> date gvkey viol new_viol_dummy
>> >>>> 1997q1 1004 0 0
>> >>>> 1997q2 1004 0 0
>> >>>> 1997q3 1004 0 0
>> >>>> 1997q4 1004 0 0
>> >>>> 1998q1 1004 0 0
>> >>>> 1998q2 1004 0 0
>> >>>> 1997q1 1021 0 0
>> >>>> 1997q2 1021 0 0
>> >>>> 1997q3 1021 0 0
>> >>>> 1997q4 1021 0 0
>> >>>> 1998q1 1021 0 0
>> >>>> 1998q2 1021 0 0
>> >>>> 1998q3 1021 0 0
>> >>>> 1998q4 1021 1 1
>> >>>> 1999q1 1021 1 0
>> >>>> 1999q2 1021 1 0
>> >>>> 1999q3 1021 1 0
>> >>>> 1999q4 1021 0 0
>> >>>>
>> >>>>
>> >>>>
>> >>>>
>> >>>> On Sun, Jun 3, 2012 at 7:46 PM, sebas nicaise <sebasnicaise@hotmail.com> wrote:
>> >>>>> Dear Abhimanyu,
>> >>>>>
>> >>>>> This also does not give me the right answer.It gives:
>> >>>>>
>> >>>>> date gvkey viol new_viol_dummy1998q4 1095 0 01999q1 1095 0 01999q2 1095 0 01999q3 1095 0 01999q4 1095 1 02000q1 1095 0 02000q2 1095 1 12000q3 1095 1 12000q4 1095 1 12001q1 1095 1 12001q2 1095 1 12001q3 1095 1 0
>> >>>>>
>> >>>>> It should be:000010000000
>> >>>>>
>> >>>>> I think the command should be more complicated since the new viol dummy variable is only 1 under certain strict conditions.NEWVIOL is only 1 if the VIOL dummy of the corresponding GVKEY has a 1 IF in the four previous quarters a 0. Furthermore, if NEWVIOL documents a 1, it can only be a 1 again after four quarters of 0.
>> >>>>>
>> >>>>> Hope you, or someone else, can help me with this seemingly easy task :)
>> >>>>>
>> >>>>> Thank you,Sebastiaan
>> >>>>> ----------------------------------------
>> >>>>>> Date: Sat, 2 Jun 2012 17:21:54 +0200
>> >>>>>> Subject: Re: st: Creating a dummy variable under certain conditions
>> >>>>>> From: abhimanyu.arora1987@gmail.com
>> >>>>>> To: statalist@hsphsun2.harvard.edu
>> >>>>>>
>> >>>>>> Okay, if I infer correctly, try this: bys gvkey: gen
>> >>>>>> new_viol_dummy=viol+F1.viol+F2.viol+F3.viol==4.
>> >>>>>> Seems good?
>> >>>>>>
>> >>>>>>
>> >>>>>>
>> >>>>>> On Sat, Jun 2, 2012 at 5:07 PM, sebas nicaise <sebasnicaise@hotmail.com> wrote:
>> >>>>>>> Maybe I can better show it visually:
>> >>>>>>> Your commands give me the following output:
>> >>>>>>> bys gvkey: gen new_viol_dummy=L.viol+L2.viol+L3.viol+L4.viol==0
>> >>>>>>> date gvkey viol new_viol_dummy 1997q1 1004 0 01997q2 1004 0 01997q3 1004 0 01997q4 1004 0 01998q1 1004 0 11998q2 1004 0 11997q1 1021 0 01997q2 1021 0 01997q3 1021 0 01997q4 1021 0 01998q1 1021 0 11998q2 1021 0 11998q3 1021 0 11998q4 1021 1 11999q1 1021 1 01999q2 1021 1 01999q3 1021 1 01999q4 1021 0 0
>> >>>>>>> While I want this: date gvkey viol new_viol_dummy 1997q1 1004 0 01997q2 1004 0 01997q3 1004 0 01997q4 1004 0 01998q1 1004 0 01998q2 1004 0 01997q1 1021 0 01997q2 1021 0 01997q3 1021 0 01997q4 1021 0 01998q1 1021 0 01998q2 1021 0 01998q3 1021 0 01998q4 1021 1 11999q1 1021 1 01999q2 1021 1 01999q3 1021 1 01999q4 1021 0 0
>> >>>>>>> If it is not clear what I mean, let me know :)
>> >>>>>>> Thank you,Sebastiaan
>> >>>>>>>> Date: Sat, 2 Jun 2012 16:34:41 +0200
>> >>>>>>>> Subject: Re: st: Creating a dummy variable under certain conditions
>> >>>>>>>> From: abhimanyu.arora1987@gmail.com
>> >>>>>>>> To: statalist@hsphsun2.harvard.edu
>> >>>>>>>>
>> >>>>>>>> Sebas,
>> >>>>>>>> Adding to Nick's comment, did you not -xtset- the data? You also have
>> >>>>>>>> an option of bys id_var: command, otherwise
>> >>>>>>>>
>> >>>>>>>>
>> >>>>>>>> On Sat, Jun 2, 2012 at 4:29 PM, Nick Cox <njcoxstata@gmail.com> wrote:
>> >>>>>>>>> You will probably want to exclude or ignore the first few observations
>> >>>>>>>>> in each panel or -replace- it with missing. Otherwise, what's wrong?
>> >>>>>>>>>
>> >>>>>>>>> Abhimanyu's slip has already been corrected in this thread, as below.
>> >>>>>>>>>
>> >>>>>>>>> Nick
>> >>>>>>>>>
>> >>>>>>>>> On Sat, Jun 2, 2012 at 3:12 PM, sebas nicaise <sebasnicaise@hotmail.com> wrote:
>> >>>>>>>>>
>> >>>>>>>>>> Thank you for the quick replys.
>> >>>>>>>>>>
>> >>>>>>>>>> However, the suggested solutions did not give the right answer.
>> >>>>>>>>>>
>> >>>>>>>>>> Nick, your solution gives me way too many 1's. I do think the first step with the cusum was the right way to go.
>> >>>>>>>>>>
>> >>>>>>>>>> Abhimanyu, your solution does not distinquish between the id's, the calculation has to start over for every gvkey.
>> >>>>>>>>>>
>> >>>>>>>>>>
>> >>>>>>>>>> Hope you can have another look at it, I will also try to work with the suggestions you gave me.
>> >>>>>>>>>>
>> >>>>>>>>>>> From: abhimanyu.arora1987@gmail.com
>> >>>>>>>>>>> To: statalist@hsphsun2.harvard.edu
>> >>>>>>>>>>>
>> >>>>>>>>>>> Thanks Nick, yes.
>> >>>>>>>>>>> But I would go for your solution which would be better in case he had
>> >>>>>>>>>>> a longer lag.
>> >>>>>>>>>>>
>> >>>>>>>>>>> On Sat, Jun 2, 2012 at 2:56 PM, Nick Cox <njcoxstata@gmail.com> wrote:
>> >>>>>>>>>>>> This looks OK as long as you substitute -viol- for -gvkey-. The
>> >>>>>>>>>>>> previous values concerned are for -viol-, not -gvkey-.
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> Nick
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> On Sat, Jun 2, 2012 at 1:51 PM, Abhimanyu Arora
>> >>>>>>>>>>>> <abhimanyu.arora1987@gmail.com> wrote:
>> >>>>>>>>>>>>
>> >>>>>>>>>>>>> Try this if your data is -xtset-ted
>> >>>>>>>>>>>>> gen new_viol_dummy=L.gvkey+L2.gvkey+L3.gvkey+L4.gvkey==0
>> >>>>>>>>>>>>
>> >>>>>>>>>>>>> On Sat, Jun 2, 2012 at 2:06 PM, sebas nicaise <sebasnicaise@hotmail.com>
>> >>>>>>>>>>>>>
>> >>>>>>>>>>>>>> I am struggeling with creating a new dummy variable. I have a very large
>> >>>>>>>>>>>>>> (unbalanced) panel dataset that looks like this:
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> date gvkey viol
>> >>>>>>>>>>>>>> 1997q1 1021 0
>> >>>>>>>>>>>>>> 1997q2 1021 0
>> >>>>>>>>>>>>>> 1997q3 1021 0
>> >>>>>>>>>>>>>> 1997q4 1021 0
>> >>>>>>>>>>>>>> 1998q1 1021 1
>> >>>>>>>>>>>>>> 1998q2 1021 0
>> >>>>>>>>>>>>>> 1998q3 1021 0
>> >>>>>>>>>>>>>> 1998q4 1021 1
>> >>>>>>>>>>>>>> 1999q1 1021 0
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> 1999q2 1021 0
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> 1999q3 1021 0
>> >>>>>>>>>>>>>> 1999q2 1023 0
>> >>>>>>>>>>>>>> 1999q3 1023 1
>> >>>>>>>>>>>>>> 1999q4 1023 0
>> >>>>>>>>>>>>>> 2000q1 1023 0
>> >>>>>>>>>>>>>> 2000q2 1023 1
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> Where date is calender quarter, gvkey is the company identifier and viol a
>> >>>>>>>>>>>>>> dummy which equals 1 if the firm is in violation of a covenant in a private
>> >>>>>>>>>>>>>> debt contract.
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> The problem is that I have to create a new dummy variable ( new violation)
>> >>>>>>>>>>>>>> which meets certain conditions.
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> The new violation dummy is only allowed to be 1 if in the previous 4
>> >>>>>>>>>>>>>> quarters of a certain company (gvkey) is 0.
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> This means for example that:
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> date gvkey viol Newviolation
>> >>>>>>>>>>>>>> 1997q1 1021 0 0
>> >>>>>>>>>>>>>> 1997q2 1021 0 0
>> >>>>>>>>>>>>>> 1997q3 1021 0 0
>> >>>>>>>>>>>>>> 1997q4 1021 0 0
>> >>>>>>>>>>>>>> 1998q1 1021 1 1
>> >>>>>>>>>>>>>> 1998q2 1021 0 0
>> >>>>>>>>>>>>>> 1998q3 1021 0 0
>> >>>>>>>>>>>>>> 1998q4 1021 1 0
>> >>>>>>>>>>>>>> 1999q1 1021 0 0
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> 1999q2 1021 0 0
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> 1999q3 1021 0 0
>> >>>>>>>>>>>>>> 1999q2 1023 0 0
>> >>>>>>>>>>>>>> 1999q3 1023 1 0
>> >>>>>>>>>>>>>> 1999q4 1023 0 0
>> >>>>>>>>>>>>>> 2000q1 1023 0 0
>> >>>>>>>>>>>>>> 2000q2 1023 1 0
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>>
>> >>>>>>>>>>>>>> I do not know how to tell Stata to create this variable, Hope someone is
>> >>>>>>>>>>>>>> so kind to help me.
>> >>>>>>>>>>>>
>> >>>>>>>>>>>> *
>> >>>>>>>>>>>> * For searches and help try:
>> >>>>>>>>>>>> * http://www.stata.com/help.cgi?search
>> >>>>>>>>>>>> * http://www.stata.com/support/statalist/faq
>> >>>>>>>>>>>> * http://www.ats.ucla.edu/stat/stata/
>> >>>>>>>>>>>
>> >>>>>>>>>>> *
>> >>>>>>>>>>> * For searches and help try:
>> >>>>>>>>>>> * http://www.stata.com/help.cgi?search
>> >>>>>>>>>>> * http://www.stata.com/support/statalist/faq
>> >>>>>>>>>>> * http://www.ats.ucla.edu/stat/stata/
>> >>>>>>>>>> *
>> >>>>>>>>>> * For searches and help try:
>> >>>>>>>>>> * http://www.stata.com/help.cgi?search
>> >>>>>>>>>> * http://www.stata.com/support/statalist/faq
>> >>>>>>>>>> * http://www.ats.ucla.edu/stat/stata/
>> >>>>>>>>>
>> >>>>>>>>> *
>> >>>>>>>>> * For searches and help try:
>> >>>>>>>>> * http://www.stata.com/help.cgi?search
>> >>>>>>>>> * http://www.stata.com/support/statalist/faq
>> >>>>>>>>> * http://www.ats.ucla.edu/stat/stata/
>> >>>>>>>>
>> >>>>>>>> *
>> >>>>>>>> * For searches and help try:
>> >>>>>>>> * http://www.stata.com/help.cgi?search
>> >>>>>>>> * http://www.stata.com/support/statalist/faq
>> >>>>>>>> * http://www.ats.ucla.edu/stat/stata/
>> >>>>>>>
>> >>>>>>> *
>> >>>>>>> * For searches and help try:
>> >>>>>>> * http://www.stata.com/help.cgi?search
>> >>>>>>> * http://www.stata.com/support/statalist/faq
>> >>>>>>> * http://www.ats.ucla.edu/stat/stata/
>> >>>>>>
>> >>>>>> *
>> >>>>>> * For searches and help try:
>> >>>>>> * http://www.stata.com/help.cgi?search
>> >>>>>> * http://www.stata.com/support/statalist/faq
>> >>>>>> * http://www.ats.ucla.edu/stat/stata/
>> >>>>>
>> >>>>> *
>> >>>>> * For searches and help try:
>> >>>>> * http://www.stata.com/help.cgi?search
>> >>>>> * http://www.stata.com/support/statalist/faq
>> >>>>> * http://www.ats.ucla.edu/stat/stata/
>> >>>>
>> >>>> *
>> >>>> * For searches and help try:
>> >>>> * http://www.stata.com/help.cgi?search
>> >>>> * http://www.stata.com/support/statalist/faq
>> >>>> * http://www.ats.ucla.edu/stat/stata/
>> >>> *
>> >>> * For searches and help try:
>> >>> * http://www.stata.com/help.cgi?search
>> >>> * http://www.stata.com/support/statalist/faq
>> >>> * http://www.ats.ucla.edu/stat/stata/
>> >>
>> >> *
>> >> * For searches and help try:
>> >> * http://www.stata.com/help.cgi?search
>> >> * http://www.stata.com/support/statalist/faq
>> >> * http://www.ats.ucla.edu/stat/stata/
>>
>> *
>> * For searches and help try:
>> * http://www.stata.com/help.cgi?search
>> * http://www.stata.com/support/statalist/faq
>> * http://www.ats.ucla.edu/stat/stata/
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/statalist/faq
> *   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```