Repeat case equals VAR

[Peter Spangler]

Dear SPSS Folk,

My data has 4 Columns. Each column has user IDs in Rows. Some of the IDs repeat between VAR1 to VAR3. I would like to assign the names of the variables to the cases for which that ID repeats.

   

VAR1 VAR2 VAR3 NEW_VAR

    1       2         3       VAR1, VAR2 

    0       1         0 

Any suggestions are greatly appreciated!

[David Marso]

Peter,
Your question is not clear .
That might explain why nobody has bothered to respond!
Please provide a little more detail?
Here's how it works:
If you take 1 minute to pose your question.
I'll take 1 minute to respond.
90 sec gotta go.
-------------------

Peter Spangler wrote

Dear SPSS Folk,

My data has 4 Columns. Each column has user IDs in Rows. Some of the IDs
repeat between VAR1 to VAR3. I would like to assign the names of the
variables to the cases for which that ID repeats.


*VAR1 VAR2 VAR3 NEW_VAR*
    1       2         3       VAR1, VAR2
    0       1         0

Any suggestions are greatly appreciated!

[Tierney, Mary Lou]

I am out of the office and offline today, Friday 29 March.

Please contact:

 

·         [hidden email] for questions or assistance with FY14 proposal process questions including Idea Market, Strategy Market or Innovation@MITRE (sponsor portal)

·         [hidden email] for questions or assistance with MIP funded project processes or the website

·         [hidden email] for questions about or assistance with MIP ProjectPages (page functionality, content on the pages, i.e. PI name, project title, etc.)

·         [hidden email] for questions about charge numbers

·         [hidden email] for assistance with other Innovation Zone sites, such as CI&T InZone

 

Regards,

Mary Lou

[Peter Spangler]

David,

My apologies. I intended to be concise, not unclear. 

I would like to create a new variable to assign the variable names to cases where values are repeated across three variables. For example, value 999 appears in VAR1 and VAR2. I would assign this case VAR1, VAR2. 

I hope this is more clear. 

Peter

On Fri, Mar 29, 2013 at 7:37 AM, David Marso <[hidden email]> wrote:

Peter,
Your question is not clear .
That might explain why nobody has bothered to respond!
Please provide a little more detail?
Here's how it works:
If you take 1 minute to pose your question.
I'll take 1 minute to respond.
90 sec gotta go.
-------------------

Peter Spangler wrote

> Dear SPSS Folk,
>
> My data has 4 Columns. Each column has user IDs in Rows. Some of the IDs
> repeat between VAR1 to VAR3. I would like to assign the names of the
> variables to the cases for which that ID repeats.
>
>

> *VAR1 VAR2 VAR3 NEW_VAR*

>     1       2         3       VAR1, VAR2
>     0       1         0
>
> Any suggestions are greatly appreciated!

-----
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/Repeat-case-equals-VAR-tp5719153p5719169.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

[David Marso]

Well,  About as clear as this is likely to be!
Ponder this:
VARSTOCASES ...
SORT...
STRING ...
See DO IF and LAG
COMPUTE  ... CONCAT...
AGGREGATE or CASESTOVARS.
-------------------
Have a great weekend.  
I bet you'll sort it out by then, or realize that the
question as posed is very ill defined.

Peter Spangler wrote

David,
My apologies. I intended to be concise, not unclear.

I would like to create a new variable to assign the variable names to cases
where values are repeated across three variables. For example, value 999
appears in VAR1 and VAR2. I would assign this case VAR1, VAR2.

I hope this is more clear.

Peter


On Fri, Mar 29, 2013 at 7:37 AM, David Marso <[hidden email]> wrote:

> Peter,
> Your question is not clear .
> That might explain why nobody has bothered to respond!
> Please provide a little more detail?
> Here's how it works:
> If you take 1 minute to pose your question.
> I'll take 1 minute to respond.
> 90 sec gotta go.
> -------------------
>
> Peter Spangler wrote
> > Dear SPSS Folk,
> >
> > My data has 4 Columns. Each column has user IDs in Rows. Some of the IDs
> > repeat between VAR1 to VAR3. I would like to assign the names of the
> > variables to the cases for which that ID repeats.
> >
> >
> > *VAR1 VAR2 VAR3 NEW_VAR*
> >     1       2         3       VAR1, VAR2
> >     0       1         0
> >
> > Any suggestions are greatly appreciated!
>
>
>
>
>
> -----
> 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/Repeat-case-equals-VAR-tp5719153p5719169.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
>

[Ujjawal]

DATA LIST Free / V1 V2 V3.
BEGIN DATA
1 1 2
3 2 3
3 2 2
END DATA.

STRING NEWVAR (A10).
DO IF (V1=V2).
Compute NEWVAR="V1,V2".
ELSE IF (V2=V3).
COMPUTE NEWVAR="V2,V3".
ELSE IF (V1=V3).
COMPUTE NEWVAR = "V1,V3".
END IF .
LIST .

      V1       V2       V3 NEWVAR
 
    1.00     1.00     2.00 V1,V2
    3.00     2.00     3.00 V1,V3
    3.00     2.00     2.00 V2,V3

[Bruce Weaver]

Ah, is that what the OP meant?  What if all 3 variables are the same?

DATA LIST Free / V1 V2 V3.
BEGIN DATA
1 1 2
3 2 3
3 2 2
3 3 3
END DATA.

STRING NEWVAR (A10).
DO IF SD.3(V1,V2,V3) EQ 0.
- Compute NEWVAR="V1,V2,V3".
ELSE IF (V1=V2).
- Compute NEWVAR="V1,V2".
ELSE IF (V2=V3).
- COMPUTE NEWVAR="V2,V3".
ELSE IF (V1=V3).
- COMPUTE NEWVAR = "V1,V3".
END IF .
LIST .

Ujjawal wrote

DATA LIST Free / V1 V2 V3.
BEGIN DATA
1 1 2
3 2 3
3 2 2
END DATA.

STRING NEWVAR (A10).
DO IF (V1=V2).
Compute NEWVAR="V1,V2".
ELSE IF (V2=V3).
COMPUTE NEWVAR="V2,V3".
ELSE IF (V1=V3).
COMPUTE NEWVAR = "V1,V3".
END IF .
LIST .

      V1       V2       V3 NEWVAR
 
    1.00     1.00     2.00 V1,V2
    3.00     2.00     3.00 V1,V3
    3.00     2.00     2.00 V2,V3

[David Marso]

From Peter's example that is unlikely.
Look at my advice.  That guts the fish as presented.
But I'm not writing any more code tonight!
Peter needs to present a clearer example.
THIS is NOT TWITTER!
Do I need to bump my "how to ask questions thread again" ?????
-----

Bruce Weaver wrote

Ah, is that what the OP meant?  What if all 3 variables are the same?

DATA LIST Free / V1 V2 V3.
BEGIN DATA
1 1 2
3 2 3
3 2 2
3 3 3
END DATA.

STRING NEWVAR (A10).
DO IF SD.3(V1,V2,V3) EQ 0.
- Compute NEWVAR="V1,V2,V3".
ELSE IF (V1=V2).
- Compute NEWVAR="V1,V2".
ELSE IF (V2=V3).
- COMPUTE NEWVAR="V2,V3".
ELSE IF (V1=V3).
- COMPUTE NEWVAR = "V1,V3".
END IF .
LIST .

Ujjawal wrote

DATA LIST Free / V1 V2 V3.
BEGIN DATA
1 1 2
3 2 3
3 2 2
END DATA.

STRING NEWVAR (A10).
DO IF (V1=V2).
Compute NEWVAR="V1,V2".
ELSE IF (V2=V3).
COMPUTE NEWVAR="V2,V3".
ELSE IF (V1=V3).
COMPUTE NEWVAR = "V1,V3".
END IF .
LIST .

      V1       V2       V3 NEWVAR
 
    1.00     1.00     2.00 V1,V2
    3.00     2.00     3.00 V1,V3
    3.00     2.00     2.00 V2,V3

[Richard Ristow]

At 04:17 PM 3/28/2013, Peter Spangler wrote:

>My data has 4 Columns. Each column has user IDs in Rows. Some of the
>IDs repeat between VAR1 to VAR3. I would like to assign the names of
>the variables to the cases for which that ID repeats.
>
>
>VAR1 VAR2 VAR3 NEW_VAR
>  1    2    3   VAR1, VAR2
>  0    1    0

I gather from what you write, and later discussion, that your example
here does not represent what you really want; you'd want instead,
with the input you give,

VAR1 VAR2 VAR3 NEW_VAR
  1    2    3
  0    1    0   VAR1, VAR3

If I'm wrong, the following is in vain; if I'm write, I hope it
helps. In the test data below, I've added a variable "Record" which
uniquely identifies the input records; some such record identifier is
essential. (And if you had four or more "VARn" variables, so that
more than one ID could be duplicated, the problem would be
significantly more complicated.)

[TestData]
Record VAR1 VAR2 VAR3

A        1    2    3
B        1    3    3
C        3    2    3
D        3    3   3

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

NEW FILE.
ADD FILES /FILE=TestData.

EXECUTE /* SPSS v.14 glitch: without this, VARSTOCASES */
         /* sees no data                                */.

VARSTOCASES
  /MAKE PersonID FROM VAR1 VAR2 VAR3
  /INDEX   = VarName(PersonID)
  /KEEP    = Record
  /NULL    = DROP.

Variables to Cases

Generated Variables
|--------|------|
|Name    |Label |
|--------|------|
|VarName |<none>|
|PersonID|<none>|
|--------|------|
Processing Statistics
|-------------|-|
|Variables In |4|
|Variables Out|3|
|-------------|-|
DATASET NAME     Calculate.
LIST.

List
[Calculate]
Record VarName PersonID

A      VAR1        1
A      VAR2        2
A      VAR3        3
B      VAR1        1
B      VAR2        3
B      VAR3        3
C      VAR1        3
C      VAR2        2
C      VAR3        3
D      VAR1        3
D      VAR2        3
D      VAR3        3
  Number of cases read:  12    Number of cases listed:  12

SORT CASES BY Record PersonID VarName.
ADD FILES
   /FILE=*
   /BY Record PersonID
   /FIRST=Start
   /LAST =End.

STRING NewVar (A15).

DO IF   Start.
.  COMPUTE NewVar = VarName.
ELSE.
.  COMPUTE NewVar = CONCAT(RTRIM(LAG(NewVar)),
                            ', '              ,
                            VarName           ).
END IF.

*  If the following LIST is removed, it must be         .
*  replaced by EXECUTE, or the SELECT IF that follows   .
*  may fail.                                            .

LIST.

List
[Calculate]
Record VarName PersonID Start End NewVar

A      VAR1        1      1    1  VAR1
A      VAR2        2      1    1  VAR2
A      VAR3        3      1    1  VAR3
B      VAR1        1      1    1  VAR1
B      VAR2        3      1    0  VAR2
B      VAR3        3      0    1  VAR2, VAR3
C      VAR2        2      1    1  VAR2
C      VAR1        3      1    0  VAR1
C      VAR3        3      0    1  VAR1, VAR3
D      VAR1        3      1    0  VAR1
D      VAR2        3      0    0  VAR1, VAR2
D      VAR3        3      0    1  VAR1, VAR2, VAR

Number of cases read:  12    Number of cases listed:  12

SELECT IF End AND NOT Start.

MATCH FILES
    /FILE=TestData
    /FILE=Calculate
    /By   Record
    /DROP=VarName, PersonID, Start, End.

LIST.

List
Record VAR1 VAR2 VAR3 NewVar

A        1    2    3
B        1    3    3  VAR2, VAR3
C        3    2    3  VAR1, VAR3
D        3    3   3  VAR1, VAR2, VAR

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

==================================
APPENDIX: All code, with test data
==================================
*  C:\Documents and Settings\Richard\My Documents   .
*    \Technical\spssx-l\Z-2013\                     .
*    2013-03-28 Spangler-Repeat case equals VAR.SPS .

*  In response to posting                           .
*  Date:    Thu, 28 Mar 2013 13:17:00 -0700         .
*  From:    Peter Spangler <[hidden email]>  .
*  Subject: Repeat case equals VAR                  .
*  To:      [hidden email]                .

*  The language of the posting is opaque, and I won't try to repeat   .
*  it here. I think I'm giving him what he wants.                     .

*  ................................................................. .
*  .................   Test data               ..................... .

DATA LIST LIST/
    Record  VAR1 VAR2 VAR3
    (  A1,   F1,  F1,  F1).
BEGIN DATA
       A      1    2    3
       B      1    3    3
       C      3    2    3
       D      3    3    3
END DATA.
DATASET NAME     TestData.
LIST.

NEW FILE.
ADD FILES /FILE=TestData.

EXECUTE /* SPSS v.14 glitch: without this, VARSTOCASE */
         /* sees no data                               */.

VARSTOCASES
  /MAKE PersonID FROM VAR1 VAR2 VAR3
  /INDEX   = VarName(PersonID)
  /KEEP    = Record
  /NULL    = DROP.

DATASET NAME     Calculate.
LIST.

SORT CASES BY Record PersonID VarName.

ADD FILES
   /FILE=*
   /BY Record PersonID
   /FIRST=Start
   /LAST =End.

STRING NewVar (A15).

DO IF   Start.
.  COMPUTE NewVar = VarName.
ELSE.
.  COMPUTE NewVar = CONCAT(RTRIM(LAG(NewVar)),
                            ', '              ,
                            VarName           ).
END IF.

*  If the following LIST is removed, it must be         .
*  replaced by EXECUTE, or the SELECT IF that follows   .
*  may fail.                                            .

LIST.

SELECT IF End AND NOT Start.

.  /* Debugging only */ LIST.

MATCH FILES
    /FILE=TestData
    /FILE=Calculate
    /By   Record
    /DROP=VarName, PersonID, Start, End.

LIST.

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