SPSSX Discussion

Data reconfiguration (VarsToCases)

Classic

List

Threaded

6 messages Options

Doug

Data reconfiguration (VarsToCases)

Greetings-
I have a matrix of data I need to reconfigure so I can analyze it properly. After calculating transition probabilities over some years (4 in this example, more in reality), I have a 32x240 matrix, which I need to convert to a 7680-record file.

Current matrix:

State1->S1a	S1a	S1a	S1a	S1b	S1b	S1b	S1b	S1…48
Year->	2007	2008	2009	2010	2007	2008	2009	2010	2010
State0
S0a	0.26	0.29	0.28	0.29	0.30	0.30	0.31	0.32	0.23
S0b	0.14	0.10	0.11	0.12	0.12	0.12	0.12	0.12	0.12
S0c	0.14	0.10	0.11	0.12	0.12	0.12	0.12	0.12	0.12
S0…32	0.14	0.10	0.11	0.12	0.12	0.12	0.12	0.12	0.12

Desired format:

State0	State1	Year	Tr
S0a	S1a	2007	0.26
S0a	S1a	2007	0.29
S0a	S1a	2007	0.28
S0a	S1a	2007	0.29
S0a	S1b	2007	0.30
S0a	S1b	2007	0.30
S0a	S1b	2007	0.31
S0a	S1b	2007	0.32
S0a	S1…48	2008	0.23
S0b	S1a	2008	0.14
S0b	S1a	2008	0.10
S0b	S1a	2008	0.11
S0b	S1a	2008	0.12
S0b	S1b	2008	0.12
S0b	S1b	2008	0.12
S0b	S1b	2008	0.12
S0b	S1b	2008	0.12
S0b	S1…48	2009	0.12
S0c	…	…	…
S0…32	…	…	…

I'm familiar with the casestovars and varstocases functions, but not particularly comfortable. Any syntax advice with this would be appreciated.

Thanks,
Doug

Valerie Villella

Automatic reply: Data reconfiguration (VarsToCases)

Thank you for your email. Please note that I will be out of the office from Thursday, November 17 and returning Wednesday, November 23, 2011. I will respond to my emails upon my return. If this is an urgent matter please contact Dan Buchanan at [hidden email] or at 905-851-8821 ext. 229.

=====================
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

David Marso

Re: Data reconfiguration (VarsToCases)

Administrator

In reply to this post by Doug

Doug,
Your desired format does NOT map in any way to your current format (unless you are presenting a messed up example -why does 2007 repeat when the numbers below the headings are 2007, 2008...-).
Please elaborate or provide a correct example. VarsToCases would suffice but it does NOT look like you have this stored in SPSS but rather in some sort of table. Could just blast away and map the correct values of state1 and year later.

DATA LIST LIST /state0 (a3) s1a2007 TO s1a2010 (4F8.2) s1b2007 to s1b2010 (4f8.2) othrjunk (F).
begin data
S0a 0.26 0.29 0.28 0.29 0.30 0.30 0.31 0.32 0.23
S0b 0.14 0.10 0.11 0.12 0.12 0.12 0.12 0.12 0.12
S0c 0.14 0.10 0.11 0.12 0.12 0.12 0.12 0.12 0.12
end data.
VARSTOCASES /MAKE trans1 FROM s1a2007 s1a2008 s1a2009 s1a2010 s1b2007
s1b2008 s1b2009 s1b2010
/INDEX = state1(2) year(4)
/KEEP = state0
/NULL = KEEP.
RECODE year (1=2007)(2=2008)(3=2009)(4=2010).
STRING state1r(a8).
RECODE state1 (1="s1a")(2="s1b") INTO state1r /*etc*/.
exe.
HTH, David

Doug wrote
Greetings-
I have a matrix of data I need to reconfigure so I can analyze it properly. After calculating transition probabilities over some years (4 in this example, more in reality), I have a 32x240 matrix, which I need to convert to a 7680-record file.

Current matrix:
State1->S1a	S1a	S1a	S1a	S1b	S1b	S1b	S1b	S1…48
Year->	2007	2008	2009	2010	2007	2008	2009	2010	2010
State0
S0a	0.26	0.29	0.28	0.29	0.30	0.30	0.31	0.32	0.23
S0b	0.14	0.10	0.11	0.12	0.12	0.12	0.12	0.12	0.12
S0c	0.14	0.10	0.11	0.12	0.12	0.12	0.12	0.12	0.12
S0…32	0.14	0.10	0.11	0.12	0.12	0.12	0.12	0.12	0.12
Desired format:
State0	State1	Year	Tr
S0a	S1a	2007	0.26
S0a	S1a	2007	0.29
S0a	S1a	2007	0.28
S0a	S1a	2007	0.29
S0a	S1b	2007	0.30
S0a	S1b	2007	0.30
S0a	S1b	2007	0.31
S0a	S1b	2007	0.32
S0a	S1…48	2008	0.23
S0b	S1a	2008	0.14
S0b	S1a	2008	0.10
S0b	S1a	2008	0.11
S0b	S1a	2008	0.12
S0b	S1b	2008	0.12
S0b	S1b	2008	0.12
S0b	S1b	2008	0.12
S0b	S1b	2008	0.12
S0b	S1…48	2009	0.12
S0c	…	…	…
S0…32	…	…	…
I'm familiar with the casestovars and varstocases functions, but not particularly comfortable. Any syntax advice with this would be appreciated.

Thanks,
Doug

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

Maguin, Eugene

Re: Data reconfiguration (VarsToCases)

In reply to this post by Doug

Doug, the current and desired matrix structure information is missing from
your posting. Hard to say anything useful without that. Re-post will you.
Gene Maguin

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Doug
Sent: Wednesday, November 16, 2011 5:22 PM
To: [hidden email]
Subject: Data reconfiguration (VarsToCases)

Greetings-
I have a matrix of data I need to reconfigure so I can analyze it properly.
After calculating transition probabilities over some years (4 in this
example, more in reality), I have a 32x240 matrix, which I need to convert
to a 7680-record file.

Current matrix:

Desired format:

I'm familiar with the casestovars and varstocases functions, but not
particularly comfortable. Any syntax advice with this would be appreciated.

Thanks,
Doug

--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/Data-reconfiguration-VarsToCas
es-tp4999309p4999309.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
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

=====================
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

Maguin, Eugene

Re: Data reconfiguration (VarsToCases)

Doug,

Let's get some basics out of the way. Is this file now an spss data file or
a comma delimited text or define column text file or some other format?

I don't understand the structure you describe. First, let's combine state1
and year. So are you saying you have 4*2*48 data columns, from
s1a2007-s1a2010, s1b2007-s1b2010 . . . s48a2007-s48a2010,
s48b2007-s48b2010??
And, are you saying you have 3*33 rows, from s0a, s0b, s0c . . . s32a, s32b,
s32c??

Let's suppose that is all true. Suppose your variables are named as I
describe above. Then:

data list records=2 /
state0 s1a2007 to s1a2010 s1b2007 to s1b2010 (a3,8(1x,f4.2))
/ s2a2007 to s2a2010 s2b2007 to s2b2010 (3x,8(1x,f4.2)).
begin data
S0a 0.26 0.27 0.28 0.29 0.30 0.31 0.32 0.33
0.27 0.28 0.29 0.30 0.31 0.32 0.33 0.34
S0b 0.14 0.10 0.11 0.12 0.13 0.14 0.15 0.16
0.15 0.11 0.12 0.13 0.14 0.15 0.16 0.17
S0c 0.15 0.11 0.12 0.13 0.14 0.15 0.16 0.17
0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18
S1a 0.27 0.28 0.29 0.30 0.31 0.32 0.33 0.34
0.28 0.29 0.30 0.31 0.32 0.33 0.34 0.35
S1b 0.16 0.18 0.19 0.20 0.21 0.22 0.23 0.24
0.18 0.19 0.20 0.21 0.22 0.23 0.24 0.25
S1c 0.17 0.19 0.20 0.21 0.22 0.23 0.24 0.25
0.19 0.20 0.21 0.22 0.23 0.24 0.25 0.26
end data.

varstocases make transition from s1a2007 to s2b2010/
index=seqnum.

compute year=2007+mod((seqnum-1),4).
format year(f4.0).

compute seq2=1.
if (state0 eq lag(state0)) seq2=lag(seq2)+1.
compute set=trunc((seq2-1)/8)+1.
format set(f2.0).

string state1(a3).
do if (seqnum le 4).
+ compute state1=concat('S',ltrim(string(set,f2.0)),'a').
else if (seqnum gt 4).
+ compute state1=concat('S',ltrim(string(set,f2.0)),'b').
end if.
execute.

See if this works for you.

Gene Maguin

It's an S0-->S1 transition matrix, non-square, with a column for each
S1, with S1s repeated over 5 years. I'm trying to get a 4-column file
with S0, S1, Year, and Tr (the response variable).

I might be better off regenerating the table so years repeat
differently. The basic problem is casestovars with layered data.

Thanks for any help you can offer.

State1->S1a S1a S1a S1a S1b S1b S1b S1b
S1.48
Year-> 2007 2008 2009 2010 2007 2008 2009 2010 2010
State0
S0a 0.26 0.29 0.28 0.29 0.30 0.30 0.31 0.32 0.23
S0b 0.14 0.10 0.11 0.12 0.12 0.12 0.12 0.12 0.12
S0c 0.14 0.10 0.11 0.12 0.12 0.12 0.12 0.12 0.12
S0.32 0.14 0.10 0.11 0.12 0.12 0.12 0.12 0.12 0.12

Desired format:

State0 State1 Year Tr
S0a S1a 2007 0.26
S0a S1a 2007 0.29
S0a S1a 2007 0.28
S0a S1a 2007 0.29
S0a S1b 2007 0.30
S0a S1b 2007 0.30
S0a S1b 2007 0.31
S0a S1b 2007 0.32
S0a S1.48 2008 0.23
S0b S1a 2008 0.14
S0b S1a 2008 0.10
S0b S1a 2008 0.11
S0b S1a 2008 0.12
S0b S1b 2008 0.12
S0b S1b 2008 0.12
S0b S1b 2008 0.12
S0b S1b 2008 0.12
S0b S1.48 2009 0.12
S0c . . .
S0.32 . . .

=====================
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

David Marso

Re: Data reconfiguration (VarsToCases)

Administrator

Easier to use 2 INDEX variables in the VARSTOCASES per my previously posted solution.

Gene Maguin wrote

Doug,

Let's get some basics out of the way. Is this file now an spss data file or
a comma delimited text or define column text file or some other format?

I don't understand the structure you describe. First, let's combine state1
and year. So are you saying you have 4*2*48 data columns, from
s1a2007-s1a2010, s1b2007-s1b2010 . . . s48a2007-s48a2010,
s48b2007-s48b2010??
And, are you saying you have 3*33 rows, from s0a, s0b, s0c . . . s32a, s32b,
s32c??

Let's suppose that is all true. Suppose your variables are named as I
describe above. Then:

data list records=2 /
state0 s1a2007 to s1a2010 s1b2007 to s1b2010 (a3,8(1x,f4.2))
/ s2a2007 to s2a2010 s2b2007 to s2b2010 (3x,8(1x,f4.2)).
begin data
S0a 0.26 0.27 0.28 0.29 0.30 0.31 0.32 0.33
0.27 0.28 0.29 0.30 0.31 0.32 0.33 0.34
S0b 0.14 0.10 0.11 0.12 0.13 0.14 0.15 0.16
0.15 0.11 0.12 0.13 0.14 0.15 0.16 0.17
S0c 0.15 0.11 0.12 0.13 0.14 0.15 0.16 0.17
0.11 0.12 0.13 0.14 0.15 0.16 0.17 0.18
S1a 0.27 0.28 0.29 0.30 0.31 0.32 0.33 0.34
0.28 0.29 0.30 0.31 0.32 0.33 0.34 0.35
S1b 0.16 0.18 0.19 0.20 0.21 0.22 0.23 0.24
0.18 0.19 0.20 0.21 0.22 0.23 0.24 0.25
S1c 0.17 0.19 0.20 0.21 0.22 0.23 0.24 0.25
0.19 0.20 0.21 0.22 0.23 0.24 0.25 0.26
end data.

varstocases make transition from s1a2007 to s2b2010/
index=seqnum.

compute year=2007+mod((seqnum-1),4).
format year(f4.0).

compute seq2=1.
if (state0 eq lag(state0)) seq2=lag(seq2)+1.
compute set=trunc((seq2-1)/8)+1.
format set(f2.0).

string state1(a3).
do if (seqnum le 4).
+ compute state1=concat('S',ltrim(string(set,f2.0)),'a').
else if (seqnum gt 4).
+ compute state1=concat('S',ltrim(string(set,f2.0)),'b').
end if.
execute.

See if this works for you.

Gene Maguin

It's an S0-->S1 transition matrix, non-square, with a column for each
S1, with S1s repeated over 5 years. I'm trying to get a 4-column file
with S0, S1, Year, and Tr (the response variable).

I might be better off regenerating the table so years repeat
differently. The basic problem is casestovars with layered data.

Thanks for any help you can offer.

State1->S1a S1a S1a S1a S1b S1b S1b S1b
S1.48
Year-> 2007 2008 2009 2010 2007 2008 2009 2010 2010
State0
S0a 0.26 0.29 0.28 0.29 0.30 0.30 0.31 0.32 0.23
S0b 0.14 0.10 0.11 0.12 0.12 0.12 0.12 0.12 0.12
S0c 0.14 0.10 0.11 0.12 0.12 0.12 0.12 0.12 0.12
S0.32 0.14 0.10 0.11 0.12 0.12 0.12 0.12 0.12 0.12

Desired format:

State0 State1 Year Tr
S0a S1a 2007 0.26
S0a S1a 2007 0.29
S0a S1a 2007 0.28
S0a S1a 2007 0.29
S0a S1b 2007 0.30
S0a S1b 2007 0.30
S0a S1b 2007 0.31
S0a S1b 2007 0.32
S0a S1.48 2008 0.23
S0b S1a 2008 0.14
S0b S1a 2008 0.10
S0b S1a 2008 0.11
S0b S1a 2008 0.12
S0b S1b 2008 0.12
S0b S1b 2008 0.12
S0b S1b 2008 0.12
S0b S1b 2008 0.12
S0b S1.48 2009 0.12
S0c . . .
S0.32 . . .

=====================
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