How to replace observations with the next queue?

[Nang Widaryoko]

Dear All, would do you please to help me, how to perform this? 1. I have a database of around 6 variables and 100 observations. I would like to replace the first (only first) of movenumber with contain missing values in migstat. For example, from the following database, I would like to replace the each first observation for each ID with the next movenumber observation whose migstat value is missing. As a result, the movenumber sequence remains 1 to 16, but the data on other variables (year, dest, origin, migstat) shifts up to compensate for the missing in the first row

ID	movenumber	year	dest	origin	migstat
1	1	1935	1201	1151	.
1	2	1936	1201	1151	1
1	3	1938	1222	1151	1
1	4	1940	1432	1151	1
1	5
1	6
1	7
1	8
1	9
1	10
1	11
1	12
1	13
1	14
1	15
1	16

2. How to delete all of variables (ID,year,dest,origin,migstat) if it contains only 1 movenumber and migstat is missing Thankyou in advance...

[Carlo Lazzaro]

Nang:
welcome to this forum.
I do not follow on your 1st question, as it seems to conflict with your 2nd one I reply to below:

. bysort ID: drop if _n==1

[Nang Widaryoko]

Thank you very much Mr. Carlo
Well, I want to replace all observations of these variables (year, dest, origin, migstat) in the first row (because it contains missing values in migstat) with observations in the row 2 to 4 (subsequent observations). So...for movenumber 1 it becomes 1936 (year), 1201 (dest), 1151 (origin) and 1 (migstat) and so on..

[Carlo Lazzaro]

Nang:
you may want to try:

bysort ID: drop if _n==1
bysort ID: replace movenumber=movenumber-1

[Nang Widaryoko]

Thank you for your respon, but it isn't work yet

[Carlo Lazzaro]

Nang:
what does it mean "it isn't working yet"?
Please describe. Thanks.

[Nang Widaryoko]

I get all of the observations in the first row deleted (movenumber, year, dest, origin, migstat). As a result, the movenumber now begins at 2, rather than 1.
Meanwhile, I'd like to keep the move numbers from 1 to 16 as they are. Could I describe my desired output in the table below? Thank you very much Sir

ID	movenumber	year	dest	origin	migstat
	1	1936	1201	1551	1
	2	1938	1222	1551	1
	3	1940	1432	1551	1
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16

[Hemanshu Kumar]

Perhaps this, then?

sort ID movenumber
by ID: drop if _n == 1
by ID: replace movenumber = movenumber-1
expand 2 if movenumber == 15, gen(new)
replace movenumber = 16 if new
foreach var of varlist year-migstat {
    replace `var' = . if new
}
sort ID movenumber
drop new

[Clyde Schechter]

I'm not entirely sure I understand what you want to do. It seems an unusual thing to do and I don't understand the rationale for it. But I believe this code will accomplish it:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(id movenumber) int(year dest origin) byte migstat
1  1 1935 1201 1151 .
1  2 1936 1201 1151 1
1  3 1938 1222 1151 1
1  4 1940 1432 1151 1
1  5    .    .    . .
1  6    .    .    . .
1  7    .    .    . .
1  8    .    .    . .
1  9    .    .    . .
1 10    .    .    . .
1 11    .    .    . .
1 12    .    .    . .
1 13    .    .    . .
1 14    .    .    . .
1 15    .    .    . .
1 16    .    .    . .
end

frame put id migstat, into(working)
frame working {
    drop if missing(migstat)
    sort id, stable
    gen movenumber = _n
}

frlink 1:1 id movenumber, frame(working)
drop migstat
frget migstat, from(working)
frame drop working
drop working

How to delete all of variables (ID,year,dest,origin,migstat) if it contains only 1 movenumber and migstat is missing

by id (movenumber), sort: drop if _N == 1 & missing(migstat)

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

[Nang Widaryoko]

Thank you for your response Kumar...but it still does not work...it only replace the movenumber and does not shift the observations of the other variables to replace the missing values

[Hemanshu Kumar]

Nang Widaryoko I had edited the code I initially posted. Could you check the code as it is now and let me know if that solves your problem?

[Nang Widaryoko]

Great..., thank you very much Mr. Clyde....its works. And I'm sorry not using dataex in this forum...

[Nang Widaryoko]

Hemanshu Kumar thank you very much for your kind respons