shifting observations in Stata

Question

I want to shift observations to the left if there are missing values. For instance, if I have

num1 num2 num3 num4 num5 num6
1    2    .    2    4    .
.    .    .    3    4    4
.    .    3    4    1    .
.    2    .    2    1    .

I want to shift them over:

num1 num2 num3 num4 num5 num6
1    2    2    4    .    .
3    4    4    .    .    .
3    4    1    .    .    .
2    2    1    .    .    .

I don't want to reshape the data. The data I have is too large.

Nick Cox Nick Cox · Accepted Answer · 2018-11-04T19:19:13

This works for your example:

clear
input num1 num2 num3 num4 num5 num6
1    2    .    2    4    .
.    .    .    3    4    4
.    .    3    4    1    .
.    2    .    2    1    .
end 

egen all = concat(num*) 
replace all = subinstr(all, ".", "", .) 
compress all 

count if all != "" 
local j = 1 
quietly while r(N) > 0 { 
    gen NUM`j' = real( substr(all, 1, 1) )
    replace all = substr(all, 2, .) 
    local ++j
    count if all != "" 
} 

drop all 
list num* NUM*  
    
     +---------------------------------------------------------------------+
     | num1   num2   num3   num4   num5   num6   NUM1   NUM2   NUM3   NUM4 |
     |---------------------------------------------------------------------|
  1. |    1      2      .      2      4      .      1      2      2      4 |
  2. |    .      .      .      3      4      4      3      4      4        |
  3. |    .      .      3      4      1      .      3      4      1        |
  4. |    .      2      .      2      1      .      2      2      1        |
     +---------------------------------------------------------------------+

EDIT: That creates new variables alongside the old. It's then up to you to decide whether to drop the old and rename the new.

Beyond your example, the thread raises various questions, including

Whether your data layout (structure or format, some say) is good for your purposes. If your dataset is really panel or longitudinal data, for example, then a long layout is generally preferable in Stata. For that, you need reshape long.
A comparison in terms of memory and speed of this method and one based on reshape long in the first instance (and a final reshape wide if the original layout is deemed essential). A comparison might feature the OP's dataset (and others of similar form but different size, as much of the point of posting here is that other people may have similar problems).

People seem to complain often about the speed of reshape, but speculation and gossip aside, evidence would be of interest.

EDIT 30 March 2020 This is a better solution, I suggest.

clear
input num1 num2 num3 num4 num5 num6
1    2    .    2    4    .
.    .    .    3    4    4
.    .    3    4    1    .
.    2    .    2    1    .
end 

egen NUM = concat(num?), p(" ")
replace NUM = subinstr(NUM, ".", "", .)
split NUM, destring 

list NUM? 

     +---------------------------+
     | NUM1   NUM2   NUM3   NUM4 |
     |---------------------------|
  1. |    1      2      2      4 |
  2. |    3      4      4      . |
  3. |    3      4      1      . |
  4. |    2      2      1      . |
     +---------------------------+

shifting observations in Stata

1 Answers