Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.

# Re: st: How can I get the second last non-missing value?

 From Sergiy Radyakin <[email protected]> To "[email protected]" <[email protected]> Subject Re: st: How can I get the second last non-missing value? Date Wed, 12 Jun 2013 18:31:01 -0400

```Besides loosing a variable it is also quite slow because of 2 reshapes
(and perhaps you have other variables in the data as well). I'd go to
Mata with this one, it is about 10 times faster and can probably still
be optimized:
r; t=1.72 18:25:22 vs r; t=16.88 18:25:39
Best, Sergiy

*** begin example ***
clear all

input id v1 v2 v3 v4 v5
1 1  2  3  .  .
2 1  2  .  .  .
3 1  2  3  4  .
end

local exp=300000

expand `exp'

mata

void prelast() {
V=.
st_view(V,.,st_local("varlist"))
R=.
st_view(R,.,st_local("result"))

for(i=1;i<=rows(V);i++) {

for(j=0;j<cols(V);j++) {
if (missing(V[i,cols(V)-j])==0) {
// found last non-missing

if (cols(V)-j-1<1) break;  //nothing before

for(k=cols(V)-j-1;k>=1;k--) {
if (missing(V[i,k])==0)
R[i,1]=V[i,k]
break;
}

break;
}
}
}
}

end

program define sergiy
syntax varlist, result(string)

quietly generate double `result'=.
mata prelast()
end

program define rebecca
quietly reshape long v, i(id) j(num)
keep if ! missing(v)
bys id (num): gen v6 = v[_N-1]
quietly reshape wide v, i(id) j(num)
end

set rmsg on
sergiy v1 v2 v3 v4 v5, result(r)
set rmsg off

clear

input id v1 v2 v3 v4 v5
1 1  2  3  .  .
2 1  2  .  .  .
3 1  2  3  4  .
end

expand `exp'
replace id=_n

set rmsg on
rebecca
set rmsg off

On Wed, Jun 12, 2013 at 5:01 PM, Rebecca Pope <[email protected]> wrote:
> Duygu,
> I think this will be easier with your data in long form.
>
> *** begin example ***
> clear
> input id v1 v2 v3 v4 v5
>   1 1  2  3  .  .
>   2 1  2  .  .  .
>   3 1  2  3  4  .
>  end
>
> reshape long v, i(id) j(num)
> keep if ! missing(v)
> bys id (num): gen v6 = v[_N-1]
> list, noobs clean
> reshape wide v, i(id) j(num)
> list, noobs clean
>
> *** end ***
>
> In this case, you lose v5, but presumably in your real data that
> variable is not universally missing. If you don't have a constant
> prefix for the variables in your dataset, you might want to check out
> -rename- and look at the methods for renaming groups. Standing advice:
> test this on a copy of your data rather than the original data.
>
> Regards,
> Rebecca
>
> On Wed, Jun 12, 2013 at 3:27 PM, duygu yıldırım <[email protected]> wrote:
>> Hello everyone,
>>
>> I use a cross-sectional individual level data set. I want to ask if there is a way that I can create a variable which consists the second last non-missing value within observations?
>>
>> for example;
>>
>> id v1 v2 v3 v4 v5
>>  1 1  2  3  .  .
>>  2 1  2  .  .  .
>>  3 1  2  3  4  .
>>
>> So the variable that I want to create is;
>>
>> id  v6
>> 1   2
>> 2   1
>> 3   3
>>
>> I can get the last non-missing value within observation by using the rowlast command, but I also need the one just before the last one.
>>
>> Thank you all very much for any help,
>>
>> Duygu.
>>
>> *
>> *   For searches and help try:
>> *   http://www.stata.com/help.cgi?search
>> *   http://www.stata.com/support/faqs/resources/statalist-faq/
>> *   http://www.ats.ucla.edu/stat/stata/
>
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/
```