SPSSX Discussion

stacked file restructure

Classic

List

Threaded

9 messages Options

Kylie

stacked file restructure

Hi all,

I am hoping for assistance with an awkward data file restructure. The
original data has been provided in the following format (which I have dubbed
"stacked"):

ID DAY PARAM VALUE
1 0 WEIGHT 105.6
1 0 SYS BP 110
1 0 TEMP 34.4
1 7 WEIGHT 105.1
1 7 SYS BP 114
1 7 TEMP 33.9
...

Ie, each measurement parameter has been listed as a separate row in the same
variable, rather than getting its own variable (ie, the parameters are
"stacked" on top of each other).

I need to get this data into both of the standard formats:
- "long" - one row per subject per day with separate variables per
parameter, and
- "wide" - one row per subject with separate variables per parameter per day
(Obviously, once I can get it into either of the above then the other is
trivial)

That is:

ID DAY WEIGHT SYSBP TEMP
1 0 105.6 110 34.4
1 7 105.1 114 33.9
...

ID WEIGHT_0 SYSBP_0 TEMP_0 WEIGHT_7 SYSBP_7 TEMP_7
1 105.6 110 34.4 105.1 114 33.9
...

A straight CASETOVARS restructure creates all the columns needed for the
"wide" file above, however with 21 measurement days and up to 8 parameters
measured each day, there are a total of 124 day-by-parameter variables
created in the restructure and it's a big mess trying to name and label each
one with the correct day-by-parameter combination without making a mistake
(and I have over a dozen of these files to do in the same way).

Is there any way to do this "wide" restructure in such a way that the
variable naming uses the PARAM entries in the original file? Or
alternatively, to go straight to the "long" format? Any suggestions would be
appreciated.

Thanks,
Kylie.

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

Mark Webb-3

Re: stacked file restructure

Try this

SORT CASES BY day param .
CASESTOVARS
/ID = day
/INDEX = param
/GROUPBY = VARIABLE .

Kylie Lange wrote:

> Hi all,
>
> I am hoping for assistance with an awkward data file restructure. The
> original data has been provided in the following format (which I have dubbed
> "stacked"):
>
> ID DAY PARAM VALUE
> 1 0 WEIGHT 105.6
> 1 0 SYS BP 110
> 1 0 TEMP 34.4
> 1 7 WEIGHT 105.1
> 1 7 SYS BP 114
> 1 7 TEMP 33.9
> ...
>
> Ie, each measurement parameter has been listed as a separate row in the same
> variable, rather than getting its own variable (ie, the parameters are
> "stacked" on top of each other).
>
> I need to get this data into both of the standard formats:
> - "long" - one row per subject per day with separate variables per
> parameter, and
> - "wide" - one row per subject with separate variables per parameter per day
> (Obviously, once I can get it into either of the above then the other is
> trivial)
>
> That is:
>
> ID DAY WEIGHT SYSBP TEMP
> 1 0 105.6 110 34.4
> 1 7 105.1 114 33.9
> ...
>
> ID WEIGHT_0 SYSBP_0 TEMP_0 WEIGHT_7 SYSBP_7 TEMP_7
> 1 105.6 110 34.4 105.1 114 33.9
> ...
>
> A straight CASETOVARS restructure creates all the columns needed for the
> "wide" file above, however with 21 measurement days and up to 8 parameters
> measured each day, there are a total of 124 day-by-parameter variables
> created in the restructure and it's a big mess trying to name and label each
> one with the correct day-by-parameter combination without making a mistake
> (and I have over a dozen of these files to do in the same way).
>
> Is there any way to do this "wide" restructure in such a way that the
> variable naming uses the PARAM entries in the original file? Or
> alternatively, to go straight to the "long" format? Any suggestions would be
> appreciated.
>
> Thanks,
> Kylie.
>
> =====================
> 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
>
>

--
Mark Webb

+27 21 786 4379
+27 72 199 1000
Skype - webbmark
[hidden email]

=====================
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: stacked file restructure

In reply to this post by Kylie

Minor addition to the solution below, let it precede with:
compute param = replace(rtrim(param), " ", "_").

--- On Fri, 6/5/09, Mark Webb <[hidden email]> wrote:

> From: Mark Webb <[hidden email]>
> Subject: Re: stacked file restructure
> To: [hidden email]
> Date: Friday, June 5, 2009, 11:03 AM
> Try this
>
> SORT CASES BY day param .
> CASESTOVARS
> /ID = day
> /INDEX = param
> /GROUPBY = VARIABLE .
>
>
> Kylie Lange wrote:
> > Hi all,
> >
> > I am hoping for assistance with an awkward data file
> restructure. The
> > original data has been provided in the following
> format (which I have dubbed
> > "stacked"):
> >
> > ID DAY PARAM VALUE
> > 1 0
> WEIGHT 105.6
> > 1 0 SYS
> BP 110
> > 1 0 TEMP
> 34.4
> > 1 7
> WEIGHT 105.1
> > 1 7 SYS
> BP 114
> > 1 7 TEMP
> 33.9
> > ...
> >
> > Ie, each measurement parameter has been listed as a
> separate row in the same
> > variable, rather than getting its own variable (ie,
> the parameters are
> > "stacked" on top of each other).
> >
> > I need to get this data into both of the standard
> formats:
> > - "long" - one row per subject per day with separate
> variables per
> > parameter, and
> > - "wide" - one row per subject with separate variables
> per parameter per day
> > (Obviously, once I can get it into either of the above
> then the other is
> > trivial)
> >
> > That is:
> >
> > ID DAY WEIGHT SYSBP
> TEMP
> > 1 0
> 105.6
> 110 34.4
> > 1 7
> 105.1
> 114 33.9
> > ...
> >
> > ID WEIGHT_0 SYSBP_0 TEMP_0 WEIGHT_7
> SYSBP_7 TEMP_7
> > 1 105.6 110
> 34.4 105.1
> 114 33.9
> > ...
> >
> > A straight CASETOVARS restructure creates all the
> columns needed for the
> > "wide" file above, however with 21 measurement days
> and up to 8 parameters
> > measured each day, there are a total of 124
> day-by-parameter variables
> > created in the restructure and it's a big mess trying
> to name and label each
> > one with the correct day-by-parameter combination
> without making a mistake
> > (and I have over a dozen of these files to do in the
> same way).
> >
> > Is there any way to do this "wide" restructure in such
> a way that the
> > variable naming uses the PARAM entries in the original
> file? Or
> > alternatively, to go straight to the "long" format?
> Any suggestions would be
> > appreciated.
> >
> > Thanks,
> > Kylie.
> >
> > =====================
> > 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
> >
> >
>
> --
> Mark Webb
>
> +27 21 786 4379
> +27 72 199 1000
> Skype - webbmark
> [hidden email]
>
> =====================
> 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
>

Kylie

Re: stacked file restructure

Thanks Mark and Albert-Jan for your suggestions.

I get an error with the suggested code, possibly because I did not make it
clear enough that the file contains multiple subjects ('ID')? The error
message is:

"The INDEX values for case 2 have occurred before in the cases with the same
ID values. This command is not executed."

Any other suggestions would be appreciated...

Thanks,
Kylie.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Albert-jan Roskam
Sent: Friday, 5 June 2009 6:45 PM
To: [hidden email]
Subject: Re: stacked file restructure

Minor addition to the solution below, let it precede with:
compute param = replace(rtrim(param), " ", "_").

--- On Fri, 6/5/09, Mark Webb <[hidden email]> wrote:

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

Kylie

var names and labels stored in variables (was: stacked file restructure)

In reply to this post by Albert-Jan Roskam

Hi all,

I am still working on the restructuring problem I described earlier (see
below). I haven't been able to get restructure to automatically do what I
need, so I am now trying to approach the problem from a slightly different
angle.

To recap I have the following data:

ID DAY PARAM VALUE
1 0 WEIGHT 105.6
1 0 SYS BP 110
1 0 TEMP 34.4
1 7 WEIGHT 105.1
1 7 SYS BP 114
1 7 TEMP 33.9
...

I am trying to create the following:

ID WEIGHT_0 SYSBP_0 TEMP_0 WEIGHT_7 SYSBP_7 TEMP_7
1 105.6 110 34.4 105.1 114 33.9
...

A straight CASETOVARS creates the above variables, however they are simply
labeled VALUE.1 through to VALUE.124 (there are 124 combinations of the 21
DAYs and 8 PARAMs present in the file). The corresponding day and parameter
for each value are stored in the DAY.1 - DAY.124 and PARAM.1 - PARAM.124
variables. Ie,

ID VALUE.1 VALUE.2 ... DAY.1 DAY.2 ... PARAM.1 PARAM.2
1 105.6 110 ... 0 0 ... WEIGHT SYS BP
...

Can anyone help with syntax to rename and label the VALUE.1 - VALUE.124
variables based on the contents of the corresonding DAY and PARAM variables?
I have been looking at the nifty examples that use syntax to generate a
syntax file so perhaps that approach could be used to generate the
following?

VARIABLE LABELS VALUE.1 'WEIGHT DAY 0'
VALUE.2 'SYS BP DAY 0'.
RENAME VARIABLES (VALUE.1=WEIGHT.0) (VALUE.2=SYSBP.0).

As always, all suggestions would be appreciated.

Thanks,
Kylie.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Kylie Lange
Sent: Tuesday, 9 June 2009 10:13 AM
To: [hidden email]
Subject: Re: stacked file restructure

Thanks Mark and Albert-Jan for your suggestions.

I get an error with the suggested code, possibly because I did not make it
clear enough that the file contains multiple subjects ('ID')? The error
message is:

"The INDEX values for case 2 have occurred before in the cases with the same
ID values. This command is not executed."

Any other suggestions would be appreciated...

Thanks,
Kylie.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Albert-jan Roskam
Sent: Friday, 5 June 2009 6:45 PM
To: [hidden email]
Subject: Re: stacked file restructure

Minor addition to the solution below, let it precede with:
compute param = replace(rtrim(param), " ", "_").

--- On Fri, 6/5/09, Mark Webb <[hidden email]> wrote:

Albert-Jan Roskam

Re: var names and labels stored in variables (was: stacked file restructure)

Hi Kylie!

It seems that CASESTOVARS should be able to do this in one go, but well, below is a Python approach (just because it's nice to play with it! ;-).
I divided it into a number of functions, thus suppressing my tendency to put everything in one big chunk of code. Suggestions for improvement are very welcome, of course!

Cheers!!
Albert-Jan()

* sample data.
data list fixed / VALUE 1-3 (F) DAY 4-5 (F) PARAM 6-11 (A).
begin data
1050 WEIGHT
1101 SYS BP
end data.
compute id = 1.
casestovars / id = id.

* actual code.
begin program.
import spss, spssaux
# get data of first case.
def caseinfo ():
cursor=spss.Cursor()
firstcase = cursor.fetchone()
cursor.close()
return firstcase
# get data from variable dictionary.
def varinfo ():
return [v.VariableName for v in spssaux.VariableDict()]
# combine the two previous data.
def case_and_var_info ():
d = {}
for i in range(spss.GetVariableCount()):
d.update({varinfo()[i] : caseinfo()[i]})
return d
# dynamically create spss syntax.
# this assumes that the first var (the zeroeth var) is something else (in this case id), ie., not param.n, value.n, day.n
def make_sps ():
d = case_and_var_info ()
numrange = len([v.VariableName for v in spssaux.VariableDict() if v.VariableName.startswith("PARAM")]) # this should be 124 in your case.
for i in range(1, numrange+1):
param = d["PARAM."+str(i)]
day = str(int(d["DAY."+str(i)]))
new = param.replace(" ", "_") + "_" + day
old = "VALUE."+str(i)
spss.Submit("RENAME VARIABLES " + old + "=" + new + ".")
spss.Submit("VARIABLE LABEL "+ new +" '" + param.capitalize() + " of day " + day + "'.")
make_sps()
end program.

--- On Wed, 6/10/09, Kylie Lange <[hidden email]> wrote:

> From: Kylie Lange <[hidden email]>
> Subject: var names and labels stored in variables (was: stacked file restructure)
> To: [hidden email]
> Date: Wednesday, June 10, 2009, 8:16 AM
> Hi all,
>
> I am still working on the restructuring problem I described
> earlier (see
> below). I haven't been able to get restructure to
> automatically do what I
> need, so I am now trying to approach the problem from a
> slightly different
> angle.
>
> To recap I have the following data:
>
> ID DAY PARAM VALUE
> 1 0
> WEIGHT 105.6
> 1 0 SYS
> BP 110
> 1 0 TEMP
> 34.4
> 1 7
> WEIGHT 105.1
> 1 7 SYS
> BP 114
> 1 7 TEMP
> 33.9
> ...
>
> I am trying to create the following:
>
> ID WEIGHT_0 SYSBP_0 TEMP_0 WEIGHT_7
> SYSBP_7 TEMP_7
> 1 105.6 110
> 34.4 105.1
> 114 33.9
> ...
>
> A straight CASETOVARS creates the above variables, however
> they are simply
> labeled VALUE.1 through to VALUE.124 (there are 124
> combinations of the 21
> DAYs and 8 PARAMs present in the file). The corresponding
> day and parameter
> for each value are stored in the DAY.1 - DAY.124 and
> PARAM.1 - PARAM.124
> variables. Ie,
>
> ID VALUE.1 VALUE.2 ... DAY.1
> DAY.2 ... PARAM.1 PARAM.2
> 1 105.6 110
> ... 0 0
> ... WEIGHT SYS BP
> ...
>
> Can anyone help with syntax to rename and label the VALUE.1
> - VALUE.124
> variables based on the contents of the corresonding DAY and
> PARAM variables?
> I have been looking at the nifty examples that use syntax
> to generate a
> syntax file so perhaps that approach could be used to
> generate the
> following?
>
> VARIABLE LABELS VALUE.1 'WEIGHT DAY 0'
>
> VALUE.2 'SYS BP DAY 0'.
> RENAME VARIABLES (VALUE.1=WEIGHT.0) (VALUE.2=SYSBP.0).
>
> As always, all suggestions would be appreciated.
>
> Thanks,
> Kylie.
>
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]]
> On Behalf Of
> Kylie Lange
> Sent: Tuesday, 9 June 2009 10:13 AM
> To: [hidden email]
> Subject: Re: stacked file restructure
>
> Thanks Mark and Albert-Jan for your suggestions.
>
> I get an error with the suggested code, possibly because I
> did not make it
> clear enough that the file contains multiple subjects
> ('ID')? The error
> message is:
>
> "The INDEX values for case 2 have occurred before in the
> cases with the same
> ID values. This command is not executed."
>
> Any other suggestions would be appreciated...
>
> Thanks,
> Kylie.
>
>
> -----Original Message-----
> From: SPSSX(r) Discussion [mailto:[hidden email]]
> On Behalf Of
> Albert-jan Roskam
> Sent: Friday, 5 June 2009 6:45 PM
> To: [hidden email]
> Subject: Re: stacked file restructure
>
> Minor addition to the solution below, let it precede with:
> compute param = replace(rtrim(param), " ", "_").
>
>
> --- On Fri, 6/5/09, Mark Webb <[hidden email]>
> wrote:
>
> > From: Mark Webb <[hidden email]>
> > Subject: Re: stacked file restructure
> > To: [hidden email]
> > Date: Friday, June 5, 2009, 11:03 AM
> > Try this
> >
> > SORT CASES BY day param .
> > CASESTOVARS
> > /ID = day
> > /INDEX = param
> > /GROUPBY = VARIABLE .
> >
> >
> > Kylie Lange wrote:
> > > Hi all,
> > >
> > > I am hoping for assistance with an awkward data
> file
> > restructure. The
> > > original data has been provided in the following
> > format (which I have dubbed
> > > "stacked"):
> > >
> > > ID DAY PARAM VALUE
> > > 1 0
> > WEIGHT 105.6
> > > 1 0 SYS
> > BP 110
> > > 1 0 TEMP
> > 34.4
> > > 1 7
> > WEIGHT 105.1
> > > 1 7 SYS
> > BP 114
> > > 1 7 TEMP
> > 33.9
> > > ...
> > >
> > > Ie, each measurement parameter has been listed as
> a
> > separate row in the same
> > > variable, rather than getting its own variable
> (ie,
> > the parameters are
> > > "stacked" on top of each other).
> > >
> > > I need to get this data into both of the
> standard
> > formats:
> > > - "long" - one row per subject per day with
> separate
> > variables per
> > > parameter, and
> > > - "wide" - one row per subject with separate
> variables
> > per parameter per day
> > > (Obviously, once I can get it into either of the
> above
> > then the other is
> > > trivial)
> > >
> > > That is:
> > >
> > > ID DAY WEIGHT SYSBP
> > TEMP
> > > 1 0
> > 105.6
> > 110 34.4
> > > 1 7
> > 105.1
> > 114 33.9
> > > ...
> > >
> > > ID WEIGHT_0 SYSBP_0 TEMP_0
> WEIGHT_7
> > SYSBP_7 TEMP_7
> > > 1 105.6 110
> > 34.4 105.1
> > 114 33.9
> > > ...
> > >
> > > A straight CASETOVARS restructure creates all
> the
> > columns needed for the
> > > "wide" file above, however with 21 measurement
> days
> > and up to 8 parameters
> > > measured each day, there are a total of 124
> > day-by-parameter variables
> > > created in the restructure and it's a big mess
> trying
> > to name and label each
> > > one with the correct day-by-parameter
> combination
> > without making a mistake
> > > (and I have over a dozen of these files to do in
> the
> > same way).
> > >
> > > Is there any way to do this "wide" restructure in
> such
> > a way that the
> > > variable naming uses the PARAM entries in the
> original
> > file? Or
> > > alternatively, to go straight to the "long"
> format?
> > Any suggestions would be
> > > appreciated.
> > >
> > > Thanks,
> > > Kylie.
> > >
> > > =====================
> > > 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
> > >
> > >
> >
> > --
> > Mark Webb
> >
> > +27 21 786 4379
> > +27 72 199 1000
> > Skype - webbmark
> > [hidden email]
> >
> > =====================
> > 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
>
> =====================
> 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
>

Kylie

Re: var names and labels stored in variables (was: stacked file restructure)

Hi Albert-Jan,

Thanks so much, this is great. It is working perfectly on the simpler files,
however I have run into an additional complication. Some of the data files have
negative values in the DAYS variable. This appears to be causing the following
error, as it tries to include the - symbol in the new variable name:

>Error # 5219 in column 46. Text: -14
>Invalid symbol in the RENAME new variable list. PASW Statistics encountered
>something other than a variable name or the TO keyword.
>This command not executed.

Is it possible to modify the code to handle this case? The new variable name
could be PARAM_negDAY. I'm hoping that it will only need a simple IF check
before creating "new"?

Thanks again,
Kylie.

Quoting Albert-jan Roskam <[hidden email]>:

> Hi Kylie!
>
> It seems that CASESTOVARS should be able to do this in one go, but well,
> below is a Python approach (just because it's nice to play with it! ;-).
> I divided it into a number of functions, thus suppressing my tendency to put
> everything in one big chunk of code. Suggestions for improvement are very
> welcome, of course!
>
> Cheers!!
> Albert-Jan()
>
> * sample data.
> data list fixed / VALUE 1-3 (F) DAY 4-5 (F) PARAM 6-11 (A).
> begin data
> 1050 WEIGHT
> 1101 SYS BP
> end data.
> compute id = 1.
> casestovars / id = id.
>
> * actual code.
> begin program.
> import spss, spssaux
> # get data of first case.
> def caseinfo ():
> cursor=spss.Cursor()
> firstcase = cursor.fetchone()
> cursor.close()
> return firstcase
> # get data from variable dictionary.
> def varinfo ():
> return [v.VariableName for v in spssaux.VariableDict()]
> # combine the two previous data.
> def case_and_var_info ():
> d = {}
> for i in range(spss.GetVariableCount()):
> d.update({varinfo()[i] : caseinfo()[i]})
> return d
> # dynamically create spss syntax.
> # this assumes that the first var (the zeroeth var) is something else (in
> this case id), ie., not param.n, value.n, day.n
> def make_sps ():
> d = case_and_var_info ()
> numrange = len([v.VariableName for v in spssaux.VariableDict() if
> v.VariableName.startswith("PARAM")]) # this should be 124 in your case.
> for i in range(1, numrange+1):
> param = d["PARAM."+str(i)]
> day = str(int(d["DAY."+str(i)]))
> new = param.replace(" ", "_") + "_" + day
> old = "VALUE."+str(i)
> spss.Submit("RENAME VARIABLES " + old + "=" + new + ".")
> spss.Submit("VARIABLE LABEL "+ new +" '" + param.capitalize() + " of day
> " + day + "'.")
> make_sps()
> end program.
>
>
> --- On Wed, 6/10/09, Kylie Lange <[hidden email]> wrote:
>
> > From: Kylie Lange <[hidden email]>
> > Subject: var names and labels stored in variables (was: stacked file
> restructure)
> > To: [hidden email]
> > Date: Wednesday, June 10, 2009, 8:16 AM
> > Hi all,
> >
> > I am still working on the restructuring problem I described
> > earlier (see
> > below). I haven't been able to get restructure to
> > automatically do what I
> > need, so I am now trying to approach the problem from a
> > slightly different
> > angle.
> >
> > To recap I have the following data:
> >
> > ID DAY PARAM VALUE
> > 1 0
> > WEIGHT 105.6
> > 1 0 SYS
> > BP 110
> > 1 0 TEMP
> > 34.4
> > 1 7
> > WEIGHT 105.1
> > 1 7 SYS
> > BP 114
> > 1 7 TEMP
> > 33.9
> > ...
> >
> > I am trying to create the following:
> >
> > ID WEIGHT_0 SYSBP_0 TEMP_0 WEIGHT_7
> > SYSBP_7 TEMP_7
> > 1 105.6 110
> > 34.4 105.1
> > 114 33.9
> > ...
> >
> > A straight CASETOVARS creates the above variables, however
> > they are simply
> > labeled VALUE.1 through to VALUE.124 (there are 124
> > combinations of the 21
> > DAYs and 8 PARAMs present in the file). The corresponding
> > day and parameter
> > for each value are stored in the DAY.1 - DAY.124 and
> > PARAM.1 - PARAM.124
> > variables. Ie,
> >
> > ID VALUE.1 VALUE.2 ... DAY.1
> > DAY.2 ... PARAM.1 PARAM.2
> > 1 105.6 110
> > ... 0 0
> > ... WEIGHT SYS BP
> > ...
> >
> > Can anyone help with syntax to rename and label the VALUE.1
> > - VALUE.124
> > variables based on the contents of the corresonding DAY and
> > PARAM variables?
> > I have been looking at the nifty examples that use syntax
> > to generate a
> > syntax file so perhaps that approach could be used to
> > generate the
> > following?
> >
> > VARIABLE LABELS VALUE.1 'WEIGHT DAY 0'
> >
> > VALUE.2 'SYS BP DAY 0'.
> > RENAME VARIABLES (VALUE.1=WEIGHT.0) (VALUE.2=SYSBP.0).
> >
> > As always, all suggestions would be appreciated.
> >
> > Thanks,
> > Kylie.
> >
> >
> > -----Original Message-----
> > From: SPSSX(r) Discussion [mailto:[hidden email]]
> > On Behalf Of
> > Kylie Lange
> > Sent: Tuesday, 9 June 2009 10:13 AM
> > To: [hidden email]
> > Subject: Re: stacked file restructure
> >
> > Thanks Mark and Albert-Jan for your suggestions.
> >
> > I get an error with the suggested code, possibly because I
> > did not make it
> > clear enough that the file contains multiple subjects
> > ('ID')? The error
> > message is:
> >
> > "The INDEX values for case 2 have occurred before in the
> > cases with the same
> > ID values. This command is not executed."
> >
> > Any other suggestions would be appreciated...
> >
> > Thanks,
> > Kylie.
> >
> >
> > -----Original Message-----
> > From: SPSSX(r) Discussion [mailto:[hidden email]]
> > On Behalf Of
> > Albert-jan Roskam
> > Sent: Friday, 5 June 2009 6:45 PM
> > To: [hidden email]
> > Subject: Re: stacked file restructure
> >
> > Minor addition to the solution below, let it precede with:
> > compute param = replace(rtrim(param), " ", "_").
> >
> >
> > --- On Fri, 6/5/09, Mark Webb <[hidden email]>
> > wrote:
> >
> > > From: Mark Webb <[hidden email]>
> > > Subject: Re: stacked file restructure
> > > To: [hidden email]
> > > Date: Friday, June 5, 2009, 11:03 AM
> > > Try this
> > >
> > > SORT CASES BY day param .
> > > CASESTOVARS
> > > /ID = day
> > > /INDEX = param
> > > /GROUPBY = VARIABLE .
> > >
> > >
> > > Kylie Lange wrote:
> > > > Hi all,
> > > >
> > > > I am hoping for assistance with an awkward data
> > file
> > > restructure. The
> > > > original data has been provided in the following
> > > format (which I have dubbed
> > > > "stacked"):
> > > >
> > > > ID DAY PARAM VALUE
> > > > 1 0
> > > WEIGHT 105.6
> > > > 1 0 SYS
> > > BP 110
> > > > 1 0 TEMP
> > > 34.4
> > > > 1 7
> > > WEIGHT 105.1
> > > > 1 7 SYS
> > > BP 114
> > > > 1 7 TEMP
> > > 33.9
> > > > ...
> > > >
> > > > Ie, each measurement parameter has been listed as
> > a
> > > separate row in the same
> > > > variable, rather than getting its own variable
> > (ie,
> > > the parameters are
> > > > "stacked" on top of each other).
> > > >
> > > > I need to get this data into both of the
> > standard
> > > formats:
> > > > - "long" - one row per subject per day with
> > separate
> > > variables per
> > > > parameter, and
> > > > - "wide" - one row per subject with separate
> > variables
> > > per parameter per day
> > > > (Obviously, once I can get it into either of the
> > above
> > > then the other is
> > > > trivial)
> > > >
> > > > That is:
> > > >
> > > > ID DAY WEIGHT SYSBP
> > > TEMP
> > > > 1 0
> > > 105.6
> > > 110 34.4
> > > > 1 7
> > > 105.1
> > > 114 33.9
> > > > ...
> > > >
> > > > ID WEIGHT_0 SYSBP_0 TEMP_0
> > WEIGHT_7
> > > SYSBP_7 TEMP_7
> > > > 1 105.6 110
> > > 34.4 105.1
> > > 114 33.9
> > > > ...
> > > >
> > > > A straight CASETOVARS restructure creates all
> > the
> > > columns needed for the
> > > > "wide" file above, however with 21 measurement
> > days
> > > and up to 8 parameters
> > > > measured each day, there are a total of 124
> > > day-by-parameter variables
> > > > created in the restructure and it's a big mess
> > trying
> > > to name and label each
> > > > one with the correct day-by-parameter
> > combination
> > > without making a mistake
> > > > (and I have over a dozen of these files to do in
> > the
> > > same way).
> > > >
> > > > Is there any way to do this "wide" restructure in
> > such
> > > a way that the
> > > > variable naming uses the PARAM entries in the
> > original
> > > file? Or
> > > > alternatively, to go straight to the "long"
> > format?
> > > Any suggestions would be
> > > > appreciated.
> > > >
> > > > Thanks,
> > > > Kylie.
> > > >
> > > > =====================
> > > > 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
> > > >
> > > >
> > >
> > > --
> > > Mark Webb
> > >
> > > +27 21 786 4379
> > > +27 72 199 1000
> > > Skype - webbmark
> > > [hidden email]
> > >
> > > =====================
> > > 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
> >
> > =====================
> > 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

Albert-Jan Roskam

Re: var names and labels stored in variables (was: stacked file restructure)

In reply to this post by Kylie

Hmmm, untested, but if you replace:
new = param.replace(" ", "_") + "_" + day
With:
new = param.replace(" ", "_") + "_" + day.replace("-","neg")

If might work!

Good luck!
Albert-Jan

--- On Thu, 6/11/09, Kylie Lange <[hidden email]> wrote:

> From: Kylie Lange <[hidden email]>
> Subject: Re: var names and labels stored in variables (was: stacked file restructure)
> To: [hidden email]
> Date: Thursday, June 11, 2009, 3:08 AM
> Hi Albert-Jan,
>
> Thanks so much, this is great. It is working perfectly on
> the simpler files,
> however I have run into an additional complication. Some of
> the data files have
> negative values in the DAYS variable. This appears to be
> causing the following
> error, as it tries to include the - symbol in the new
> variable name:
>
> >Error # 5219 in column 46. Text: -14
> >Invalid symbol in the RENAME new variable list.
> PASW Statistics encountered
> >something other than a variable name or the TO
> keyword.
> >This command not executed.
>
> Is it possible to modify the code to handle this case? The
> new variable name
> could be PARAM_negDAY. I'm hoping that it will only need a
> simple IF check
> before creating "new"?
>
> Thanks again,
> Kylie.
>
>
> Quoting Albert-jan Roskam <[hidden email]>:
>
> > Hi Kylie!
> >
> > It seems that CASESTOVARS should be able to do this in
> one go, but well,
> > below is a Python approach (just because it's nice to
> play with it! ;-).
> > I divided it into a number of functions, thus
> suppressing my tendency to put
> > everything in one big chunk of code. Suggestions for
> improvement are very
> > welcome, of course!
> >
> > Cheers!!
> > Albert-Jan()
> >
> > * sample data.
> > data list fixed / VALUE 1-3 (F) DAY 4-5 (F) PARAM 6-11
> (A).
> > begin data
> > 1050 WEIGHT
> > 1101 SYS BP
> > end data.
> > compute id = 1.
> > casestovars / id = id.
> >
> > * actual code.
> > begin program.
> > import spss, spssaux
> > # get data of first case.
> > def caseinfo ():
> > cursor=spss.Cursor()
> > firstcase = cursor.fetchone()
> > cursor.close()
> > return firstcase
> > # get data from variable dictionary.
> > def varinfo ():
> > return [v.VariableName for v in
> spssaux.VariableDict()]
> > # combine the two previous data.
> > def case_and_var_info ():
> > d = {}
> > for i in
> range(spss.GetVariableCount()):
> > d.update({varinfo()[i] :
> caseinfo()[i]})
> > return d
> > # dynamically create spss syntax.
> > # this assumes that the first var (the zeroeth var) is
> something else (in
> > this case id), ie., not param.n, value.n, day.n
> > def make_sps ():
> > d = case_and_var_info ()
> > numrange = len([v.VariableName for v
> in spssaux.VariableDict() if
> > v.VariableName.startswith("PARAM")]) # this should be
> 124 in your case.
> > for i in range(1, numrange+1):
> > param = d["PARAM."+str(i)]
> > day =
> str(int(d["DAY."+str(i)]))
> > new = param.replace(" ", "_")
> + "_" + day
> > old = "VALUE."+str(i)
> > spss.Submit("RENAME VARIABLES
> " + old + "=" + new + ".")
> > spss.Submit("VARIABLE LABEL "+
> new +" '" + param.capitalize() + " of day
> > " + day + "'.")
> > make_sps()
> > end program.
> >
> >
> > --- On Wed, 6/10/09, Kylie Lange <[hidden email]>
> wrote:
> >
> > > From: Kylie Lange <[hidden email]>
> > > Subject: var names and labels stored in variables
> (was: stacked file
> > restructure)
> > > To: [hidden email]
> > > Date: Wednesday, June 10, 2009, 8:16 AM
> > > Hi all,
> > >
> > > I am still working on the restructuring problem I
> described
> > > earlier (see
> > > below). I haven't been able to get restructure
> to
> > > automatically do what I
> > > need, so I am now trying to approach the problem
> from a
> > > slightly different
> > > angle.
> > >
> > > To recap I have the following data:
> > >
> > > ID DAY PARAM VALUE
> > > 1 0
> > > WEIGHT 105.6
> > > 1 0 SYS
> > > BP 110
> > > 1 0 TEMP
> > > 34.4
> > > 1 7
> > > WEIGHT 105.1
> > > 1 7 SYS
> > > BP 114
> > > 1 7 TEMP
> > > 33.9
> > > ...
> > >
> > > I am trying to create the following:
> > >
> > > ID WEIGHT_0 SYSBP_0 TEMP_0
> WEIGHT_7
> > > SYSBP_7 TEMP_7
> > > 1 105.6 110
> > > 34.4 105.1
> > > 114 33.9
> > > ...
> > >
> > > A straight CASETOVARS creates the above
> variables, however
> > > they are simply
> > > labeled VALUE.1 through to VALUE.124 (there are
> 124
> > > combinations of the 21
> > > DAYs and 8 PARAMs present in the file). The
> corresponding
> > > day and parameter
> > > for each value are stored in the DAY.1 - DAY.124
> and
> > > PARAM.1 - PARAM.124
> > > variables. Ie,
> > >
> > > ID VALUE.1 VALUE.2 ... DAY.1
> > > DAY.2 ... PARAM.1 PARAM.2
> > > 1 105.6 110
> > > ... 0
> 0
> > > ... WEIGHT SYS BP
> > > ...
> > >
> > > Can anyone help with syntax to rename and label
> the VALUE.1
> > > - VALUE.124
> > > variables based on the contents of the
> corresonding DAY and
> > > PARAM variables?
> > > I have been looking at the nifty examples that
> use syntax
> > > to generate a
> > > syntax file so perhaps that approach could be
> used to
> > > generate the
> > > following?
> > >
> > > VARIABLE LABELS VALUE.1 'WEIGHT DAY 0'
> > >
> > > VALUE.2 'SYS BP DAY 0'.
> > > RENAME VARIABLES (VALUE.1=WEIGHT.0)
> (VALUE.2=SYSBP.0).
> > >
> > > As always, all suggestions would be appreciated.
> > >
> > > Thanks,
> > > Kylie.
> > >
> > >
> > > -----Original Message-----
> > > From: SPSSX(r) Discussion [mailto:[hidden email]]
> > > On Behalf Of
> > > Kylie Lange
> > > Sent: Tuesday, 9 June 2009 10:13 AM
> > > To: [hidden email]
> > > Subject: Re: stacked file restructure
> > >
> > > Thanks Mark and Albert-Jan for your suggestions.
> > >
> > > I get an error with the suggested code, possibly
> because I
> > > did not make it
> > > clear enough that the file contains multiple
> subjects
> > > ('ID')? The error
> > > message is:
> > >
> > > "The INDEX values for case 2 have occurred before
> in the
> > > cases with the same
> > > ID values. This command is not executed."
> > >
> > > Any other suggestions would be appreciated...
> > >
> > > Thanks,
> > > Kylie.
> > >
> > >
> > > -----Original Message-----
> > > From: SPSSX(r) Discussion [mailto:[hidden email]]
> > > On Behalf Of
> > > Albert-jan Roskam
> > > Sent: Friday, 5 June 2009 6:45 PM
> > > To: [hidden email]
> > > Subject: Re: stacked file restructure
> > >
> > > Minor addition to the solution below, let it
> precede with:
> > > compute param = replace(rtrim(param), " ", "_").
> > >
> > >
> > > --- On Fri, 6/5/09, Mark Webb <[hidden email]>
> > > wrote:
> > >
> > > > From: Mark Webb <[hidden email]>
> > > > Subject: Re: stacked file restructure
> > > > To: [hidden email]
> > > > Date: Friday, June 5, 2009, 11:03 AM
> > > > Try this
> > > >
> > > > SORT CASES BY day param .
> > > > CASESTOVARS
> > > > /ID = day
> > > > /INDEX = param
> > > > /GROUPBY = VARIABLE .
> > > >
> > > >
> > > > Kylie Lange wrote:
> > > > > Hi all,
> > > > >
> > > > > I am hoping for assistance with an
> awkward data
> > > file
> > > > restructure. The
> > > > > original data has been provided in the
> following
> > > > format (which I have dubbed
> > > > > "stacked"):
> > > > >
> > > > > ID DAY
> PARAM VALUE
> > > > > 1 0
> > > > WEIGHT 105.6
> > > > > 1 0 SYS
> > > > BP 110
> > > > > 1 0 TEMP
> > > > 34.4
> > > > > 1 7
> > > > WEIGHT 105.1
> > > > > 1 7 SYS
> > > > BP 114
> > > > > 1 7 TEMP
> > > > 33.9
> > > > > ...
> > > > >
> > > > > Ie, each measurement parameter has been
> listed as
> > > a
> > > > separate row in the same
> > > > > variable, rather than getting its own
> variable
> > > (ie,
> > > > the parameters are
> > > > > "stacked" on top of each other).
> > > > >
> > > > > I need to get this data into both of
> the
> > > standard
> > > > formats:
> > > > > - "long" - one row per subject per day
> with
> > > separate
> > > > variables per
> > > > > parameter, and
> > > > > - "wide" - one row per subject with
> separate
> > > variables
> > > > per parameter per day
> > > > > (Obviously, once I can get it into
> either of the
> > > above
> > > > then the other is
> > > > > trivial)
> > > > >
> > > > > That is:
> > > > >
> > > > > ID DAY
> WEIGHT SYSBP
> > > > TEMP
> > > > > 1 0
> > > > 105.6
> > > > 110 34.4
> > > > > 1 7
> > > > 105.1
> > > > 114 33.9
> > > > > ...
> > > > >
> > > > > ID WEIGHT_0 SYSBP_0
> TEMP_0
> > > WEIGHT_7
> > > > SYSBP_7 TEMP_7
> > > > > 1 105.6
> 110
> > > > 34.4 105.1
> > > > 114 33.9
> > > > > ...
> > > > >
> > > > > A straight CASETOVARS restructure
> creates all
> > > the
> > > > columns needed for the
> > > > > "wide" file above, however with 21
> measurement
> > > days
> > > > and up to 8 parameters
> > > > > measured each day, there are a total of
> 124
> > > > day-by-parameter variables
> > > > > created in the restructure and it's a
> big mess
> > > trying
> > > > to name and label each
> > > > > one with the correct day-by-parameter
> > > combination
> > > > without making a mistake
> > > > > (and I have over a dozen of these files
> to do in
> > > the
> > > > same way).
> > > > >
> > > > > Is there any way to do this "wide"
> restructure in
> > > such
> > > > a way that the
> > > > > variable naming uses the PARAM entries
> in the
> > > original
> > > > file? Or
> > > > > alternatively, to go straight to the
> "long"
> > > format?
> > > > Any suggestions would be
> > > > > appreciated.
> > > > >
> > > > > Thanks,
> > > > > Kylie.
> > > > >
> > > > > =====================
> > > > > 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
> > > > >
> > > > >
> > > >
> > > > --
> > > > Mark Webb
> > > >
> > > > +27 21 786 4379
> > > > +27 72 199 1000
> > > > Skype - webbmark
> > > > [hidden email]
> > > >
> > > > =====================
> > > > 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
> > >
> > > =====================
> > > 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
>
> =====================
> 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: var names and labels stored in variables (was: stacked file restructure)

I've been a ambivalent spectator on this question so I certainly might've
missed something important. Kylie, from your message today, it seems that
everything works fine except when the days value is negative. If that is
true then I think the problem might be that '-' is not an allowed character
in a variable name. \

How would it work if you converted the days variable from numeric to string
and replaced the '-' with an allowed character BEFORE you did the
casestovars? It seems like that 'ought' to work. I've never tried this but
I've also never had negative data values as indexes in a casestovars.

Gene Maguin

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