SPSSX Discussion

How better can data concatenate...

Classic

List

Threaded

5 messages Options

Juris Breidaks

How better can data concatenate...

Dear members,

I have one problem how to concatenate 3 data

1 data file stucture

name age
sas 12
asa 13
ada 32

2 data file stucture

add day
121 1
131 2
111 3

3 data file stucture

memo aa
aaaa e
bbb f
ccc g

In result i need so structure

name age add day memo aa
sas 12 121 1 aaaa e
sas 13 121 1 aaaa e
sas 32 121 1 aaaa e
sas 12 131 2 aaaa e
sas 13 131 2 aaaa e
sas 32 131 2 aaaa e
sas 12 111 3 aaaa e
sas 13 111 3 aaaa e
sas 32 111 3 aaaa e
sas 12 121 1 bbb f
sas 13 121 1 bbb f
sas 32 121 1 bbb f
sas 12 131 2 bbb f
sas 13 131 2 bbb f
sas 32 131 2 bbb f
sas 12 111 3 bbb f
sas 13 111 3 bbb f
sas 32 111 3 bbb f
sas 12 121 1 cccc g
sas 13 121 1 cccc g
sas 32 121 1 cccc g
sas 12 131 2 cccc g
sas 13 131 2 cccc g
sas 32 131 2 cccc g
sas 12 111 3 cccc g
sas 13 111 3 cccc g
sas 32 111 3 cccc g

With best regards,
Juris

Marks, Jim

Re: How better can data concatenate...

Juris:

What are the rules for the final file?

Is it for every name, all combination of age, add, day, memo, and aa?

(Or is there a mistake in the final data, and you want all combinations of add, day, memo, and aa for each name-age pair?

Jim Marks

Director, Market Research

x1616

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Juris Breidaks
Sent: Wednesday, March 04, 2009 8:22 AM
To: [hidden email]
Subject: How better can data concatenate...

Art Kendall

Re: How better can data concatenate...

In reply to this post by Juris Breidaks

Please give a more verbose description of your problem.

I am not sure what you are asking.
How many cases do you want?
is the order of cases important?
is the order of variables important?

This may give you some idea what to do. I need to turn to something else.

see if this helps you approach your problem.
Open a new instance of SPSS. Copy the syntax below to a syntax file. Click <run>. Click <all>.

Art Kendall
Social Research Consultants

new file. input program. *generate indices. numeric id(f4). numeric pair1 pair2 pair3 (f1). leave id pair1 pair2. loop pair1 = 1 to 3. loop pair2 = 1 to 3. loop pair3 = 1 to 3. compute id = id +1. end case. end loop. end loop. end loop. end file. end input program. string name (a3). numeric age(f2) add (f3) day (f1). string memo (a4) aa(a1). do if pair1 eq 1. compute name = 'sas'. compute age = 12. else if pair1 eq 2. compute name = 'asa'. compute age = 13. else if pair1 eq 3. compute name = 'ada'. compute age = 32. end if. do if pair2 eq 1. compute add = 121. compute day = 1. else if pair2 eq 2. compute add = 131. compute day = 2. else if pair2 eq 3. compute add = 111. compute day = 3. end if. do if pair3 eq 1. compute memo = 'aaaa'. compute aa = 'e'. else if pair3 eq 2. compute memo = 'bbb'. compute aa = 'f'. else if pair3 eq 3. compute memo = 'ccc'. compute aa = 'g'. end if. formats pair1 pair2 pair3 (f1). execute.

Art
Juris Breidaks wrote:

Dear members,

I have one problem how to concatenate 3 data

1 data file stucture

name age
sas 12
asa 13
ada 32

2 data file stucture

add day
121 1
131 2
111 3

3 data file stucture

memo aa
aaaa e
bbb f
ccc g

In result i need so structure

name age add day memo aa
sas 12 121 1 aaaa e
sas 13 121 1 aaaa e
sas 32 121 1 aaaa e
sas 12 131 2 aaaa e
sas 13 131 2 aaaa e
sas 32 131 2 aaaa e
sas 12 111 3 aaaa e
sas 13 111 3 aaaa e
sas 32 111 3 aaaa e
sas 12 121 1 bbb f
sas 13 121 1 bbb f
sas 32 121 1 bbb f
sas 12 131 2 bbb f
sas 13 131 2 bbb f
sas 32 131 2 bbb f
sas 12 111 3 bbb f
sas 13 111 3 bbb f
sas 32 111 3 bbb f
sas 12 121 1 cccc g
sas 13 121 1 cccc g
sas 32 121 1 cccc g
sas 12 131 2 cccc g
sas 13 131 2 cccc g
sas 32 131 2 cccc g
sas 12 111 3 cccc g
sas 13 111 3 cccc g
sas 32 111 3 cccc g

With best regards,
Juris

Art Kendall
Social Research Consultants

hillel vardi

Re: How better can data concatenate...

In reply to this post by Juris Breidaks

Shalom

It look like what you want is a *Cartesian product* of your data the
is 3 files 3 line will create 3**3 = 27 lines
The easy way of doing it is sql merge table where there is no common key
between the table (you can do it in access).
In SPSS it can by done by merging file A with file B and then using
CASESTOVARS and VARSTOCASES then repeating this for file C .

here is the syntax

dataset close all .
data list / name age (a11 f2).
begin data .
sas 12
asa 13
ada 32
end data .
compute file=1.
SORT CASES BY file .
dataset name A .

data list / add day (a11 f1).
begin data .
121 1
131 2
111 3
end data .
dataset name B .
compute file=1.
SORT CASES BY file .
CASESTOVARS
/ID = file
/GROUPBY = VARIABLE .
match file file= A / table=B / by file .
execute .
dataset name AB .
VARSTOCASES /MAKE add FROM add.1 add.2 add.3
/MAKE day FROM day.1 day.2 day.3
/INDEX = Index2(3)
/NULL = KEEP.

data list / memo aa (a11 a1).
begin data .
aaaa e
bbb f
ccc g
end data .
compute file=1.
dataset name C .
SORT CASES BY file .
CASESTOVARS
/ID = file
/GROUPBY = VARIABLE .
match file file= AB / table=C / by file .
VARSTOCASES /MAKE memo FROM memo.1 memo.2 memo.3
/MAKE aa FROM aa.1 aa.2 aa.3
/INDEX = Index3(3)
/NULL = KEEP.
dataset name ABC .

Hillel Vardi
BGU

uris Breidaks wrote:

>
> Dear members,
>
> I have one problem how to concatenate 3 data
>
> 1 data file stucture
>
> name age
> sas 12
> asa 13
> ada 32
>
> 2 data file stucture
>
> add day
> 121 1
> 131 2
> 111 3
>
> 3 data file stucture
>
> memo aa
> aaaa e
> bbb f
> ccc g
>
>
>
> In result i need so structure
>
> name age add day memo aa
> sas 12 121 1 aaaa e
> sas 13 121 1 aaaa e
> sas 32 121 1 aaaa e
> sas 12 131 2 aaaa e
> sas 13 131 2 aaaa e
> sas 32 131 2 aaaa e
> sas 12 111 3 aaaa e
> sas 13 111 3 aaaa e
> sas 32 111 3 aaaa e
> sas 12 121 1 bbb f
> sas 13 121 1 bbb f
> sas 32 121 1 bbb f
> sas 12 131 2 bbb f
> sas 13 131 2 bbb f
> sas 32 131 2 bbb f
> sas 12 111 3 bbb f
> sas 13 111 3 bbb f
> sas 32 111 3 bbb f
> sas 12 121 1 cccc g
> sas 13 121 1 cccc g
> sas 32 121 1 cccc g
> sas 12 131 2 cccc g
> sas 13 131 2 cccc g
> sas 32 131 2 cccc g
> sas 12 111 3 cccc g
> sas 13 111 3 cccc g
> sas 32 111 3 cccc g
>
>
>
> With best regards,
> Juris
>

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Albert-Jan Roskam

Re: How better can data concatenate...

In reply to this post by Juris Breidaks

Hi!

The Cartesian product can be created using simple SQL statements (implicit cross join) in GET DATA. If you add sav files as a data source the following GET DATA syntax will work. Beware, though, that you'll create a potentially _massive_ dataset (combinatorial explosion).

get data /type=odbc /connect=
'dsn=d:/temp/;dbq=d:/temp;server=nottheserver'
/sql = 'select * from data1, data2, data3'
/assumedstrwidth=4.

Is this what you want?

Cheers!!
Albert-Jan

** sample data.
data list free / name (a3) age (f2).
begin data
sas 12
asa 13
ada 32
end data .
save outfile = 'd:/temp/data1.sav'.

data list free / addr (f3) day (f1).
begin data
121 1
131 2
111 3
end data .
save outfile = 'd:/temp/data2.sav'.

data list free / memo (a4) aa (a1).
begin data
aaaa e
bbb f
ccc g
end data .
save outfile = 'd:/temp/data3.sav'.

** actual code.
get data /type=odbc /connect=
'dsn=d:/temp/;dbq=d:/temp;server=nottheserver'
/sql = 'select * from data1, data2, data3'
/assumedstrwidth=4.

list all.

** list results.
name age addr day memo aa

sas 12.00 121.00 1.00 aaaa e
sas 12.00 121.00 1.00 bbb f
sas 12.00 121.00 1.00 ccc g
sas 12.00 131.00 2.00 aaaa e
sas 12.00 131.00 2.00 bbb f
sas 12.00 131.00 2.00 ccc g
sas 12.00 111.00 3.00 aaaa e
sas 12.00 111.00 3.00 bbb f
sas 12.00 111.00 3.00 ccc g
asa 13.00 121.00 1.00 aaaa e
asa 13.00 121.00 1.00 bbb f
asa 13.00 121.00 1.00 ccc g
asa 13.00 131.00 2.00 aaaa e
asa 13.00 131.00 2.00 bbb f
asa 13.00 131.00 2.00 ccc g
asa 13.00 111.00 3.00 aaaa e
asa 13.00 111.00 3.00 bbb f
asa 13.00 111.00 3.00 ccc g
ada 32.00 121.00 1.00 aaaa e
ada 32.00 121.00 1.00 bbb f
ada 32.00 121.00 1.00 ccc g
ada 32.00 131.00 2.00 aaaa e
ada 32.00 131.00 2.00 bbb f
ada 32.00 131.00 2.00 ccc g
ada 32.00 111.00 3.00 aaaa e
ada 32.00 111.00 3.00 bbb f
ada 32.00 111.00 3.00 ccc g

Number of cases read: 27 Number of cases listed: 27

--- On Wed, 3/4/09, hillel vardi <[hidden email]> wrote:

> From: hillel vardi <[hidden email]>
> Subject: Re: How better can data concatenate...
> To: [hidden email]
> Date: Wednesday, March 4, 2009, 10:29 PM
> Shalom
>
> It look like what you want is a *Cartesian product* of your
> data the
> is 3 files 3 line will create 3**3 = 27 lines
> The easy way of doing it is sql merge table where there is
> no common key
> between the table (you can do it in access).
> In SPSS it can by done by merging file
> A with file B and then using
> CASESTOVARS and VARSTOCASES then repeating this for
> file C .
>
> here is the syntax
>
>
> dataset close all .
> data list / name age (a11 f2).
> begin data .
> sas 12
> asa 13
> ada 32
> end data .
> compute file=1.
> SORT CASES BY file .
> dataset name A .
>
> data list / add day (a11 f1).
> begin data .
> 121 1
> 131 2
> 111 3
> end data .
> dataset name B .
> compute file=1.
> SORT CASES BY file .
> CASESTOVARS
> /ID = file
> /GROUPBY = VARIABLE .
> match file file= A / table=B / by file .
> execute .
> dataset name AB .
> VARSTOCASES /MAKE add FROM add.1 add.2 add.3
> /MAKE day FROM day.1 day.2 day.3
> /INDEX = Index2(3)
> /NULL = KEEP.
>
> data list / memo aa (a11 a1).
> begin data .
> aaaa e
> bbb f
> ccc g
> end data .
> compute file=1.
> dataset name C .
> SORT CASES BY file .
> CASESTOVARS
> /ID = file
> /GROUPBY = VARIABLE .
> match file file= AB / table=C / by file .
> VARSTOCASES /MAKE memo FROM memo.1 memo.2 memo.3
> /MAKE aa FROM aa.1 aa.2 aa.3
> /INDEX = Index3(3)
> /NULL = KEEP.
> dataset name ABC .
>
>
> Hillel Vardi
> BGU
>
>
> uris Breidaks wrote:
> >
> > Dear members,
> >
> > I have one problem how to concatenate 3 data
> >
> > 1 data file stucture
> >
> > name age
> > sas 12
> > asa 13
> > ada 32
> >
> > 2 data file stucture
> >
> > add day
> > 121 1
> > 131 2
> > 111 3
> >
> > 3 data file stucture
> >
> > memo aa
> > aaaa e
> > bbb f
> > ccc g
> >
> >
> >
> > In result i need so structure
> >
> > name age
> add
> day
> memo aa
> > sas 12
> 121 1
> aaaa e
> > sas 13
> 121 1
> aaaa e
> > sas 32
> 121 1
> aaaa e
> > sas 12
> 131 2
> aaaa e
> > sas 13
> 131 2
> aaaa e
> > sas 32
> 131 2
> aaaa e
> > sas 12
> 111 3
> aaaa e
> > sas 13
> 111 3
> aaaa e
> > sas 32
> 111 3
> aaaa e
> > sas 12
> 121 1
> bbb f
> > sas 13
> 121 1
> bbb f
> > sas 32
> 121 1
> bbb f
> > sas 12
> 131 2
> bbb f
> > sas 13
> 131 2
> bbb f
> > sas 32
> 131 2
> bbb f
> > sas 12
> 111 3
> bbb f
> > sas 13
> 111 3
> bbb f
> > sas 32
> 111 3
> bbb f
> > sas 12
> 121 1
> cccc g
> > sas 13
> 121 1
> cccc g
> > sas 32
> 121 1
> cccc g
> > sas 12
> 131 2
> cccc g
> > sas 13
> 131 2
> cccc g
> > sas 32
> 131 2
> cccc g
> > sas 12
> 111 3
> cccc g
> > sas 13
> 111 3
> cccc g
> > sas 32
> 111 3
> cccc g
> >
> >
> >
> > With best regards,
> > Juris
> >
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email]
> (not to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the
> command
> INFO REFCARD
>