Creating a new variable based on groups and based on condition

[Shehroz Jawad Khan]

Hello everyone!

I am facing a problem in STATA. I have some relevant variables: org_uuid, announced_on_2, first_funding_round_date, investment_type.
What I want to do is to create a new variable 'first_funding_round_stage' which will be equivalent to the investment_type for the first_funding_round_date for all values in a group. My data is something like:

org_uuid | announced_on_2| first_funding_round_date | investment_type

---

1111| 20130805| 20130805| seed
1111| 20130807| 20130804| crowd_funding
1122| 20150826| 20150930| series_a
1122| 20150814| 20150814| series_b
1122| 20150817| 20150812| series_c
1122| 20150714| 20150814| series_d
1133| 20150314| 20150314| pre_seed
1133| 20151017| 20151019| debt_financing
1133| 20150906| 20150905| private_equity

What I want is something like this:

org_uuid | announced_on_2| first_funding_round_date | investment_type | first_funding_round_stage

---

1111| 20130805| 20130805| seed| seed
1111| 20130805| 20130804| crowd_funding| seed
1122| 20150826| 20150930| series_a| series_b
1122| 20150814| 20150814| series_b| series_b
1122| 20150817| 20150812| series_c| series_b
1122| 20150714| 20150814| series_d| series_b
1133| 20150314| 20150314| pre_seed| pre_seed
1133| 20151017| 20151019| debt_financing| pre_seed
1133| 20150906| 20150905| private_equity| pre_seed

[Ken Chui]

So, for 1111, the chosen type is "seed" because the two date variables are equal (as in data line 1)?

[Shehroz Jawad Khan]

Bump

[Shehroz Jawad Khan]

Ken Chui, yes. It is because the two date variables are equal. Similar process is repeated for 1122 and 1133.

[Ken Chui]

clear
input org_uuid  long (announced_on_2 first_funding_round_date) str20 investment_type
1111 20130805 20130805 seed
1111 20130807 20130804 crowd_funding
1122 20150826 20150930 series_a
1122 20150814 20150814 series_b
1122 20150817 20150812 series_c
1122 20150714 20150814 series_d
1133 20150314 20150314 pre_seed
1133 20151017 20151019 debt_financing
1133 20150906 20150905 private_equity
end

gen wanted = investment_type if announced_on_2 == first_funding_round_date
gsort org_uuid -wanted
by org_uuid: replace wanted = wanted[_n-1] if missing(wanted)

list, sepby(org_uuid)

Results:

     +------------------------------------------------------------+
     | org_uuid   announ~2   first_~e   investment_t~e     wanted |
     |------------------------------------------------------------|
  1. |     1111   20130805   20130805             seed       seed |
  2. |     1111   20130807   20130804    crowd_funding       seed |
     |------------------------------------------------------------|
  3. |     1122   20150814   20150814         series_b   series_b |
  4. |     1122   20150826   20150930         series_a   series_b |
  5. |     1122   20150817   20150812         series_c   series_b |
  6. |     1122   20150714   20150814         series_d   series_b |
     |------------------------------------------------------------|
  7. |     1133   20150314   20150314         pre_seed   pre_seed |
  8. |     1133   20151017   20151019   debt_financing   pre_seed |
  9. |     1133   20150906   20150905   private_equity   pre_seed |
     +------------------------------------------------------------+

In future it'd be easier for users here if data example generated using command dataex is posted instead of in a table format. To learn more, see part 12 of the FAQ of this forum (http://www.statalist.org/forums/help).