I am analyzing data from a cohort study of pregnant women. Study subjects were recruited over several months, and data were collected on them at monthly visits. There were, in total, 12 data collection visits in the study. Study subjects had data collected several times until they delivered their baby and were finished with the study. The data were given to me in 12 separate data sets, each one of which contained all the study subjects, even if there were no data collected at that follow-up visit. All 12 data sets had identical structure and variable names. I first added a suffix to each variable name in a particular data set; for example, in the first data set, all variables had the suffix _01 added. The 12 data sets were then merged into a single data set so I could compare the changes in a specific variable's values over time. 
Unfortunately, because the way the data sets were organized, and because women were recruited over several months, the first data collection for a particular woman might be in the first data set with variables ending in "_01", the second data set with variables ending in "_02", etc, as shown in the schematic below.  

I need to determine the values for each woman's first data collection visit which might have any suffix from "_01" to "_12". Is there any way to efficiently ask SPSS which one of a set of variables (for example XYZ_01 to XYZ_12) is the first to contain a value and to then recode this value into another variable for each woman in the data set?  
Thanks for your help.

------------------------------
Bradley Woodruff
------------------------------

#SPSSStatistics

Hi.

Excuse all the warnings for missing data; they are an artifact of the made-up data I'm using. The part relevant to you is what comes after that - beginning with the VECTOR command. What is returned is the index of the first valid (i.e., non-missing) variable. I could get fancier if you want, but this gets the job done.

preserve.
set undefined=nowarn.
dataset close all.
new file.
output close all.
data list list /xyz_01 to xyz_12.
begin data.
. . . 1 1 1 1 . . . . .
1 1 1 . . . . . . . . .
. . . . . 1 1 1 1 1 . .
. . . . . . . . 1 1 1 1
end data.

vector #v=xyz_01 to xyz_12.
loop #i=1 to 12.
do if not sysmis(#v(#i)).
print / #i.
break.
end if.
end loop.
execute.
restore.

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------

Rather than

print / #i
​​
you could substitute a new variable:

compute first_valid=0.
vector #v=xyz_01 to xyz_12.
loop #i=1 to 12.
do if not sysmis(#v(#i)).
compute first_valid=#i.
break.
end if.
end loop.
list first_valid.​

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------

Original Message:
Sent: Thu December 01, 2022 06:07 PM
From: Rick Marcantonio
Subject: Recoding visit number

Hi.

Excuse all the warnings for missing data; they are an artifact of the made-up data I'm using. The part relevant to you is what comes after that - beginning with the VECTOR command. What is returned is the index of the first valid (i.e., non-missing) variable. I could get fancier if you want, but this gets the job done.

preserve.
set undefined=nowarn.
dataset close all.
new file.
output close all.
data list list /xyz_01 to xyz_12.
begin data.
. . . 1 1 1 1 . . . . .
1 1 1 . . . . . . . . .
. . . . . 1 1 1 1 1 . .
. . . . . . . . 1 1 1 1
end data.

vector #v=xyz_01 to xyz_12.
loop #i=1 to 12.
do if not sysmis(#v(#i)).
print / #i.
break.
end if.
end loop.
execute.
restore.

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------

** This creates a new variable with the value of the first valid existing variable:

** Create mock data.

data list list /xyz_01 to xyz_12.
begin data.
. . . 14 1 1 1 . . . . .
11 1 1 . . . . . . . . .
. . . . . 15 1 1 1 1 . .
. . . . . . . . 7 1 1 1
end data.

** Create the new variable, cleverly named "newvar" ;).

compute newvar=$sysmis.
vector #v=xyz_01 to xyz_12.
loop #i=1 to 12.
do if not sysmis(#v(#i)).
compute newvar=#v(#i).
break.
end if.
end loop.

** List the cases.

list newvar.

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------

Original Message:
Sent: Thu December 01, 2022 06:12 PM
From: Rick Marcantonio
Subject: Recoding visit number

Rather than

print / #i
​​
you could substitute a new variable:

compute first_valid=0.vector #v=xyz_01 to xyz_12.loop #i=1 to 12.do if not sysmis(#v(#i)).compute first_valid=#i.break.end if.end loop.list first_valid.​

------------------------------
Rick Marcantonio
Quality Assurance
IBM
------------------------------


Original Message:
Sent: Thu December 01, 2022 06:07 PM
From: Rick Marcantonio
Subject: Recoding visit number

Hi.

Excuse all the warnings for missing data; they are an artifact of the made-up data I'm using. The part relevant to you is what comes after that - beginning with the VECTOR command. What is returned is the index of the first valid (i.e., non-missing) variable. I could get fancier if you want, but this gets the job done.

preserve.
set undefined=nowarn.
dataset close all.
new file.
output close all.
data list list /xyz_01 to xyz_12.
begin data.
. . . 1 1 1 1 . . . . .
1 1 1 . . . . . . . . .
. . . . . 1 1 1 1 1 . .
. . . . . . . . 1 1 1 1
end data.

vector #v=xyz_01 to xyz_12.
loop #i=1 to 12.
do if not sysmis(#v(#i)).
print / #i.
break.
end if.
end loop.
execute.
restore.

------------------------------
Rick Marcantonio
Quality Assurance
IBM