Automating Adding Value Labels

[Morrell, Ronald]

On a monthly basis, our office extracts data from a SQL database into SPSS sav files for analysis.  During the month, new data values can be added to various SQL tables.  Currently, we review the data for these cases and manually assign a value label using syntax.  Ideally, we would like to automate the process.  Has anyone had experience with extracting a description field from a table and assigned the information as a value label in the SAV file?

 

Here is an example of what we are trying to do.  In the SQL database there is a table called tblMgtUnit.  It contains the data elements MgtUnitID and UnitTitle.  The MgtUnitID is imported into the SAV file.  Using syntax, the UnitTitle is added as the value label.  We would like to automate the process by pulling the ID and making the title the value label.

 

Thanks in advance for any insight,

 

Ron

 

[Jon K Peck]

Perhaps the STATS_VALLBLS_FROMTEXT extension command could handle this, depending on how your data are structured.  It takes as input variables whose values are to be labelled and variables that contain corresponding strings with the label for the value and creates value labels from them.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        "Morrell, Ronald" <[hidden email]>
To:        [hidden email],
Date:        02/17/2014 09:44 AM
Subject:        [SPSSX-L] Automating Adding Value Labels
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

On a monthly basis, our office extracts data from a SQL database into SPSS sav files for analysis.  During the month, new data values can be added to various SQL tables.  Currently, we review the data for these cases and manually assign a value label using syntax.  Ideally, we would like to automate the process.  Has anyone had experience with extracting a description field from a table and assigned the information as a value label in the SAV file?
 
Here is an example of what we are trying to do.  In the SQL database there is a table called tblMgtUnit.  It contains the data elements MgtUnitID and UnitTitle.  The MgtUnitID is imported into the SAV file.  Using syntax, the UnitTitle is added as the value label.  We would like to automate the process by pulling the ID and making the title the value label.
 
Thanks in advance for any insight,
 
Ron
 

[Morrell, Ronald]

Thanks Jon, I’ll research your suggestion.

 

Ron

 

From: Jon K Peck [mailto:[hidden email]]
Sent: Monday, February 17, 2014 11:57 AM
To: Morrell, Ronald
Cc: [hidden email]
Subject: Re: [SPSSX-L] Automating Adding Value Labels

 

Perhaps the STATS_VALLBLS_FROMTEXT extension command could handle this, depending on how your data are structured.  It takes as input variables whose values are to be labelled and variables that contain corresponding strings with the label for the value and creates value labels from them.


Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: [phone]




From:        "Morrell, Ronald" <[hidden email]>
To:        [hidden email],
Date:        02/17/2014 09:44 AM
Subject:        [SPSSX-L] Automating Adding Value Labels
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

On a monthly basis, our office extracts data from a SQL database into SPSS sav files for analysis.  During the month, new data values can be added to various SQL tables.  Currently, we review the data for these cases and manually assign a value label using syntax.  Ideally, we would like to automate the process.  Has anyone had experience with extracting a description field from a table and assigned the information as a value label in the SAV file?
 
Here is an example of what we are trying to do.  In the SQL database there is a table called tblMgtUnit.  It contains the data elements MgtUnitID and UnitTitle.  The MgtUnitID is imported into the SAV file.  Using syntax, the UnitTitle is added as the value label.  We would like to automate the process by pulling the ID and making the title the value label.
 
Thanks in advance for any insight,
 
Ron
 

[Richard Ristow]

At 11:57 AM 2/17/2014, Jon K Peck wrote:

>Perhaps the STATS_VALLBLS_FROMTEXT extension command could handle this, ...

Wow! I didn't expect this problem to come up again this soon. Good
going, Jon, though I'll also post the native-SPSS solution.

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

At 11:43 AM 2/17/2014, Morrell, Ronald wrote:

>On a monthly basis, our office extracts data from a SQL database
>into SPSS sav files. In the SQL database there is a table called
>tblMgtUnit.  It contains the data elements MgtUnitID and
>UnitTitle.  The MgtUnitID is imported into the SAV file.  Using
>syntax, the UnitTitle is added as the value label.  We would like to
>automate the process by pulling the ID and making the title the value label.

This came up quite recently (thread "value labels from string vars",
Mon, 6 Jan 2014 and following).

What you want to do is, first, before the extract, join tblMgtUnit to
your data, so fields MgtUnitID and UnitTitle (which, I presume, is
going to be your value label) are *both* included in the .SAV file.
(UnitTitle will be a string variable; MgtUnitID will be whatever type it is.)

Then, on the SPSS end, you need to make value labels for MgtUnitID so
you don't have to have UnitTitle in the file anymore. Two solutions
were written and posted to that problem. I commend to you Jon Peck's
STATS_VALLBLS_FROMTEXT, a Python-base solution.

On the other hand, it can also be done in pure SPSS code, if you like
(as I sometimes do). I posted a solution done that way; I'm repeating
it, at the end of this note.

On the third hand, you might consider importing table tblMgtUnit each
month to a separate SPSS .SAV file. That will then be a table of
values and necessary labels, and can be converted to an SPSS VALUE
LABELS statement by various means, including code like that below.

=================================================================================
In native SPSS (originally posted Thu, 16 Jan 2014, "Re: value labels
from string vars")

To use:

1. Specify a file handle for file 'Code', usually a temporary location

2. If you're only generating labels for one variable, the VARSTOCASES
isn't necessary. However, if you're using it, on the VARSTOCASES statement,
.  Change "/KEEP  = CaseID" to name whatever case identifier is in
your data -- or, omit this clause altogether; the case identifier
isn't actually used.
.  On "/MAKE Value", name the variables to which you want to assign
value labels; on "/MAKE Label", name the corresponding variables
containing the labels.


|-----------------------------|---------------------------|
|Output Created               |16-JAN-2014 12:33:52       |
|-----------------------------|---------------------------|
  [TestData]
CaseID LatinVal LatinLbl GreekVal GreekLbl HebrwVal HebrwLbl

   1001      1   A             1   Alpha         1   Aleph
   1002      1   A             2   Beta          1   Aleph
   1003      3   C             2   Beta          1   Aleph
   1004      3   C             1   Alpha         1   Aleph
   1005      3   C             1   Alpha         2   Beth
   1006      1   A             1   Alpha         3   Gimel
   1007      3   C             1   Alpha         2   Beth
   1008      1   A             2   Beta          2   Beth
   1009      3   C             1   Alpha         1   Aleph
   1010      2   B             3   Gamma         3   Gimel

Number of cases read:  10    Number of cases listed:  10


*  I.      Unroll, putting each code-label pair in a separate   ...  .
*          record                                               ...  .

DATASET   ACTIVATE TestData WINDOW=FRONT.
DATASET   COPY     Summary.
DATASET   ACTIVATE Summary  WINDOW=Front.
*  ...........................................................  ...  .
*  To use this for any other dataset, you should only have to   ...  .
*  change the lists of variables on the two MAKE lists,         ...  .
*  and change (or delete) the KEEP clause.                      ...  .
*  ...........................................................  ...  .

VARSTOCASES
    /MAKE Value FROM LatinVal GreekVal HebrwVal
    /MAKE Label FROM LatinLbl GreekLbl HebrwLbl
    /INDEX = Variable(Value)
    /KEEP  = CaseID
    /NULL  = DROP.


Variables to Cases
|-----------------------------|---------------------------|
|Output Created               |16-JAN-2014 12:33:53       |
|-----------------------------|---------------------------|
|   (Notes omitted)                                       |
|-----------------------------|---------------------------|

   *  II.   Summarize, to a single record for each                 ...  .
*        variable-value-label triplet found in the data         ...  .

AGGREGATE  OUTFILE=*
    /BREAK =Variable Value Label
    /N#    'Instances of this pair' = NU.

LIST.

List
|-----------------------------|---------------------------|
|Output Created               |16-JAN-2014 12:33:53       |
|-----------------------------|---------------------------|
Variable Value Label       N#

GreekVal    1  Alpha        6
GreekVal    2  Beta         3
GreekVal    3  Gamma        1
HebrwVal    1  Aleph        5
HebrwVal    2  Beth         3
HebrwVal    3  Gimel        2
LatinVal    1  A            4
LatinVal    2  B            1
LatinVal    3  C            5

Number of cases read:  9    Number of cases listed:  9


*  III.    Generate VALUE LABEL statements                      ...  .

ADD FILES
   /FILE=*
   /BY    Variable
   /FIRST=VarStart LAST =VarEnd.


*  III.A.  Scratch variables: A literal quotation mark, and     ...  .
*          a buffer in which to build the labels                ...  .

STRING  #Qt    (A1)
         #Label (A50).

COMPUTE #Qt = '"'.


*  III.B.  Write the statement beginnings, naming the variables ...  .

DO IF   VarStart.
.  WRITE OUTFILE=Code
    /'VALUE LABELS ' Variable .
END IF.


*  III.C.  Write the label specifications                       ...  .

COMPUTE    #Label = CONCAT(#Qt,RTRIM(Label),#Qt).

IF Varend  #Label = CONCAT(RTRIM(#Label),'.').

.  WRITE OUTFILE=Code
    /'   ' Value (F3) '  ' #Label *.

*  III.D.  Force a data pass, to write out the code             ...  .

EXECUTE.


*  IV.     Apply the labels to the original file                ...   .

DATASET ACTIVATE TestData WINDOW=FRONT.
INSERT FILE=Code.
  162  VALUE LABELS GreekVal
  163       1  "Alpha"
  164       2  "Beta"
  165       3  "Gamma".
  166  VALUE LABELS HebrwVal
  167       1  "Aleph"
  168       2  "Beth"
  169       3  "Gimel".
  170  VALUE LABELS LatinVal
  171       1  "A"
  172       2  "B"
  173       3  "C".
  174
  175  * End of INSERT and INCLUDE nesting level 01.
=============================
APPENDIX: Test data, and code
=============================
*  C:\Documents and Settings\Richard\My Documents       .
*    \Technical\spssx-l\Z-2014\                         .
*    2014-01-06 Tighe-value labels from string vars.SPS .

*  In response to posting                               .
*  Date:     Mon, 6 Jan 2014 17:46:20 -0500             .
*  From:     Elizabeth Tighe <[hidden email]>       .
*  Subject:  value labels from string vars              .
*  To:       [hidden email]                   .

*  ................................................................. .
*  "We'd like to read data stored in an Access database where the    .
*  descriptive labels on categorical variables are stored in         .
*  separate tables.  The ODBC import will pull the fields containing .
*  the labels in as additional variables and we'd like to get them   .
*  to be used as value labels instead.                               .
*                                                                    .
*  "Is there a way to assign the values of one variable (a string    .
*  variable) as the value labels for another variable, i.e., the     .
*  equivalent of the labmask function available as part of the       .
*  labutil package in Stata?"                                        .
*  ................................................................. .


*  0.a     External file for generated code                     ...  .

FILE HANDLE Code
  /NAME='C:\Documents and Settings\Richard\My Documents'              +
          '\Temporary\SPSS\'                                          +
        '2014-01-06 Tighe-value labels from string vars' +
        ' - '                                                         +
        'CODE.SPS'.

*  0.b     Test data                                            ...  .


SET RNG    =MT.
SET MTINDEX=4833.

NEW FILE.
INPUT PROGRAM.
NUMERIC  CaseID   (F5).
NUMERIC  LatinVal (F3).
STRING   LatinLbl (A1).
NUMERIC  GreekVal (F3).
STRING   GreekLbl (A5).
NUMERIC  HebrwVal (F3).
STRING   HebrwLbl (A6).

LOOP CaseID = 1001 TO 1010.
.  DO REPEAT  Value = LatinVal GreekVal HebrwVal.
.     COMPUTE Value = RV.POISSON(1.0) + 1.
.     RECODE  Value (3 THRU HI = 3).
.  END REPEAT.
.  RECODE LatinVal
          (1 = 'A') (2 = 'B') (3 = 'C')
          INTO LatinLbl.
.  RECODE GreekVal
          (1 = 'Alpha')
          (2 = 'Beta')
          (3 = 'Gamma')
          INTO GreekLbl.
.  RECODE HebrwVal
          (1 = 'Aleph')
          (2 = 'Beth')
          (3 = 'Gimel')
          INTO HebrwLbl.
.  END CASE.
END LOOP.
END FILE.
END INPUT PROGRAM.

DATASET NAME     TestData WINDOW=FRONT.

*  ...     Post, after this point                               ...  .

LIST.

*  I.      Unroll, putting each code-label pair in a separate   ...  .
*          record                                               ...  .

DATASET   ACTIVATE TestData WINDOW=FRONT.
DATASET   COPY     Summary.
DATASET   ACTIVATE Summary  WINDOW=Front.

*  ...........................................................  ...  .
*  To use this for any other dataset, you should only have to   ...  .
*  change the lists of variables on the two MAKE lists,         ...  .
*  and change (or delete) the KEEP clause.                      ...  .
*  ...........................................................  ...  .

VARSTOCASES
    /MAKE Value FROM LatinVal GreekVal HebrwVal
    /MAKE Label FROM LatinLbl GreekLbl HebrwLbl
    /INDEX = Variable(Value)
    /KEEP  = CaseID
    /NULL  = DROP.

LIST.

*  II.   Summarize, to a single record for each                 ...  .
*        variable-value-label triplet found in the data         ...  .

AGGREGATE  OUTFILE=*
    /BREAK =Variable Value Label
    /N#    'Instances of this pair' = NU.

LIST.

*  III.    Generate VALUE LABEL statements                      ...  .

ADD FILES
   /FILE=*
   /BY  Variable
   /FIRST=VarStart LAST =VarEnd.


*  III.A.  Scratch variables: A literal quotation mark, and     ...  .
*          a buffer in which to build the labels                ...  .

STRING  #Qt    (A1)
         #Label (A50).

COMPUTE #Qt = '"'.


*  III.B.  Write the statement beginnings, naming the variables ...  .

DO IF   VarStart.
.  WRITE OUTFILE=Code
    /'VALUE LABELS ' Variable .
END IF.


*  III.C.  Write the label specifications                       ...  .

COMPUTE    #Label = CONCAT(#Qt,RTRIM(Label),#Qt).

IF Varend  #Label = CONCAT(RTRIM(#Label),'.').

.  WRITE OUTFILE=Code
    /'   ' Value (F3) '  ' #Label *.

*  III.D.  Force a data pass, to write out the code             ...  .

EXECUTE.


*  IV.     Apply the labels to the original file                ...   .

DATASET ACTIVATE TestData WINDOW=FRONT.

INSERT FILE=Code.

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