[Richie Oyeleke]

I would suggest the following code to overcome the difficulties introduced by having multiple observations with the same value for fruits.

split fruits , parse(",") gen(last)
generate seq = _n
reshape long last , i(seq) j(index)
bys seq (index) : drop if mi(last) | last == last[_n-1]
bys seq (index) : keep if _n == _N
drop seq
list, clean

. list, clean

index fruits last
1. 1 nuts nuts
2. 4 orange, banana, melon, cocoa, cocoa cocoa
3. 4 orange,cocoa, banana, orange, orange orange
4. 4 orange,cocoa, banana, orange, orange orange
5. 4 orange,cocoa, banana, orange, orange orange
6. 4 orange,cocoa, banana, orange, orange orange
7. 4 orange,cocoa, banana, orange, orange orange

Thanks and I appreciate you!

[Richie Oyeleke]

Thank you very much. Your solution is sleek and works perfect!

Bjarte Aagnes William Lisowski Is there a way to keep all the original observations without dropping or loosing them. since these two lines of code deletes some of my observation data but I do not want to lose any obsersations. Any help with this?
bys fruits (index) : drop if mi(last) | last == last[_n-1] bys fruits (index) : keep if _n == _N

[Bjarte Aagnes]

Starting with original data:

gen sorder = _n

preserve

    bysort fruits : keep if ( _n == 1 )
    split fruits , parse(",") gen(last)
    reshape long last , i(fruits) j(index)
    bys fruits (index) : drop if mi(last) | last == last[_n-1]
    bys fruits (index) : keep if _n == _N

    tempfile fruits
    save `fruits'

restore

merge m:1 fruits using `fruits'

sort sorder
drop sorder

[Bjarte Aagnes]

Alternative:

********************************************************************************

split fruits , parse(",") gen(last) 

ds last*

foreach v in `=strreverse("`r(varlist)'")' {
            
        local varname = strreverse("`v'")
    
        if ( "`prev'" != "" ) {
            
            replace `prev' = "" if ( trim(`varname') == trim(`prev') )
        }
        
        local prev = "`varname'"
}

gen last  = ""
gen index = . 

foreach v in `r(varlist)' {
            
        replace last  = `v' if !mi(`v') 
        replace index = real(subinstr("`v'", "last", "", 1)) if !mi(`v') 
        drop `v'
}

********************************************************************************

[Richie Oyeleke]

Alternative:

********************************************************************************

split fruits , parse(",") gen(last)

ds last*

foreach v in `=strreverse("`r(varlist)'")' {

local varname = strreverse("`v'")

if ( "`prev'" != "" ) {

replace `prev' = "" if ( trim(`varname') == trim(`prev') )
}

local prev = "`varname'"
}

gen last = ""
gen index = .

foreach v in `r(varlist)' {

replace last = `v' if !mi(`v')
replace index = real(subinstr("`v'", "last", "", 1)) if !mi(`v')
drop `v'
}

********************************************************************************

Bjarte Aagnes Thank you for these solutions, exactly what I was looking for. Apologies for my late response, I got caught up with another project!