generate a value indicating how many time a situation happens before the specific date

[Xion Lin]

Hello everyone in the community! Sorry to have some problems with the same data again : ( , thanks again, in advance, for all your help!

This is the previous post and I think I just need to build the dummy before, but I figure out I also need to build the number variable : (
https://www.statalist.org/forums/for...-specific-date

A project (identified by pid) may submit its business plan to investors with different status (identified by investor_h_position: dummy, 1 for high-status) multiple times (each different submission is identified by v1), and (the submission date is marked by bp_submission_date).

I would like to create a variable high_position_before_num, which divides the submissions with the same pid into a group, and identifies that, in each submissions, how many times the project has been submitted to high-status investor before this submission date.

Since I've found difficult to deal with the date data, I hope that I could get suggestions and guidance from you. Thanks in advance!

Here is my sample data & code.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(v1 pid) str10 bp_submission_date float investor_h_position
59536 10134 "2016-07-14" 1
59908 10134 "2016-07-03" 0
59866 10134 "2016-06-27" 0
7782 10134 "2016-04-26" 1
59929 10134 "2016-08-01" 1
59861 10134 "2016-06-25" 0
60190 10134 "2016-07-29" 1
62622 10134 "2017-03-22" 1
60196 10134 "2016-08-28" 0
59943 10134 "2016-07-12" 0
62228 10137 "2016-12-28" 1
62230 10137 "2017-01-27" 1
62229 10137 "2016-12-30" 1
62231 10137 "2017-01-27" 1
62232 10137 "2017-01-27" 0
62234 10137 "2017-01-27" 1
62403 10137 "2017-02-16" 0
62233 10137 "2017-01-27" 1
53934 10155 "2016-01-29" 1
53942 10155 "2016-01-19" 1
53935 10155 "2016-01-19" 1
54917 10155 "2016-02-17" 1
53936 10155 "2016-01-21" 1
53938 10155 "2016-04-26" 1
62525 10155 "2017-03-16" 1
53937 10155 "2016-04-26" 1
59078 10157 "2016-06-15" 1
61380 10159 "2016-10-31" 0
62738 10161 "2017-04-05" 0
62739 10161 "2017-04-06" 1
60493 10162 "2016-09-10" 0
60559 10162 "2016-09-17" 1
60503 10162 "2016-09-10" 0
60487 10162 "2016-08-12" 1
60542 10162 "2016-09-17" 0
59237 10162 "2016-05-31" 0
60546 10162 "2016-09-17" 1
60502 10162 "2016-09-10" 1
60499 10162 "2016-09-10" 0
59227 10162 "2016-06-01" 0
56144 10162 "2016-03-06" 0
56186 10162 "2016-03-08" 0
60563 10162 "2016-09-17" 1
56143 10162 "2016-03-11" 1
56140 10162 "2016-03-06" 1
56182 10162 "2016-04-26" 1
60508 10162 "2016-09-10" 0
60488 10162 "2016-08-11" 0
60506 10162 "2016-09-10" 0
60548 10162 "2016-09-17" 0
60498 10162 "2016-08-29" 0
56184 10162 "2016-04-26" 0
60507 10162 "2016-09-10" 0
56162 10162 "2016-04-26" 1
56146 10162 "2016-03-07" 0
60565 10162 "2016-09-17" 0
56181 10162 "2016-04-26" 1
56151 10162 "2016-04-16" 1
56157 10162 "2016-03-07" 0
56177 10162 "2016-04-15" 0
59229 10162 "2016-06-12" 1
60485 10162 "2016-08-11" 1
59232 10162 "2016-06-28" 0
56171 10162 "2016-03-13" 0
56164 10162 "2016-04-26" 1
60564 10162 "2016-09-17" 0
60505 10162 "2016-09-10" 1
60495 10162 "2016-09-10" 0
56153 10162 "2016-04-26" 0
60504 10162 "2016-09-10" 1
60551 10162 "2016-09-17" 1
60555 10162 "2016-09-17" 1
60484 10162 "2016-08-11" 0
60501 10162 "2016-09-10" 1
56190 10162 "2016-04-26" 0
56148 10162 "2016-04-26" 1
56169 10162 "2016-04-26" 1
59236 10162 "2016-06-09" 1
56141 10162 "2016-03-06" 1
56189 10162 "2016-04-26" 1
56142 10162 "2016-03-09" 1
56166 10162 "2016-04-26" 1
56172 10162 "2016-04-26" 1
56154 10162 "2016-03-18" 1
60544 10162 "2016-09-17" 1
59235 10162 "2016-06-28" 1
56147 10162 "2016-03-08" 1
59238 10162 "2016-05-31" 0
56188 10162 "2016-04-26" 0
56139 10162 "2016-03-06" 1
56187 10162 "2016-03-06" 1
60483 10162 "2016-08-11" 0
56150 10162 "2016-03-10" 0
56145 10162 "2016-03-07" 0
60494 10162 "2016-09-10" 0
60550 10162 "2016-09-17" 0
56167 10162 "2016-04-26" 1
60489 10162 "2016-08-16" 1
60545 10162 "2016-09-17" 1
59240 10162 "2016-06-28" 0
end

[Clyde Schechter]

//  FIRST CREATE A REAL STATA INTERNAL FORMAT DATE VARIABLE
gen date = daily(bp_submission_date, "YMD"), after(bp_submission_date)
assert missing(date) == missing(bp_submission_date)
format date %td
drop bp_submission_date
rename date bp_submission_date

//  CALCULATE NUMBER OF TIMES PROJECT SUBMITTED TO HIGH-STATUS INVESTOR
//  BEFORE CURRENT SUBMISSION DATE
assert inlist(investor_h_position, 0, 1)
by pid (bp_submission_date), sort: gen wanted = sum(investor_h_position[_n-1])

[Hemanshu Kumar]

Xion Lin perhaps you didn't look carefully at the solution I had provided in the last thread. In it, I create a variable called high_status_sum which has exactly what you need. In that post, I had just dropped the variable at the end because you didn't say you needed it.

[Xion Lin]

Xion Lin perhaps you didn't look carefully at the solution I had provided in the last thread. In it, I create a variable called high_status_sum which has exactly what you need. In that post, I had just dropped the variable at the end because you didn't say you needed it.

Hi! Millions of thanks and apologize for my might impolite expression. Actually I have run the code you offered, but found that if a businnes plan is submitted to high-status investor for the first time, then on that specific date, only one submission will be marked as 0 and others are all 1, which should all be 0 to realize my proposed function.

Anyway, thank you for your time and effort! I really have learned a lot : )