Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Generate new variable based on several conditions

    Hi everyone,

    I have a Problem concerning the following dataset:


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float Date str19 Action str12 Name float Value
    19348 "buy"  "1"  3.272727
    19381 ""     "1"  3.636909
    19439 "sell" "1"  3.409091
    19530 ""     "1" 3.3163636
    19544 "sell" "1"  3.231818
    19563 ""     "1"  3.661818
    19348 "buy"  "2" 2.4194546
    19381 ""     "2"  2.448636
    19439 "sell" "2" 2.9858184
    19530 "buy"  "2"  3.305909
    19696 ""     "2"      3.93
    19725 "sell" "2" 4.2650003
    20275 "buy"  "2"  4.828182
    20402 ""     "2"  6.074545
    20471 "buy"  "2"  6.119091
    end
    format %td Date

    I want to create a table that looks the following:


    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float Date str19 Action str12 Name float(Value sell_price Buy_Again)
    19348 "buy"  "1"  3.272727         . .
    19381 ""     "1"  3.636909         . .
    19439 "sell" "1"  3.409091         . .
    19530 ""     "1" 3.3163636         . .
    19544 "sell" "1"  3.231818         . .
    19563 ""     "1"  3.661818         . .
    19348 "buy"  "2" 2.4194546         . .
    19381 ""     "2"  2.448636         . .
    19439 "sell" "2" 2.9858184         . .
    19530 "buy"  "2"  3.305909 2.9858184 1
    19696 ""     "2"      3.93         . 1
    19725 "sell" "2" 4.2650003         . .
    20275 "buy"  "2"  4.828182 4.2650003 1
    20402 ""     "2"  6.074545         . 1
    20471 "buy"  "2"  6.119091 4.2650003 1
    end
    format %td Date
    In principle I want to create two new variables.

    The first variable sell_price shows the last "Value" when Action=sell. What is important (and for me difficult to code) that it only shows the value where Action=buy for the second time in each Name. As you can see for Name=1 there is Action=buy only once. Thus, no change needs to be made to the variable. For Name=2 there is Action=buy appearing multiple times. For each time except for the first time I would like to have there the Value for Action=sell.

    The second variable is supposed to be Buy_Again=1 from the second time on there is Action=buy until the next time there is Action=sell for each name. For the third, fourth (… etc.) time that there is Action=buy this system is supposed to continue

    I hope you could understand what I wanted to explain. It would be great if anyone could help me on that.

    Thanks a lot!


  • #2
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float Date str19 Action str12 Name float Value
    19348 "buy"  "1"  3.272727
    19381 ""     "1"  3.636909
    19439 "sell" "1"  3.409091
    19530 ""     "1" 3.3163636
    19544 "sell" "1"  3.231818
    19563 ""     "1"  3.661818
    19348 "buy"  "2" 2.4194546
    19381 ""     "2"  2.448636
    19439 "sell" "2" 2.9858184
    19530 "buy"  "2"  3.305909
    19696 ""     "2"      3.93
    19725 "sell" "2" 4.2650003
    20275 "buy"  "2"  4.828182
    20402 ""     "2"  6.074545
    20471 "buy"  "2"  6.119091
    end
    format %td Date
    
    by Name (Date), sort: gen num_buys = sum(Action== "buy")
    gen buy_again = (num_buys > 1) & (Action != "sell")
    
    by Name (Date): gen sell_price = cond(Action == "sell", Value, .) if _n == 1
    by Name (Date): replace sell_price = ///
        cond(Action == "sell", Value, sell_price[_n-1]) if _n > 1
    replace sell_price = . if (num_buys < 2) | (Action != "buy")
    Added: My code differs slightly from what you requested. I made the variable buy_again a 0/1 variable rather than a ./1 variable. Coding variables as ./1 is legal, but it's not a good programming practice in Stata and it is a real setup for errors down the road. If you really want it as 0/1 you can go ahead and replace the 0's with missings, but then you are solely responsible for all the traps you fall into down the road with that.
    Last edited by Clyde Schechter; 12 Oct 2017, 17:49.

    Comment


    • #3
      Thank you very much!

      Comment

      Working...
      X