CODEWITHSUNDEEP
stata
Adrian
Adrian

Reputation: 68

Counting occurrences where current value is equal to or less than previous 90 day values

I have a daily transactional dataset with gaps. I want to see whether a product with a reference price websiteprice of $X on date T had an actual sold price actualsoldprice $Y >= $X for at least 10% of previous T – 90 days. In other words, for each transaction where sale_at_or_above_refprice == 1, we need to count how many times the actual sold price for prior transactions (of a given product) within the previous 90 days met or exceeded that transaction's reference price.

I have included first step results that I am looking for in the wanted variable.

My data is as follows,

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 orderdate str16 productcode str10 productcategory byte(websiteprice actualsoldprice sale_at_or_above_refprice var7 wanted)
"3-Jan-20"  "MZZ32819-564-282" "Mens Jeans" 40 25 . .  .
"8-Jan-20"  "MZZ32819-564-282" "Mens Jeans" 40 40 1 .  .
"12-Jan-20" "MZZ32819-564-282" "Mens Jeans" 40 40 1 .  1
"12-Sep-20" "MZZ32819-564-282" "Mens Jeans" 40 28 . .  .
"18-Sep-20" "MZZ32819-564-282" "Mens Jeans" 40 24 . .  .
"20-Sep-20" "MZZ32819-564-282" "Mens Jeans" 50 30 . .  .
"27-Sep-20" "MZZ32819-564-282" "Mens Jeans" 50 25 . .  .
"11-Oct-20" "MZZ32819-564-282" "Mens Jeans" 40 20 . .  .
"19-Oct-20" "MZZ32819-564-282" "Mens Jeans" 35 24 . .  .
"2-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 20 20 1 .  6
"2-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 14 14 1 .  7
"4-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 14 14 1 .  8
"7-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 14 14 1 .  9
"7-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 20 20 1 .  7
"9-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 20 20 1 .  8
"11-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 . 12
"12-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 . 13
"14-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 . 14
"15-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 . 15
"18-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 . 16
"24-Nov-20" "MZZ32819-564-282" "Mens Jeans" 20 20 1 .  9
end

EDIT - I have updated the wanted variable and include a new_wanted. The difference is taking into account repeated dates with multiple prices. Also including 2 products to run this process by id.

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 orderdate str16 productcode str10 productcategory byte(websiteprice actualsoldprice sale_at_or_above_refprice wanted new_wanted)
"3-Jan-20"  "MZZ32819-564-282" "Mens Jeans" 40 25 .  .  .
"8-Jan-20"  "MZZ32819-564-282" "Mens Jeans" 40 40 1  0  0
"12-Jan-20" "MZZ32819-564-282" "Mens Jeans" 40 40 1  1  1
"12-Sep-20" "MZZ32819-564-282" "Mens Jeans" 40 28 .  .  .
"18-Sep-20" "MZZ32819-564-282" "Mens Jeans" 40 24 .  .  .
"20-Sep-20" "MZZ32819-564-282" "Mens Jeans" 50 30 .  .  .
"27-Sep-20" "MZZ32819-564-282" "Mens Jeans" 50 25 .  .  .
"11-Oct-20" "MZZ32819-564-282" "Mens Jeans" 40 20 .  .  .
"19-Oct-20" "MZZ32819-564-282" "Mens Jeans" 35 24 .  .  .
"2-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 20 20 1  6  6
"2-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 14 14 1  6  6
"4-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 14 14 1  8  7
"7-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 14 14 1  9  8
"7-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 20 20 1  7  7
"9-Nov-20"  "MZZ32819-564-282" "Mens Jeans" 20 20 1  8  9
"11-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 12 10
"12-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 13 11
"14-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 14 12
"15-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 15 13
"18-Nov-20" "MZZ32819-564-282" "Mens Jeans" 14 14 1 16 14
"24-Nov-20" "MZZ32819-564-282" "Mens Jeans" 20 20 1  9  9
"6-Jan-20"  "ADDZ4449-524-645" "Mens Bags"  60 50 .  .  .
"11-Jan-20" "ADDZ4449-524-645" "Mens Bags"  70 60 .  .  .
"12-Feb-20" "ADDZ4449-524-645" "Mens Bags"  60 60 1  .  1
"12-Jul-20" "ADDZ4449-524-645" "Mens Bags"  60 50 .  .  .
"18-Sep-20" "ADDZ4449-524-645" "Mens Bags"  50 55 1  .  1
"20-Sep-20" "ADDZ4449-524-645" "Mens Bags"  50 45 .  .  .
"20-Sep-20" "ADDZ4449-524-645" "Mens Bags"  66 45 .  .  .
"12-Oct-20" "ADDZ4449-524-645" "Mens Bags"  55 60 1  .  1
"19-Oct-20" "ADDZ4449-524-645" "Mens Bags"  60 60 1  .  1
"2-Nov-20"  "ADDZ4449-524-645" "Mens Bags"  70 73 1  .  0
"2-Nov-20"  "ADDZ4449-524-645" "Mens Bags"  60 56 .  .  .
"4-Nov-20"  "ADDZ4449-524-645" "Mens Bags"  60 60 1  .  3
"7-Nov-20"  "ADDZ4449-524-645" "Mens Bags"  50 45 .  .  .
"7-Nov-20"  "ADDZ4449-524-645" "Mens Bags"  66 66 1  .  1
"9-Nov-20"  "ADDZ4449-524-645" "Mens Bags"  60 56 .  .  .
"11-Nov-20" "ADDZ4449-524-645" "Mens Bags"  60 76 1  .  5
"12-Nov-20" "ADDZ4449-524-645" "Mens Bags"  60 71 1  .  6
"13-Nov-20" "ADDZ4449-524-645" "Mens Bags"  60 26 .  .  .
"15-Nov-20" "ADDZ4449-524-645" "Mens Bags"  65 70 1  .  4
"15-Nov-20" "ADDZ4449-524-645" "Mens Bags"  67 70 1  .  3
"22-Nov-20" "ADDZ4449-524-645" "Mens Bags"  56 70 1  .  9
end

Below is the code that I am trying to adapt for this task. Credit to Ken Chui from STATALIST.

gen date1 = date(orderdate, "DMY", 2020)
format date1 %td

local max = _N
gen wanted2 = .
foreach x of numlist 1/`max'{
    capture drop get get_sum
    gen get = actualsoldprice >= actualsoldprice[`x']
    rangestat (sum) get, interval(date -90 -1)
    replace wanted2 = get_sum if _n == `x'
}
replace wanted2 = . if sale_at_or_above_refprice == .

Upvotes: 1

Views: 496

Answers (1)

TheIceBear
TheIceBear

Reputation: 3255

*Start by converting date to Stata date
gen stata_date = date(orderdate,"DM20Y")
format stata_date %td

*Sort data and product code as stop conditions in while loop expect them to be sorted
sort productcode stata_date

*Create varialbe to store result
gen count_less = .

*Loop over all rows
count 
forvalue row = 1/`r(N)' {
    
    *Only applicable to 
    if sale_at_or_above_refprice[`row'] == 1 {
        
        *Set result variable to 0 for this row
        replace count_less = 0 if _n == `row'
        
        *Initate locals used in while loop
        local true = 1
        local row_skip = 1
        local count = 0
        local last_date = stata_date[`row']
        
        *Loop until any stop condition sets local true to 0
        while `true' == 1 {
           
            *Test if row_skip hits top of data set (i.e row 0)
            if `row'-`row_skip' == 0                                        local true = 0
            *Test that product is same in compare row
            else if productcode[`row'] != productcode[`row'-`row_skip']     local true = 0
            *Test that previous order is within 90 days
            else if stata_date[`row'] - stata_date[`row'-`row_skip'] > 90   local true = 0

            *Test if actualsoldprice is less thatn old websiteprice
            else if websiteprice[`row'] <= actualsoldprice[`row'-`row_skip'] {
                
                * Each date can only be counted once, so test if date is last date counted 
                if `last_date' != stata_date[`row'-`row_skip'] {
                    *Compare row fits condition, add 1 to counter
                    local count = `count' + 1   
                    
                    *Update last counted date
                    local last_date = stata_date[`row'-`row_skip']
                }
            }
            *Skip one more prevuous row
            local row_skip = `row_skip' + 1
        }
        *Add the count result to the result varaible for this row
        replace count_less = `count' if _n == `row'
    }
}

Upvotes: 2

Related Questions