Date:         Tue, 18 Oct 2005 09:13:09 +0200
Reply-To:     Spousta Jan <JSpousta@CSAS.CZ>
Sender:       "SPSSX(r) Discussion" <SPSSX-L@LISTSERV.UGA.EDU>
From:         Spousta Jan <JSpousta@CSAS.CZ>
Subject:      Re: Merging Files
Content-Type: text/plain; charset="US-ASCII"

Hi Mahbub,

Perhaps I do not understand your question well, but it seems me that you
wish to construct a cartesian product of the two data sets, that is a
file with 500 * 350 = 175,000 cases.

I use in such cases mostly SQL which constructs cartesian products in a
natural way (it is implemented in PROC SQL in SAS and -as far as I know-
can be used in SPSS's command GET DATA).

The (untested !!!) algorithm for SPSS would be:

1) Write the the data tables in a database. E.g.:

* have an access file c:\files\js.mdb prepared.
get file accnum /*account numbers*/.
SAVE TRANSLATE /CONNECT='DSN=MS Access'+
  ' Database;DBQ=c:\files\js.mdb;DefaultDir= c:\files;
DriverId=25;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;'
  /TABLE = "ac" /keep accnr /TYPE = ODBC /replace.
get file secnum /*secondary account numbers*/.
SAVE TRANSLATE /CONNECT='DSN=MS Access'+
  ' Database;DBQ=c:\files\js.mdb;DefaultDir= c:\files;
DriverId=25;FIL=MS Access;MaxBufferSize=512;PageTimeout=5;'
  /TABLE = "se" /keep secnr /TYPE = ODBC /replace.

2) And now read the two tables from the Access database and merge them:

GET DATA /TYPE=ODBC /CONNECT=
'DSN=MS Access Database;DBQ=c:\files\js.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;'
 /SQL = 'SELECT `AC`.`ACCNR` AS `ACCNR`, `SE`.`SECNR` AS `SECNR`'
 'FROM  `c:\files\js`.`ac` `AC`, `c:\files\js`.`se` `SE` '
.

(In my humble opinion, people constructing programming languages with so
many apostrophes should be punished by law: they should pay one dollar
for each useless character I must use in the expression.)

I use SAS for it, so I do not have much experience with SQL in SPSS, but
there are other experts on the discussion list if you need to know more.


HTH

Jan

-----Original Message-----
From: SPSSX(r) Discussion [mailto:SPSSX-L@LISTSERV.UGA.EDU] On Behalf Of
Richard Ristow
Sent: Monday, October 17, 2005 9:38 PM
To: SPSSX-L@LISTSERV.UGA.EDU
Subject: Re: Merging Files

At 02:31 PM 10/17/2005, Mahbub Khandoker wrote:

>I have a file that contains account numbers (about 500 of them) and
>another files that have secondary account number (about 350). For each
>account numbers I want to merge the number with each secondary numbers
>that is for each account numbers there would be 350 combined numbers.
>But there is no identical variable in both files as a key id.

As you describe it, in general, no.

To see if there's any chance, remember that "computing has very little
to do with computers."

Suppose you had the files on, say, 3x5 cards, 500 white ones in one box,
350 red ones in another. They start out sorted in any order you specify
beforehand. How would you do the merge by hand?

If there's a way, there's a good chance it can be translated into an
algorithm. If there's not, now you know why you can't do it.

-Regards and good luck,
  Richard Ristow