SPSSX Discussion

Variable labels in a restructured dataset

Classic

List

Threaded

10 messages Options

Ketty Noonan

Variable labels in a restructured dataset

Hello All,

I am working on a custom utility that will restructure data from cases to variables.

Original dataset:

ID Brand

1 1

1 2

1 3

2 1

3 4

Restructured dataset (Brand1,...,Brand4 will be a Yes/No)

ID Brand1 Brand2 Brand3 Brand4

1 1 1 1 0

2 1 0 0 0

3 0 0 0 1

My question is: How can I define variable labels in the restructure dataset? The variable labels of Brand1, Brand2, ... will be based on the value labels of Brand in the original dataset.

I know I can do it by using this syntax.

variable label brand1 'Kelloggs'.

variable label brand2 'Kashi'

variable label brand3 'Nestle'.

variable label brand4 'Poptart'.

Is there any macro to this is automatically? The problem is that my syntax needs to be flexible and needs to work for any number of brands and any types of datasets.

Thank you!

Ketty

David Marso

Re: Variable labels in a restructured dataset

Administrator

You will want to study the scripting APIs.
see python and the 'VB like' scripting language documentation.
---

Ketty Noonan wrote

Hello All,
I am working on a custom utility that will restructure data from cases to variables.
Original dataset:ID Brand1 11 21 32 13 4
Restructured dataset (Brand1,...,Brand4 will be a Yes/No)
ID Brand1 Brand2 Brand3 Brand41 1 1 1 02 1 0 0 03 0 0 0 1
My question is: How can I define variable labels in the restructure dataset? The variable labels of Brand1, Brand2, ... will be based on the value labels of Brand in the original dataset.
I know I can do it by using this syntax.variable label brand1 'Kelloggs'.variable label brand2 'Kashi'variable label brand3 'Nestle'.variable label brand4 'Poptart'.
Is there any macro to this is automatically? The problem is that my syntax needs to be flexible and needs to work for any number of brands and any types of datasets.
Thank you!Ketty

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?"

Bruce Weaver

Re: Variable labels in a restructured dataset

Administrator

David, I know you now thoroughly despise it (and Jon loathes it even more than you do), but this strikes me as a case where your old disavowed method of using WRITE OUTFILE to produce a syntax file, and then running it via INCLUDE/INSERT FILE would probably be a lot easier (assuming Ketty is not well-versed in "scripting APIs" etc). If Ketty's original file has value labels for variable BRAND, the labels could be recovered easily via the VALUELABEL function.

For example (avert your eyes, David & Jon):

* Run the following with the original dataset active.
* This assumes that VALUE LABELS have been assigned for variable Brand.

* Keep only one record per Brand.

sort cases by Brand.
match files file = * / FIRST = FirstRec / BY Brand.
execute.
select if FirstRec.
execute.

* Get the maximum brand number.
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES
/BREAK=
/MaxBrand = MAX(Brand).

string VarName (A8) / BrandLabel (A15).
compute VarName = REPLACE(CONCAT("Brand",string(Brand,f3.0))," ","").
compute BrandLabel = ValueLabel(Brand).
execute.
list.

* Write the VARIABLE LABELS command to a file.

do if $casenum EQ 1.
+ WRITE OUTFILE = "C:\TEMP\MyVarLabels.sps" / "VARIABLE LABELS".
end if.
WRITE OUTFILE "C:\TEMP\MyVarLabels.sps" / " ",Varname, " '",BrandLabel,"'".
do if Brand EQ MaxBrand.
+ WRITE OUTFILE = "C:\TEMP\MyVarLabels.sps" / ".".
end if.
execute.

* Now activate the restructured data file and run the following.

INSERT FILE = "C:\TEMP\MyVarLabels.sps".
* Clean up the junk.
ERASE FILE = "C:\TEMP\MyVarLabels.sps".

Donning my flame-retardant suit and ducking for cover...

David Marso wrote

You will want to study the scripting APIs.
see python and the 'VB like' scripting language documentation.
---

Ketty Noonan wrote

Hello All,
I am working on a custom utility that will restructure data from cases to variables.
Original dataset:ID Brand1 11 21 32 13 4
Restructured dataset (Brand1,...,Brand4 will be a Yes/No)
ID Brand1 Brand2 Brand3 Brand41 1 1 1 02 1 0 0 03 0 0 0 1
My question is: How can I define variable labels in the restructure dataset? The variable labels of Brand1, Brand2, ... will be based on the value labels of Brand in the original dataset.
I know I can do it by using this syntax.variable label brand1 'Kelloggs'.variable label brand2 'Kashi'variable label brand3 'Nestle'.variable label brand4 'Poptart'.
Is there any macro to this is automatically? The problem is that my syntax needs to be flexible and needs to work for any number of brands and any types of datasets.
Thank you!Ketty

--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

David Marso

Re: Variable labels in a restructured dataset

Administrator

This post was updated on .

EDITED: Had a bad value label for category 5 and that Fubed the resulting outcome.
-----------------------------------------------------------------------------------------------
I had thought of the VALUELABEL function after posting.
DATA LIST FREE / id x.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.

VALUE LABELS
x 1 'Category_1' 2 'Category_2' 3 'Category_3' 4 'Category_4' 5 'Category_5' 6 'Category_6'.
STRING OLD_Labels (A64).
COMPUTE OLD_Labels=VALUELABEL(x).
CASESTOVARS ID=ID / INDEX=Old_Labels .
RECODE Category_1 TO Category_6 (SYSMIS=0)(ELSE=1).
LIST.

id Category_1 Category_2 Category_3 Category_4 Category_5 Category_6

1.0000 1.0000 1.0000 .0000 1.0000 .0000 .0000
2.0000 .0000 1.0000 .0000 1.0000 1.0000 .0000
3.0000 1.0000 1.0000 1.0000 .0000 .0000 1.0000
4.0000 1.0000 .0000 .0000 .0000 .0000 .0000

Number of cases read: 4 Number of cases listed: 4

Bruce Weaver

Re: Variable labels in a restructured dataset

Administrator

Fair enough. When I posted, I had a nagging feeling there was a way to do this with CASESTOVARS (instead of that other method), but I just didn't think about it long enough! ;-)

David Marso wrote

EDITED: Had a bad value label for category 5 and that Fubed the resulting outcome.
-----------------------------------------------------------------------------------------------
I had thought of the VALUELABEL function after posting.
DATA LIST FREE / id x.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.

VALUE LABELS
x 1 'Category_1' 2 'Category_2' 3 'Category_3' 4 'Category_4' 5 'Category_5' 6 'Category_6'.
STRING OLD_Labels (A64).
COMPUTE OLD_Labels=VALUELABEL(x).
CASESTOVARS ID=ID / INDEX=Old_Labels .
RECODE Category_1 TO Category_6 (SYSMIS=0)(ELSE=1).
LIST.

id Category_1 Category_2 Category_3 Category_4 Category_5 Category_6

1.0000 1.0000 1.0000 .0000 1.0000 .0000 .0000
2.0000 .0000 1.0000 .0000 1.0000 1.0000 .0000
3.0000 1.0000 1.0000 1.0000 .0000 .0000 1.0000
4.0000 1.0000 .0000 .0000 .0000 .0000 .0000

Number of cases read: 4 Number of cases listed: 4

Bruce Weaver

Re: Variable labels in a restructured dataset

Administrator

In reply to this post by David Marso

Here's a slightly more general version of David's syntax that can cope with Brand labels that include spaces, for example. Some of the important changes in bold face.

DATA LIST FREE / id Brand.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.
FORMATS id Brand (f2.0).
VALUE LABELS Brand
1 'Kelloggs'
2 'Kashi'
3 'Nestle'
4 'Poptart'
5 'Brand 5'
6 'Some other brand'
.
STRING OLD_Labels (A64).
* Some brand labels may include spaces. Replace spaces with "_".
COMPUTE OLD_Labels=REPLACE(VALUELABEL(Brand)," ","_").
* Restructure the file.
CASESTOVARS ID=ID / INDEX=Old_Labels .
* Create two place-holder variables to use in the upcoming RECODE.
NUMERIC junk1 junk2(f1).
* Position the place-holder variables.
MATCH FILES file = * / keep = id junk1 all.
EXECUTE.
RECODE junk1 TO junk2 (SYSMIS=0)(ELSE=1).
EXECUTE.
DELETE VARIABLES junk1 junk2.
LIST.

OUTPUT:

id Brand_5 Kashi Kelloggs Nestle Poptart Some_other_brand

1 0 1 1 0 1 0
2 1 1 0 0 1 0
3 0 1 1 1 0 1
4 0 0 1 0 0 0

Number of cases read: 4 Number of cases listed: 4

David Marso wrote

EDITED: Had a bad value label for category 5 and that Fubed the resulting outcome.
-----------------------------------------------------------------------------------------------
I had thought of the VALUELABEL function after posting.
DATA LIST FREE / id x.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.

VALUE LABELS
x 1 'Category_1' 2 'Category_2' 3 'Category_3' 4 'Category_4' 5 'Category_5' 6 'Category_6'.
STRING OLD_Labels (A64).
COMPUTE OLD_Labels=VALUELABEL(x).
CASESTOVARS ID=ID / INDEX=Old_Labels .
RECODE Category_1 TO Category_6 (SYSMIS=0)(ELSE=1).
LIST.

id Category_1 Category_2 Category_3 Category_4 Category_5 Category_6

1.0000 1.0000 1.0000 .0000 1.0000 .0000 .0000
2.0000 .0000 1.0000 .0000 1.0000 1.0000 .0000
3.0000 1.0000 1.0000 1.0000 .0000 .0000 1.0000
4.0000 1.0000 .0000 .0000 .0000 .0000 .0000

Number of cases read: 4 Number of cases listed: 4

David Marso

Re: Variable labels in a restructured dataset

Administrator

Nice little touch up!
--

Bruce Weaver wrote

Here's a slightly more general version of David's syntax that can cope with Brand labels that include spaces, for example. Some of the important changes in bold face.

DATA LIST FREE / id Brand.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.
FORMATS id Brand (f2.0).
VALUE LABELS Brand
1 'Kelloggs'
2 'Kashi'
3 'Nestle'
4 'Poptart'
5 'Brand 5'
6 'Some other brand'
.
STRING OLD_Labels (A64).
* Some brand labels may include spaces. Replace spaces with "_".
COMPUTE OLD_Labels=REPLACE(VALUELABEL(Brand)," ","_").
* Restructure the file.
CASESTOVARS ID=ID / INDEX=Old_Labels .
* Create two place-holder variables to use in the upcoming RECODE.
NUMERIC junk1 junk2(f1).
* Position the place-holder variables.
MATCH FILES file = * / keep = id junk1 all.
EXECUTE.
RECODE junk1 TO junk2 (SYSMIS=0)(ELSE=1).
EXECUTE.
DELETE VARIABLES junk1 junk2.
LIST.

OUTPUT:

id Brand_5 Kashi Kelloggs Nestle Poptart Some_other_brand

1 0 1 1 0 1 0
2 1 1 0 0 1 0
3 0 1 1 1 0 1
4 0 0 1 0 0 0

Number of cases read: 4 Number of cases listed: 4

David Marso wrote

EDITED: Had a bad value label for category 5 and that Fubed the resulting outcome.
-----------------------------------------------------------------------------------------------
I had thought of the VALUELABEL function after posting.
DATA LIST FREE / id x.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.

VALUE LABELS
x 1 'Category_1' 2 'Category_2' 3 'Category_3' 4 'Category_4' 5 'Category_5' 6 'Category_6'.
STRING OLD_Labels (A64).
COMPUTE OLD_Labels=VALUELABEL(x).
CASESTOVARS ID=ID / INDEX=Old_Labels .
RECODE Category_1 TO Category_6 (SYSMIS=0)(ELSE=1).
LIST.

id Category_1 Category_2 Category_3 Category_4 Category_5 Category_6

1.0000 1.0000 1.0000 .0000 1.0000 .0000 .0000
2.0000 .0000 1.0000 .0000 1.0000 1.0000 .0000
3.0000 1.0000 1.0000 1.0000 .0000 .0000 1.0000
4.0000 1.0000 .0000 .0000 .0000 .0000 .0000

Number of cases read: 4 Number of cases listed: 4

David Marso

Re: Variable labels in a restructured dataset

Administrator

Just reexamined the original OP request and it would appear that the desire was that VAL LABELS become VAR LABELS. Ergo:
1. Horrible hack you posted .
2. Scripting.

David Marso wrote

Nice little touch up!
--

Bruce Weaver wrote

Here's a slightly more general version of David's syntax that can cope with Brand labels that include spaces, for example. Some of the important changes in bold face.

DATA LIST FREE / id Brand.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.
FORMATS id Brand (f2.0).
VALUE LABELS Brand
1 'Kelloggs'
2 'Kashi'
3 'Nestle'
4 'Poptart'
5 'Brand 5'
6 'Some other brand'
.
STRING OLD_Labels (A64).
* Some brand labels may include spaces. Replace spaces with "_".
COMPUTE OLD_Labels=REPLACE(VALUELABEL(Brand)," ","_").
* Restructure the file.
CASESTOVARS ID=ID / INDEX=Old_Labels .
* Create two place-holder variables to use in the upcoming RECODE.
NUMERIC junk1 junk2(f1).
* Position the place-holder variables.
MATCH FILES file = * / keep = id junk1 all.
EXECUTE.
RECODE junk1 TO junk2 (SYSMIS=0)(ELSE=1).
EXECUTE.
DELETE VARIABLES junk1 junk2.
LIST.

OUTPUT:

id Brand_5 Kashi Kelloggs Nestle Poptart Some_other_brand

1 0 1 1 0 1 0
2 1 1 0 0 1 0
3 0 1 1 1 0 1
4 0 0 1 0 0 0

Number of cases read: 4 Number of cases listed: 4

David Marso wrote

EDITED: Had a bad value label for category 5 and that Fubed the resulting outcome.
-----------------------------------------------------------------------------------------------
I had thought of the VALUELABEL function after posting.
DATA LIST FREE / id x.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.

VALUE LABELS
x 1 'Category_1' 2 'Category_2' 3 'Category_3' 4 'Category_4' 5 'Category_5' 6 'Category_6'.
STRING OLD_Labels (A64).
COMPUTE OLD_Labels=VALUELABEL(x).
CASESTOVARS ID=ID / INDEX=Old_Labels .
RECODE Category_1 TO Category_6 (SYSMIS=0)(ELSE=1).
LIST.

id Category_1 Category_2 Category_3 Category_4 Category_5 Category_6

1.0000 1.0000 1.0000 .0000 1.0000 .0000 .0000
2.0000 .0000 1.0000 .0000 1.0000 1.0000 .0000
3.0000 1.0000 1.0000 1.0000 .0000 .0000 1.0000
4.0000 1.0000 .0000 .0000 .0000 .0000 .0000

Number of cases read: 4 Number of cases listed: 4

Art Kendall

Re: Variable labels in a restructured dataset

Have not thought this through, but what if one did this to generalize
autorecode ...
compute newvar = valuelabel(oldvar)
flip.

Art Kendall
Social Research Consultants

On 4/10/2013 7:32 PM, David Marso [via SPSSX Discussion] wrote:

Just reexamined the original OP request and it would appear that the desire was that VAL LABELS become VAR LABELS. Ergo:
1. Horrible hack you posted .
2. Scripting.

David Marso wrote

Nice little touch up!
--

Bruce Weaver wrote

Here's a slightly more general version of David's syntax that can cope with Brand labels that include spaces, for example. Some of the important changes in bold face.

DATA LIST FREE / id Brand.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.
FORMATS id Brand (f2.0).
VALUE LABELS Brand
1 'Kelloggs'
2 'Kashi'
3 'Nestle'
4 'Poptart'
5 'Brand 5'
6 'Some other brand'
.
STRING OLD_Labels (A64).
* Some brand labels may include spaces. Replace spaces with "_".
COMPUTE OLD_Labels=REPLACE(VALUELABEL(Brand)," ","_").
* Restructure the file.
CASESTOVARS ID=ID / INDEX=Old_Labels .
* Create two place-holder variables to use in the upcoming RECODE.
NUMERIC junk1 junk2(f1).
* Position the place-holder variables.
MATCH FILES file = * / keep = id junk1 all.
EXECUTE.
RECODE junk1 TO junk2 (SYSMIS=0)(ELSE=1).
EXECUTE.
DELETE VARIABLES junk1 junk2.
LIST.

OUTPUT:

id Brand_5 Kashi Kelloggs Nestle Poptart Some_other_brand

1 0 1 1 0 1 0
2 1 1 0 0 1 0
3 0 1 1 1 0 1
4 0 0 1 0 0 0

Number of cases read: 4 Number of cases listed: 4

David Marso wrote

EDITED: Had a bad value label for category 5 and that Fubed the resulting outcome.
-----------------------------------------------------------------------------------------------
I had thought of the VALUELABEL function after posting.
DATA LIST FREE / id x.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.

VALUE LABELS
x 1 'Category_1' 2 'Category_2' 3 'Category_3' 4 'Category_4' 5 'Category_5' 6 'Category_6'.
STRING OLD_Labels (A64).
COMPUTE OLD_Labels=VALUELABEL(x).
CASESTOVARS ID=ID / INDEX=Old_Labels .
RECODE Category_1 TO Category_6 (SYSMIS=0)(ELSE=1).
LIST.

id Category_1 Category_2 Category_3 Category_4 Category_5 Category_6

1.0000 1.0000 1.0000 .0000 1.0000 .0000 .0000
2.0000 .0000 1.0000 .0000 1.0000 1.0000 .0000
3.0000 1.0000 1.0000 1.0000 .0000 .0000 1.0000
4.0000 1.0000 .0000 .0000 .0000 .0000 .0000

Number of cases read: 4 Number of cases listed: 4

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?"

If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Variable-labels-in-a-restructured-dataset-tp5719380p5719391.html

To start a new topic under SPSSX Discussion, email [hidden email]
To unsubscribe from SPSSX Discussion, click here.
NAML

Art Kendall
Social Research Consultants

Bruce Weaver

Re: Variable labels in a restructured dataset

Administrator

In reply to this post by David Marso

Re your point 1 below, that may be. But at the end of the day, I think it accomplishes what Ketty asked for (assuming none of her value labels are too wonky). And I did tell you to avert your eyes. ;-)

I just made a quick preliminary attempt at the approach Art suggested (AUTORECODE + FLIP), but no joy yet.

David Marso wrote

Just reexamined the original OP request and it would appear that the desire was that VAL LABELS become VAR LABELS. Ergo:
1. Horrible hack you posted .
2. Scripting.

David Marso wrote

Nice little touch up!
--

Bruce Weaver wrote

Here's a slightly more general version of David's syntax that can cope with Brand labels that include spaces, for example. Some of the important changes in bold face.

DATA LIST FREE / id Brand.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.
FORMATS id Brand (f2.0).
VALUE LABELS Brand
1 'Kelloggs'
2 'Kashi'
3 'Nestle'
4 'Poptart'
5 'Brand 5'
6 'Some other brand'
.
STRING OLD_Labels (A64).
* Some brand labels may include spaces. Replace spaces with "_".
COMPUTE OLD_Labels=REPLACE(VALUELABEL(Brand)," ","_").
* Restructure the file.
CASESTOVARS ID=ID / INDEX=Old_Labels .
* Create two place-holder variables to use in the upcoming RECODE.
NUMERIC junk1 junk2(f1).
* Position the place-holder variables.
MATCH FILES file = * / keep = id junk1 all.
EXECUTE.
RECODE junk1 TO junk2 (SYSMIS=0)(ELSE=1).
EXECUTE.
DELETE VARIABLES junk1 junk2.
LIST.

OUTPUT:

id Brand_5 Kashi Kelloggs Nestle Poptart Some_other_brand

1 0 1 1 0 1 0
2 1 1 0 0 1 0
3 0 1 1 1 0 1
4 0 0 1 0 0 0

Number of cases read: 4 Number of cases listed: 4

David Marso wrote

EDITED: Had a bad value label for category 5 and that Fubed the resulting outcome.
-----------------------------------------------------------------------------------------------
I had thought of the VALUELABEL function after posting.
DATA LIST FREE / id x.
BEGIN DATA
1 1 1 2 1 4
2 2 2 4 2 5
3 1 3 2 3 3 3 6
4 1
END DATA.

VALUE LABELS
x 1 'Category_1' 2 'Category_2' 3 'Category_3' 4 'Category_4' 5 'Category_5' 6 'Category_6'.
STRING OLD_Labels (A64).
COMPUTE OLD_Labels=VALUELABEL(x).
CASESTOVARS ID=ID / INDEX=Old_Labels .
RECODE Category_1 TO Category_6 (SYSMIS=0)(ELSE=1).
LIST.

id Category_1 Category_2 Category_3 Category_4 Category_5 Category_6

1.0000 1.0000 1.0000 .0000 1.0000 .0000 .0000
2.0000 .0000 1.0000 .0000 1.0000 1.0000 .0000
3.0000 1.0000 1.0000 1.0000 .0000 .0000 1.0000
4.0000 1.0000 .0000 .0000 .0000 .0000 .0000

Number of cases read: 4 Number of cases listed: 4