insert extra rows or cells

[xenia]

hello list,
I have a variable, representing IDs, so it goes like:
1
2
3
45
67
77
78
79
80
etc. Is it possible in SPSS syntax, to put extra cells or rows in that same variable, not recode to another, and fill them in with data so that the IDs appear consecutively? i.e. 1, 2, 3, 4, 5, etc. So, in short, I want to put empty rows or cells between non-consecutive numbers and give them the respective value.

[David Marso]

Here is one approach.
data list free / id data.
begin data
1 123 2 345 3 657 45 123 67 345 77 567 78 123 79 123 80 233
end data.
DO IF id NE lag(id).
+  LOOP newid=lag(id)+1 TO id-1.
+    XSAVE OUTFILE 'ID_Fill.sav' / RENAME (NEWID ID=ID @)/DROP data .
+  END LOOP.
END IF.
EXE.
ADD FILES / FILE * / FILE 'ID_Fill.sav'  / BY ID /DROP @ newid.
EXE.

xenia wrote

hello list,
I have a variable, representing IDs, so it goes like:
1
2
3
45
67
77
78
79
80
etc. Is it possible in SPSS syntax, to put extra cells or rows in that same variable, not recode to another, and fill them in with data so that the IDs appear consecutively? i.e. 1, 2, 3, 4, 5, etc. So, in short, I want to put empty rows or cells between non-consecutive numbers and give them the respective value.

[xenia]

This is great, thank you.
However, if my list has e.g. 10000 IDs, how can I read-in the ID variable and feed it in this syntax? i.e. I don't want to provide a list, but the variable to be read automatically.

[Bruce Weaver]

That's interesting.  I was thinking of something a little different.  

1) Use INPUT PROGRAM to create a dataset with ID ranging from 1 to N;
2) Merge with the original dataset (MATCH FILES BY ID).

If the first ID in the original file is GREATER than 1, my method will still yield a final file with ID ranging from 1 to N.  David's will have ID ranging from the first ID in the original file to N.  It's not clear to me which the OP wants.

HTH.

David Marso wrote

Here is one approach.
data list free / id data.
begin data
1 123 2 345 3 657 45 123 67 345 77 567 78 123 79 123 80 233
end data.
DO IF id NE lag(id).
+  LOOP newid=lag(id)+1 TO id-1.
+    XSAVE OUTFILE 'ID_Fill.sav' / RENAME (NEWID ID=ID @)/DROP data .
+  END LOOP.
END IF.
EXE.
ADD FILES / FILE * / FILE 'ID_Fill.sav'  / BY ID /DROP @ newid.
EXE.

xenia wrote

hello list,
I have a variable, representing IDs, so it goes like:
1
2
3
45
67
77
78
79
80
etc. Is it possible in SPSS syntax, to put extra cells or rows in that same variable, not recode to another, and fill them in with data so that the IDs appear consecutively? i.e. 1, 2, 3, 4, 5, etc. So, in short, I want to put empty rows or cells between non-consecutive numbers and give them the respective value.

[David Marso]

Just use your existing data rather than my mock up (ie, nix DATA LIST...END DATA).
My code assumes the variable is called ID (modify accordingly).

xenia wrote

This is great, thank you.
However, if my list has e.g. 10000 IDs, how can I read-in the ID variable and feed it in this syntax? i.e. I don't want to provide a list, but the variable to be read automatically.

[xenia]

I don't mind it starting from either 1 or the first list number, what I'm interested in is to fill in the same variable with the missing IDs. Also, if I have another variable with values corresponding to these IDs e.g.
ID     age
3       35
4       31
5       22
8       50
9       44
11     29
14     36
 
I'd like the missing ID cells/rows to be inserted for age as well, not just for ID, i.e.

ID     age
3       35
4       31
5       22
6
7
8       50
9       44
10
11     29
12
13
14     36

Thank you

[xenia]

ok ,thank you

[David Marso]

I'll see your INPUT PROGRAM and raise you a two liner (MATRIX-magic).
However, I wouldn't try this with a file of 1000000 IDs ;-)
----
***SAMPLE DATA (note last ID is 10000***.
data list free / id data.
begin data
1 123 2 345 3 657 45 123 67 345 77 567 78 123 79 123 80 233 10000 222
end data.

MATRIX.
GET data / file * /var id .
SAVE (T({1:data(nrow(data),1)})) /OUTFILE 'id_fill.sav' / VARIABLE ID.
END MATRIX.
MATCH FILES / FILE * / IN=@ORIG@/FILE 'id_fill.sav' /BY ID.
EXE.

I've been doing a LOT of work with MATRIX over the past couple months and
one can do some really slick stuff with it.
Riddle me this Bruce:  What do you think the following code does?
COMPUTE Resid= dv- Des * GINV(T(DES) * Des) * T(Des) * dv.
COMPUTE ResidCP=SSCP({Resid,{0;Resid(1:(NROW(Resid)-1))}}).
COMPUTE AC_UnC=ResidCP(1,2)/ResidCP(1,1).

Bruce Weaver wrote

That's interesting.  I was thinking of something a little different.  

1) Use INPUT PROGRAM to create a dataset with ID ranging from 1 to N;
2) Merge with the original dataset (MATCH FILES BY ID).

If the first ID in the original file is GREATER than 1, my method will still yield a final file with ID ranging from 1 to N.  David's will have ID ranging from the first ID in the original file to N.  It's not clear to me which the OP wants.

HTH.

David Marso wrote

Here is one approach.
data list free / id data.
begin data
1 123 2 345 3 657 45 123 67 345 77 567 78 123 79 123 80 233
end data.
DO IF id NE lag(id).
+  LOOP newid=lag(id)+1 TO id-1.
+    XSAVE OUTFILE 'ID_Fill.sav' / RENAME (NEWID ID=ID @)/DROP data .
+  END LOOP.
END IF.
EXE.
ADD FILES / FILE * / FILE 'ID_Fill.sav'  / BY ID /DROP @ newid.
EXE.

xenia wrote

hello list,
I have a variable, representing IDs, so it goes like:
1
2
3
45
67
77
78
79
80
etc. Is it possible in SPSS syntax, to put extra cells or rows in that same variable, not recode to another, and fill them in with data so that the IDs appear consecutively? i.e. 1, 2, 3, 4, 5, etc. So, in short, I want to put empty rows or cells between non-consecutive numbers and give them the respective value.

[xenia]

This is great, many thanks