Merge 2 data files based on many to many relationship

 

Download as .sps Shows as plain text

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
*(Q) I would like to merge to files based on a many to many two variables,
	neither of which are unique.

* The first file has two variables:
	--the first variable is unique,
	--the second variable is shared by groups of the first variable, so
	that none of the values for this variable are necessarily unique.

* The second file also has the second variable mentioned above for the
	first file.
* Again, none of these variables are necessarily unique.

* My goal is to get a merged file that multiples all of the first
	variables in the first file times each case in the second file, where
	the second variable in the first file = the variable in the second
	file.	

* Example:

*First File
	V1 V2
	1  A
	2  A
	3  A
	4  B
	5  B.

*Second File
	A
	A
	B.

* End result should be:
	1  A
	2  A
	3  A
	1  A
	2  A
	3  A
	4  B
	5  B.

*(A) Posted to SPSS newsgroup by Raynald Levesque on 2002/03/29.



DATA LIST LIST /v1(F8.0) v2(A1).
BEGIN DATA
1  A
2  A
3  A
4  B
5  B
END DATA.
SORT CASES BY v2.
SAVE OUTFILE='c:\\temp\\datafile1.sav'.

NEW FILE.
DATA LIST LIST /v2(A1).
BEGIN DATA
A
A
B
END DATA.
SAVE OUTFILE='c:\\temp\\datafile2.sav'.

***** Start job.
GET FILE='c:\\temp\\datafile2.sav'.
COMPUTE nobreak=1.



AGGREGATE
  /OUTFILE=*
  /BREAK=v2
  /nb = N(nobreak).
MATCH FILES TABLE=*
	/FILE='c:\\temp\\datafile1.sav'
	/BY v2.
WEIGHT CASES BY nb.

* For all practical purposes, the above gives you what you need.
* But in case you need the actual cases (not just a weigthed 
	file giving you the same number of cases), see
"Expand crosstab data into original data file.SPS" in 
http://pages.infinit.net/rlevesqu/SampleSyntax.htm#ReadWriteOrCreateData
Related pages

...

Navigate from here