Hi,
I have a wide dataset (about 5000 observations) containing data on age and marital status from the years 2014-2019 – (I was only able to include 2014-2017 due to the limit of dataex though). However, not everyone has data for all four (in reality, six) years. Specifically, many age and marital status values are left-justified (so to speak) and don’t actually correspond to their ‘correct’ column. As an example, ID “PLMIB” has data for year 2016 only, but their respective yearly age and marital status values appear in column 2014. I need to transfer/shift their age (84) and marital status (Widowed) values, which are currently in the 2014 column, to the correct 2016 columns where they belong. However, I need to do this for every age and marital status value that is in the incorrect column. I included a short example of the data since visually looking at it might be easier to understand.

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 ID int(Census_2014 Census_2015 Census_2016 Census_2017 Age_Census_2014 Age_Census_2015 Age_Census_2016 Age_Census_2017) str14(Marital_Status_Census_2014 Marital_Status_Census_2015 Marital_Status_Census_2016 Marital_Status_Census_2017)
"URHDJ" 2014 2015 2016 2017 62 63 64 65 "Married" "Married" "Married" "Married"
"MKLRD" 2014 2015 2016 2017 58 59 60 61 "Married" "Married" "Married" "Married"
"BCGDT"    . 2015 2016 2017 53 54 55  . "Married" "Widowed" "Married" ""      
"AQWSD"    . 2015 2016 2017 46 47 48  . ""        ""        ""        ""      
"ZFHKA"    . 2015 2016 2017 47 48 49  . ""        ""        ""        ""      
"TYRHF"    . 2015 2016 2017 60 61 62  . ""        ""        ""        ""      
"WRYUT" 2014    . 2016 2017 52 53 54  . "Married" "Married" "Married" ""      
"BHNDF" 2014    . 2016 2017 51 52 53  . "Married" "Married" "Widowed" ""      
"AWESR" 2014 2015    . 2017 60 61 62  . ""        ""        ""        ""      
"MCNFR" 2014 2015    . 2017 54 55 57  . "Married" "Married" "Married" ""      
"HNJGB" 2014 2015    . 2017 58 59 60  . "Widowed" "Widowed" "Widowed" ""      
"MNBVC" 2014    .    . 2017 52 53  .  . ""        ""        ""        ""      
"VBCNF"    .    .    . 2017 76  .  .  . "Widowed" ""        ""        ""       
"XCDVS" 2014 2015 2016    . 52 53 54  . ""        ""        ""        ""      
"IOPUT" 2014 2015 2016    . 44 45 46  . "Married" "Married" "Married" ""      
"NMDRF" 2014 2015 2016    . 56 57 58  . "Married" "Married" "Widowed" ""      
"LKDLF" 2014 2015 2016    . 85 86 87  . "Married" "Married" "Married" ""      
"TYRUE"    . 2015 2016    . 81 82  .  . "Widowed" "Widowed" ""        ""      
"SRIUO"    . 2015 2016    . 57 58  .  . ""        ""        ""        ""      
"FCRVT"    .    . 2016    . 60  .  .  . "Widowed" ""        ""        ""      
"PLMIB"    .    . 2016    . 84  .  .  . "Widowed" ""        ""        ""      
"YHNBG" 2014 2015    .    . 43 44  .  . ""        ""        ""        ""      
"LIKMD" 2014 2015    .    . 44 45  .  . ""        ""        ""        ""      
"DDERF"    . 2015    .    . 65  .  .  . "Married" ""        ""        ""      
"VGVBF" 2014    .    .    . 71  .  .  . ""        ""        ""        ""      
"ASDFC" 2014    .    .    . 50  .  .  . ""        ""        ""        ""      
end
Essentially, I need to shift all of the age and marital status values to their correct column so they ‘line up’ according to whichever years a respective ID/person has data on. I was trying to do some sort of loop(s) that individually shifted incorrect values one cell to the right until they reached the correct location. However, my issue was twofold: 1. how to account for the different types of ‘shifts’ (1 cell to the right, 2 cells to the right, etc.) in the loop, and 2. How to code a break so the loop doesn’t shift cells when it doesn’t need to.

Code:
levelsof ID, local(Key)             // For 1 cell shifts
foreach p of local Key {                                                  
local c 6          
forvalues j = 7(-1)4 {
capture replace Age_Census_201`j' = Age_Census_201`c' if Census_201`j' != . & ID == "`p'"
capture replace Age_Census_201`j' = . Census_201`j' == . & ID == "`p'"
local `c--'            
if (Year`j' != . & Year`c' == .) {
continue
}
}
}
 
levelsof ID, local(Key)             // For 2 cell shifts
foreach p of local Key {                                                  
local c 5          
forvalues j = 7(-1)4 {
capture replace Age_Census_201`j' = Age_Census_201`c' if Census_201`j' != . & ID == "`p'"
capture replace Age_Census_201`j' = . Census_201`j' == . & ID == "`p'"
local `c--'            
if (Year`j' != . & Year`c' == .) {
continue
}
}
}
David