SPSSX Discussion

Exporting SPSS value labels to a SQL Database

classic Classic list List threaded Threaded
♦
♦
 9 messages Options
Adam Troy
Reply | Threaded
Open this post in threaded view
♦
♦
|

Exporting SPSS value labels to a SQL Database

Adam Troy
Hello,

Has anyone come up with a solution for pushing/exporting both the variable data and value labels to a SQL database? It appears that only the data carries over which means people trying to chart the data from the database with third-party programs cannot see the variable value labels making the data uninterpretable.

The prevailing solution from my Google searches seems to be to just upload two versions of every variable (one with the quantitative data and one with the qualitative label as a string) within a single data file, but that does not seem optimal. Is there another lookup table one should create that links to the database file? A similar question was asked here:

http://stackoverflow.com/questions/8527800/dealing-with-spss-labels-when-exporting-to-a-database

Would appreciate any ideas! I'm using SPSS Version 21.

Thanks,

Adam Troy
Rick Oliver-3
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Exporting SPSS value labels to a SQL Database

Rick Oliver-3
You can create lookup tables for each set of value labels, using the variable value as the key. That may even less optimal if you have a large number of variables with value labels.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:        Adam Troy <[hidden email]>
To:        [hidden email],
Date:        04/02/2014 09:06 AM
Subject:        Exporting SPSS value labels to a SQL Database
Sent by:        "SPSSX(r) Discussion" <[hidden email]>



Hello,
Has anyone come up with a solution for pushing/exporting both the variable data and value labels to a SQL database? It appears that only the data carries over which means people trying to chart the data from the database with third-party programs cannot see the variable value labels making the data uninterpretable.

The prevailing solution from my Google searches seems to be to just upload two versions of every variable (one with the quantitative data and one with the qualitative label as a string) within a single data file, but that does not seem optimal. Is there another lookup table one should create that links to the database file? A similar question was asked here:

http://stackoverflow.com/questions/8527800/dealing-with-spss-labels-when-exporting-to-a-database
Would appreciate any ideas! I'm using SPSS Version 21.

Thanks,

Adam Troy
David Marso
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Exporting SPSS value labels to a SQL Database

David Marso
Administrator
I don't see any reason why a large number of variables presents any issue at all if one uses proper normalization in the database tables! How else would someone approach this?  Certainly not with a wide data structure!

NEW FILE.
DATASET CLOSE ALL.
/*<data simulation>*/.
MATRIX.
SAVE (TRUNC(UNIFORM(1000,100)*5 +1)) / OUTFILE * / VARIABLES x001 TO x100.
END MATRIX.

VALUE LABELS
 x001 TO x100 1 'value 1' 2 'value 2' 3 'value 3' 4 'value 4' 5 'value 5'.
/*</data simulation>*/.

/* FREQUENCIES x001 x100.

STRING lab001 TO lab100 (A8).
DO REPEAT x=x001 TO x100 / l=lab001 TO lab100.
COMPUTE l=VALUELABEL(x).
END REPEAT.
VARSTOCASES
 /MAKE value FROM   x001 TO x100
 /MAKE label FROM lab001 TO lab100
 /INDEX=Varname(value).
DATASET DECLARE table.
AGGREGATE OUTFILE  table /BREAK varname value /LABEL=FIRST(label).


Rick Oliver wrote
You can create lookup tables for each set of value labels, using the
variable value as the key. That may even less optimal if you have a large
number of variables with value labels.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:   Adam Troy <[hidden email]>
To:     [hidden email],
Date:   04/02/2014 09:06 AM
Subject:        Exporting SPSS value labels to a SQL Database
Sent by:        "SPSSX(r) Discussion" <[hidden email]>



Hello,

Has anyone come up with a solution for pushing/exporting both the variable
data and value labels to a SQL database? It appears that only the data
carries over which means people trying to chart the data from the database
with third-party programs cannot see the variable value labels making the
data uninterpretable.

The prevailing solution from my Google searches seems to be to just upload
two versions of every variable (one with the quantitative data and one
with the qualitative label as a string) within a single data file, but
that does not seem optimal. Is there another lookup table one should
create that links to the database file? A similar question was asked here:

http://stackoverflow.com/questions/8527800/dealing-with-spss-labels-when-exporting-to-a-database


Would appreciate any ideas! I'm using SPSS Version 21.

Thanks,

Adam Troy
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?"
Rick Oliver-3
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Exporting SPSS value labels to a SQL Database

Rick Oliver-3
Yes, you can certainly create new variables that contain the value labels as values and then export that result back to the database.

The typical database way of handling this would be to store the value labels in a lookup table, and if you want to do it that way, I think it would requires a separate lookup table for each unique set of labels. If multiple variables use the same set of value labels for the same values, then they can use the same lookup table.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:        David Marso <[hidden email]>
To:        [hidden email],
Date:        04/02/2014 10:05 AM
Subject:        Re: Exporting SPSS value labels to a SQL Database
Sent by:        "SPSSX(r) Discussion" <[hidden email]>



I don't see any reason why a large number of variables presents any issue at
all if one uses proper normalization in the database tables! How else would
someone approach this?  Certainly not with a wide data structure!

NEW FILE.
DATASET CLOSE ALL.
/*<data simulation>*/.
MATRIX.
SAVE (TRUNC(UNIFORM(1000,100)*5 +1)) / OUTFILE * / VARIABLES x001 TO x100.
END MATRIX.

VALUE LABELS
x001 TO x100 1 'value 1' 2 'value 2' 3 'value 3' 4 'value 4' 5 'value 5'.
/*</data simulation>*/.

/* FREQUENCIES x001 x100.

STRING lab001 TO lab100 (A8).
DO REPEAT x=x001 TO x100 / l=lab001 TO lab100.
COMPUTE l=VALUELABEL(x).
END REPEAT.
VARSTOCASES
/MAKE value FROM   x001 TO x100
/MAKE label FROM lab001 TO lab100
/INDEX=Varname(value).
DATASET DECLARE table.
AGGREGATE OUTFILE  table /BREAK varname value /LABEL=FIRST(label).



Rick Oliver wrote
> You can create lookup tables for each set of value labels, using the
> variable value as the key. That may even less optimal if you have a large
> number of variables with value labels.
>
> Rick Oliver
> Senior Information Developer
> IBM Business Analytics (SPSS)
> E-mail:

> oliverr@.ibm

>
>
>
> From:   Adam Troy &lt;

> abtphd@

> &gt;
> To:

> SPSSX-L@.uga

> ,
> Date:   04/02/2014 09:06 AM
> Subject:        Exporting SPSS value labels to a SQL Database
> Sent by:        "SPSSX(r) Discussion" &lt;

> SPSSX-L@.uga

> &gt;
>
>
>
> Hello,
>
> Has anyone come up with a solution for pushing/exporting both the variable
> data and value labels to a SQL database? It appears that only the data
> carries over which means people trying to chart the data from the database
> with third-party programs cannot see the variable value labels making the
> data uninterpretable.
>
> The prevailing solution from my Google searches seems to be to just upload
> two versions of every variable (one with the quantitative data and one
> with the qualitative label as a string) within a single data file, but
> that does not seem optimal. Is there another lookup table one should
> create that links to the database file? A similar question was asked here:
>
> http://stackoverflow.com/questions/8527800/dealing-with-spss-labels-when-exporting-to-a-database
>
>
> Would appreciate any ideas! I'm using SPSS Version 21.
>
> Thanks,
>
> Adam Troy





-----
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?"
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Exporting-SPSS-value-labels-to-a-SQL-Database-tp5725168p5725170.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
Art Kendall
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Exporting SPSS value labels to a SQL Database

Art Kendall
In reply to this post by Adam Troy
one workaround might be to create an additional set of variables where the contents are the value labels.

VALUELABEL function

VALUELABEL. VALUELABEL(varname). String. Returns the value label for the value of variable or an empty string if there is no label for the value. The value of varname must be a variable name; it cannot be an expression.

Example

STRING labelvar (A120).
COMPUTE labelvar=VALUELABEL(var1).
DO REPEAT varlist=var2, var3, var4
         /newvars=labelvar2, labelvar3, labelvar4.
- STRING newvars(A120).
- COMPUTE newvars=VALUELABEL(varlist).
END REPEAT.



Art Kendall
Social Research Consultants
On 4/2/2014 10:07 AM, Adam B. Troy-3 [via SPSSX Discussion] wrote:
Hello,

Has anyone come up with a solution for pushing/exporting both the variable data and value labels to a SQL database? It appears that only the data carries over which means people trying to chart the data from the database with third-party programs cannot see the variable value labels making the data uninterpretable.

The prevailing solution from my Google searches seems to be to just upload two versions of every variable (one with the quantitative data and one with the qualitative label as a string) within a single data file, but that does not seem optimal. Is there another lookup table one should create that links to the database file? A similar question was asked here:

http://stackoverflow.com/questions/8527800/dealing-with-spss-labels-when-exporting-to-a-database

Would appreciate any ideas! I'm using SPSS Version 21.

Thanks,

Adam Troy


If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Exporting-SPSS-value-labels-to-a-SQL-Database-tp5725168.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
David Marso
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Exporting SPSS value labels to a SQL Database

David Marso
Administrator
In reply to this post by Rick Oliver-3
Create a SINGLE lookup table indexed by VARIABLE NAME and VALUE!

Rick Oliver wrote
Yes, you can certainly create new variables that contain the value labels
as values and then export that result back to the database.

The typical database way of handling this would be to store the value
labels in a lookup table, and if you want to do it that way, I think it
would requires a separate lookup table for each unique set of labels. If
multiple variables use the same set of value labels for the same values,
then they can use the same lookup table.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:   David Marso <[hidden email]>
To:     [hidden email],
Date:   04/02/2014 10:05 AM
Subject:        Re: Exporting SPSS value labels to a SQL Database
Sent by:        "SPSSX(r) Discussion" <[hidden email]>



I don't see any reason why a large number of variables presents any issue
at
all if one uses proper normalization in the database tables! How else
would
someone approach this?  Certainly not with a wide data structure!

NEW FILE.
DATASET CLOSE ALL.
/*<data simulation>*/.
MATRIX.
SAVE (TRUNC(UNIFORM(1000,100)*5 +1)) / OUTFILE * / VARIABLES x001 TO x100.
END MATRIX.

VALUE LABELS
 x001 TO x100 1 'value 1' 2 'value 2' 3 'value 3' 4 'value 4' 5 'value 5'.
/*</data simulation>*/.

/* FREQUENCIES x001 x100.

STRING lab001 TO lab100 (A8).
DO REPEAT x=x001 TO x100 / l=lab001 TO lab100.
COMPUTE l=VALUELABEL(x).
END REPEAT.
VARSTOCASES
 /MAKE value FROM   x001 TO x100
 /MAKE label FROM lab001 TO lab100
 /INDEX=Varname(value).
DATASET DECLARE table.
AGGREGATE OUTFILE  table /BREAK varname value /LABEL=FIRST(label).



Rick Oliver wrote
> You can create lookup tables for each set of value labels, using the
> variable value as the key. That may even less optimal if you have a
large
> number of variables with value labels.
>
> Rick Oliver
> Senior Information Developer
> IBM Business Analytics (SPSS)
> E-mail:

> oliverr@.ibm

>
>
>
> From:   Adam Troy <

> abtphd@

> >
> To:

> SPSSX-L@.uga

> ,
> Date:   04/02/2014 09:06 AM
> Subject:        Exporting SPSS value labels to a SQL Database
> Sent by:        "SPSSX(r) Discussion" <

> SPSSX-L@.uga

> >
>
>
>
> Hello,
>
> Has anyone come up with a solution for pushing/exporting both the
variable
> data and value labels to a SQL database? It appears that only the data
> carries over which means people trying to chart the data from the
database
> with third-party programs cannot see the variable value labels making
the
> data uninterpretable.
>
> The prevailing solution from my Google searches seems to be to just
upload
> two versions of every variable (one with the quantitative data and one
> with the qualitative label as a string) within a single data file, but
> that does not seem optimal. Is there another lookup table one should
> create that links to the database file? A similar question was asked
here:
>
>
http://stackoverflow.com/questions/8527800/dealing-with-spss-labels-when-exporting-to-a-database

>
>
> Would appreciate any ideas! I'm using SPSS Version 21.
>
> Thanks,
>
> Adam Troy





-----
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?"
--
View this message in context:
http://spssx-discussion.1045642.n5.nabble.com/Exporting-SPSS-value-labels-to-a-SQL-Database-tp5725168p5725170.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
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?"
Rick Oliver-3
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Exporting SPSS value labels to a SQL Database

Rick Oliver-3
D'oh! That should work.

Rick Oliver
Senior Information Developer
IBM Business Analytics (SPSS)
E-mail: [hidden email]



From:        David Marso <[hidden email]>
To:        [hidden email],
Date:        04/02/2014 10:44 AM
Subject:        Re: Exporting SPSS value labels to a SQL Database
Sent by:        "SPSSX(r) Discussion" <[hidden email]>



Create a SINGLE lookup table indexed by VARIABLE NAME and VALUE!


Rick Oliver wrote
> Yes, you can certainly create new variables that contain the value labels
> as values and then export that result back to the database.
>
> The typical database way of handling this would be to store the value
> labels in a lookup table, and if you want to do it that way, I think it
> would requires a separate lookup table for each unique set of labels. If
> multiple variables use the same set of value labels for the same values,
> then they can use the same lookup table.
>
> Rick Oliver
> Senior Information Developer
> IBM Business Analytics (SPSS)
> E-mail:

> oliverr@.ibm

>
>
>
> From:   David Marso &lt;

> david.marso@

> &gt;
> To:

> SPSSX-L@.uga

> ,
> Date:   04/02/2014 10:05 AM
> Subject:        Re: Exporting SPSS value labels to a SQL Database
> Sent by:        "SPSSX(r) Discussion" &lt;

> SPSSX-L@.uga

> &gt;
>
>
>
> I don't see any reason why a large number of variables presents any issue
> at
> all if one uses proper normalization in the database tables! How else
> would
> someone approach this?  Certainly not with a wide data structure!
>
> NEW FILE.
> DATASET CLOSE ALL.
> /*
> <data simulation>
> */.
> MATRIX.
> SAVE (TRUNC(UNIFORM(1000,100)*5 +1)) / OUTFILE * / VARIABLES x001 TO x100.
> END MATRIX.
>
> VALUE LABELS
>  x001 TO x100 1 'value 1' 2 'value 2' 3 'value 3' 4 'value 4' 5 'value 5'.
> /*
> </data simulation>
> */.
>
> /* FREQUENCIES x001 x100.
>
> STRING lab001 TO lab100 (A8).
> DO REPEAT x=x001 TO x100 / l=lab001 TO lab100.
> COMPUTE l=VALUELABEL(x).
> END REPEAT.
> VARSTOCASES
>  /MAKE value FROM   x001 TO x100
>  /MAKE label FROM lab001 TO lab100
>  /INDEX=Varname(value).
> DATASET DECLARE table.
> AGGREGATE OUTFILE  table /BREAK varname value /LABEL=FIRST(label).
>
>
>
> Rick Oliver wrote
>> You can create lookup tables for each set of value labels, using the
>> variable value as the key. That may even less optimal if you have a
> large
>> number of variables with value labels.
>>
>> Rick Oliver
>> Senior Information Developer
>> IBM Business Analytics (SPSS)
>> E-mail:
>
>> oliverr@.ibm
>
>>
>>
>>
>> From:   Adam Troy &lt;
>
>> abtphd@
>
>> &gt;
>> To:
>
>> SPSSX-L@.uga
>
>> ,
>> Date:   04/02/2014 09:06 AM
>> Subject:        Exporting SPSS value labels to a SQL Database
>> Sent by:        "SPSSX(r) Discussion" &lt;
>
>> SPSSX-L@.uga
>
>> &gt;
>>
>>
>>
>> Hello,
>>
>> Has anyone come up with a solution for pushing/exporting both the
> variable
>> data and value labels to a SQL database? It appears that only the data
>> carries over which means people trying to chart the data from the
> database
>> with third-party programs cannot see the variable value labels making
> the
>> data uninterpretable.
>>
>> The prevailing solution from my Google searches seems to be to just
> upload
>> two versions of every variable (one with the quantitative data and one
>> with the qualitative label as a string) within a single data file, but
>> that does not seem optimal. Is there another lookup table one should
>> create that links to the database file? A similar question was asked
> here:
>>
>>
> http://stackoverflow.com/questions/8527800/dealing-with-spss-labels-when-exporting-to-a-database
>
>>
>>
>> Would appreciate any ideas! I'm using SPSS Version 21.
>>
>> Thanks,
>>
>> Adam Troy
>
>
>
>
>
> -----
> 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?"
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Exporting-SPSS-value-labels-to-a-SQL-Database-tp5725168p5725170.html
>
> Sent from the SPSSX Discussion mailing list archive at Nabble.com.
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

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





-----
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?"
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Exporting-SPSS-value-labels-to-a-SQL-Database-tp5725168p5725176.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
Richard Ristow
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Exporting SPSS value labels to a SQL Database

Richard Ristow
In reply to this post by David Marso
At 11:39 AM 4/2/2014, David Marso wrote:

>Create a SINGLE lookup table indexed by VARIABLE NAME and VALUE!

An excellent idea, that blows up if the variables don't all have the
same datatype.

=====================
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
Reply | Threaded
Open this post in threaded view
♦
♦
|

Re: Exporting SPSS value labels to a SQL Database

David Marso
Administrator
Well then coerce them via ALTER TYPE or make 2 tables, one for strings (after conversion to max length) one for numeric?
Richard Ristow wrote
At 11:39 AM 4/2/2014, David Marso wrote:

>Create a SINGLE lookup table indexed by VARIABLE NAME and VALUE!

An excellent idea, that blows up if the variables don't all have the
same datatype.

=====================
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
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?"
Free forum by Nabble Edit this page