Transposing data with mixed fields...

[J. R. Carroll]

Listers,

I have a dataset where row1 has string data (Ss ID's), column1 has variable names, and the rest of it is data.

Example:

Username    John    Mark    Bob

V1    10    4    1   10

V2    19    2    3    4   

V3    12    2    4    1

V4    11    2    3    4

The dataset:  I need to transpose the data (so my column1 becomes my variable names), and the numerical data stored therein (where each column represents a person), gets 'flipped' so rows represent a participant (standard transposing).

The problem:  I have successfully transposed the data (not the usernames row), but only after figuring out that SPSS doesn't like dealing with columns that are set as "string", and the fields below it are mixed between string (only the top of the column) and numeric (everything else).  Once I stripped the string away, SPSS transposed the data, took column1 as the variable names, and allowed me to change all the numeric variables to numeric field type.  But now I am left with a column that says "Username" that is blank - due to my ineptness to successfully tell SPSS exactly what I wanted it to do.

My current solution:  In summary, I will "select" casenum 1, copy to a new dataset, transpose that, then merge into the existing dataset with the already transposed data.

My question:  Does anyone have a better solution?  I'd like to be able to tell SPSS to just look at row1 of the original dataset, and just "paste vertically" (for lack of a better description); it seems a bit... tedious (?) to go through the process of Selecting row1, copy to a new dataset, transpose, merge, etc.  So much so, that I suspect there is a better way.  Advice?

Thanks in advanced (hope I included enough clarity).   

-J

[Albert-Jan Roskam]

Hello,

Have you tried the command FLIP? No subcommands, just 'FLIP'.

 

Cheers!!

Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a

fresh water system, and public health, what have the Romans ever done for us?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

From: J. R. Carroll <[hidden email]>
To: [hidden email]
Sent: Monday, January 23, 2012 9:05 PM
Subject: [SPSSX-L] Transposing data with mixed fields...

Listers,

I have a dataset where row1 has string data (Ss ID's), column1 has variable names, and the rest of it is data.

Example:

Username    John    Mark    Bob

V1    10    4    1   10

V2    19    2    3    4   

V3    12    2    4    1

V4    11    2    3    4

The dataset:  I need to transpose the data (so my column1 becomes my variable names), and the numerical data stored therein (where each column represents a person), gets 'flipped' so rows represent a participant (standard transposing).

The problem:  I have successfully transposed the data (not the usernames row), but only after figuring out that SPSS doesn't like dealing with columns that are set as "string", and the fields below it are mixed between string (only the top of the column) and numeric (everything else).  Once I stripped the string away, SPSS transposed the data, took column1 as the variable names, and allowed me to change all the numeric variables to numeric field type.  But now I am left with a column that says "Username" that is blank - due to my ineptness to successfully tell SPSS exactly what I wanted it to do.

My current solution:  In summary, I will "select" casenum 1, copy to a new dataset, transpose that, then merge into the existing dataset with the already transposed data.

My question:  Does anyone have a better solution?  I'd like to be able to tell SPSS to just look at row1 of the original dataset, and just "paste vertically" (for lack of a better description); it seems a bit... tedious (?) to go through the process of Selecting row1, copy to a new dataset, transpose, merge, etc.  So much so, that I suspect there is a better way.  Advice?

Thanks in advanced (hope I included enough clarity).   

-J

[David Marso]

First question I have  is:
How did your data arrive in that format in the first place?
In any case they are defined as STRING and that fact remains even after your FLIP.
Pretty much useless AFAICT.
You might want to address your data import strategy if this is a common occurance.
For now, this works (I generated the GET DATA command from the wizard (not what I typically would do).  NOTE:  Your original data has fewer variables defined than columns so hard to tell what you actually are working with).
--
data list free/X1 TO X5 (5A8).
BEGIN DATA
Username xx   John    Mark    Bob
V1    10    4    1   10
V2    19    2    3    4  
V3    12    2    4    1
V4    11    2    3    4
END DATA.
SAVE TRANSLATE OUTFILE='C:\Temp\tempdat.dat'  /TYPE=TAB /MAP /REPLACE.
GET DATA  /TYPE = TXT
 /FILE = 'C:\Temp\tempdat.dat'
 /DELCASE = LINE
 /DELIMITERS = "\t"
 /ARRANGEMENT = DELIMITED
 /FIRSTCASE = 2
 /IMPORTCASE = ALL
 /VARIABLES =
 CASE_LBL A8
 xx F2.1
 John F1.0
 Mark F1.0
 Bob F2.1
 .
CACHE.
EXECUTE.
FLIP.

J. R. Carroll wrote

Listers,

I have a dataset where row1 has string data (Ss ID's), column1 has variable
names, and the rest of it is data.

*Example:*

Username    John    Mark    Bob
V1    10    4    1   10
V2    19    2    3    4
V3    12    2    4    1
V4    11    2    3    4


*The dataset:*  I need to transpose the data (so my column1 becomes my
variable names), and the numerical data stored therein (where each column
represents a person), gets 'flipped' so rows represent a participant
(standard transposing).

*The problem: * I have successfully transposed the data (not the *usernames
row*), but only after figuring out that SPSS doesn't like dealing with
columns that are set as "string", and the fields below it are mixed between
string (only the top of the column) and numeric (everything else).  Once I
stripped the string away, SPSS transposed the data, took column1 as the
variable names, and allowed me to change all the numeric variables to
numeric field type.  But now I am left with a column that says "Username"
that is blank - due to my ineptness to successfully tell SPSS exactly what
I wanted it to do.

*My current solution: * In summary, I will "select" casenum 1, copy to a
new dataset, transpose that, *then *merge into the existing dataset with
the already transposed data.

*My question:  *Does anyone have a better solution?  I'd like to be able to
tell SPSS to just look at row1 of the original dataset, and just "paste
vertically" (for lack of a better description); it seems a bit... tedious
(?) to go through the process of Selecting row1, copy to a new dataset,
transpose, merge, etc.  So much so, that I suspect there is a better way.
 Advice?

Thanks in advanced (hope I included enough clarity).

-J

[Bruce Weaver]

Maybe I'm just a bit slow on a Monday afternoon (if not generally), but it would help me if you showed what you want the data to look like after you've finished.  We've got the "before" below.  Now show us the "after" please.   ;-)

J. R. Carroll wrote

Listers,

I have a dataset where row1 has string data (Ss ID's), column1 has variable
names, and the rest of it is data.

*Example:*

Username    John    Mark    Bob
V1    10    4    1   10
V2    19    2    3    4
V3    12    2    4    1
V4    11    2    3    4


*The dataset:*  I need to transpose the data (so my column1 becomes my
variable names), and the numerical data stored therein (where each column
represents a person), gets 'flipped' so rows represent a participant
(standard transposing).

*The problem: * I have successfully transposed the data (not the *usernames
row*), but only after figuring out that SPSS doesn't like dealing with
columns that are set as "string", and the fields below it are mixed between
string (only the top of the column) and numeric (everything else).  Once I
stripped the string away, SPSS transposed the data, took column1 as the
variable names, and allowed me to change all the numeric variables to
numeric field type.  But now I am left with a column that says "Username"
that is blank - due to my ineptness to successfully tell SPSS exactly what
I wanted it to do.

*My current solution: * In summary, I will "select" casenum 1, copy to a
new dataset, transpose that, *then *merge into the existing dataset with
the already transposed data.

*My question:  *Does anyone have a better solution?  I'd like to be able to
tell SPSS to just look at row1 of the original dataset, and just "paste
vertically" (for lack of a better description); it seems a bit... tedious
(?) to go through the process of Selecting row1, copy to a new dataset,
transpose, merge, etc.  So much so, that I suspect there is a better way.
 Advice?

Thanks in advanced (hope I included enough clarity).

-J

[J. R. Carroll]

Yep.  Mia culpa.  I should have posted the syntax I've tried thus far (as David Marso and others have asked before).

FLIP VARIABLES=V2 V3 V4 V5 V6 V7 V8 V9 V10 V11 V12 V13 V14 V15 V16 V17 V18 V19 V20 V21 V22 V23 V24 
    V25 V26 V27 V28 V29 V30 V31 V32 V33 V34 V35 V36 V37 V38 V39 V40 V41 V42 V43 V44 V45 V46 V47 V48 V49 
    V50 V51 V52 V53 V54 V55 V56 V57 V58 V59 V60 V61 V62 V63 V64 V65 V66 V67 V68 V69 V70 V71 V72 V73 V74 
    V75 V76 V77 V78 V79 V80 V81 V82 V83 V84 V85 V86 V87 V88 V89 V90 V91 V92 V93 V94 V95 V96 V97 V98 V99 
    V100 V101 V102 V103 V104 V105 V106 V107 V108 V109 V110 V111 V112 V113 V114 V115 V116 V117 V118 V119 
    V120 V121 V122 V123 V124 V125 V126 V127 V128 V129 V130 V131 V132 V133 V134 V135 V136 V137 V138 V139 
    V140 V141 V142 V143 V144 V145 V146 V147 V148 V149 V150 V151 V152 V153 V154 V155 V156 V157 V158 V159 
    V160 V161 V162 V163 V164 V165 V166 V167 V168 V169 V170 V171 V172 V173 V174 V175 V176 V177 V178 V179 
    V180 V181 V182 V183 V184 V185 V186 V187 V188 V189 V190 V191 V192 V193
  /NEWNAMES=V1.
DATASET NAME FLIPPED WINDOW=FRONT.

Certainly not all of the variable names are needed, but that's cut/pasted directly from the syntax file.  If I use the above syntax, it will give me variables names (from column 1) but the data (numeric values) are transposed as sysmis.  As soon as I take out the first row (which are strings - user names) it properly uses the FLIP procedure in the way that I need it to work (but now I am left without the user names in it's own column).  

Hope that clarifies my situation a bit better...

-J

----


J. R. Carroll

Independent Researcher through Hurtz Labs

Research Methods, Test Development, and Statistics

www.jrcresearch.net

Cell:  [phone]

Email: [hidden email]

          [hidden email]

          [hidden email]

On Mon, Jan 23, 2012 at 1:06 PM, Albert-Jan Roskam <[hidden email]> wrote:

Hello,

Have you tried the command FLIP? No subcommands, just 'FLIP'.

 

Cheers!!

Albert-Jan

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a

fresh water system, and public health, what have the Romans ever done for us?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

---

From: J. R. Carroll <[hidden email]>
To: [hidden email]
Sent: Monday, January 23, 2012 9:05 PM
Subject: [SPSSX-L] Transposing data with mixed fields...

Listers,

I have a dataset where row1 has string data (Ss ID's), column1 has variable names, and the rest of it is data.

Example:

Username    John    Mark    Bob

V1    10    4    1   10

V2    19    2    3    4   

V3    12    2    4    1

V4    11    2    3    4

The dataset:  I need to transpose the data (so my column1 becomes my variable names), and the numerical data stored therein (where each column represents a person), gets 'flipped' so rows represent a participant (standard transposing).

The problem:  I have successfully transposed the data (not the usernames row), but only after figuring out that SPSS doesn't like dealing with columns that are set as "string", and the fields below it are mixed between string (only the top of the column) and numeric (everything else).  Once I stripped the string away, SPSS transposed the data, took column1 as the variable names, and allowed me to change all the numeric variables to numeric field type.  But now I am left with a column that says "Username" that is blank - due to my ineptness to successfully tell SPSS exactly what I wanted it to do.

My current solution:  In summary, I will "select" casenum 1, copy to a new dataset, transpose that, then merge into the existing dataset with the already transposed data.

My question:  Does anyone have a better solution?  I'd like to be able to tell SPSS to just look at row1 of the original dataset, and just "paste vertically" (for lack of a better description); it seems a bit... tedious (?) to go through the process of Selecting row1, copy to a new dataset, transpose, merge, etc.  So much so, that I suspect there is a better way.  Advice?

Thanks in advanced (hope I included enough clarity).   

-J

[J. R. Carroll-2]

Bruce,

I want the columns to be rows and rows to be columns - not sure how else to describe what I want it to look like.  David's example does the flip, in the same way I am doing it (but I still have the original issue, and maybe David's response addresses it, and it's is just too early on a Monday for me as well).  

To repost (with a properly adjusted number of variables), my data currently looks like:

Username    Cindy    John    Mark    Bob
V1    10    4    1   10
V2    19    2    3    4
V3    12    2    4    1
V4    11    2    3    4

What I need it to look like is:

Username    V1    V2    V3    V4
Cindy     10     19     12     11
John     4     2     2     2
Mark     1     3     4     3
Bob     10     4     1    4

////

David,

Thanks!  Your syntax did part of what I needed it to do (but you were manually entering in variable names, whereas my file had about 200, and I didn't want to put all of those into syntax).  Based on what you did, I was able to poke around to get it to perform how I needed it to!

This is what I ended up with:

GET TRANSLATE FILE= 'H:\CAARR\_RAW DATA for JAQ Nov 2011\CAARR_Survey_Data ALL Nov 2011.txt'

/type=TAB 

/fieldnames.

 

FLIP /NEWNAMES=username.

I went back to the original xls file and saved it out as a text/tab delim, then read the first line in as the variable names - then simple flip.  WAY easier than what I was trying to do!

-J

----


'J.' R. Carroll

Comira - 'Test Us!'

Psychometric Specialist

Office:  1-800-947-4228 xt 244

Cell:  [phone]

Email: [hidden email]

On Mon, Jan 23, 2012 at 1:11 PM, Bruce Weaver <[hidden email]> wrote:

Maybe I'm just a bit slow on a Monday afternoon (if not generally), but it
would help me if you showed what you want the data to look like after you've
finished.  We've got the "before" below.  Now show us the "after" please.
;-)



J. R. Carroll wrote

>
> Listers,
>
> I have a dataset where row1 has string data (Ss ID's), column1 has
> variable
> names, and the rest of it is data.
>

> *Example:*

>
> Username    John    Mark    Bob
> V1    10    4    1   10
> V2    19    2    3    4
> V3    12    2    4    1
> V4    11    2    3    4
>
>

> *The dataset:*  I need to transpose the data (so my column1 becomes my

> variable names), and the numerical data stored therein (where each column
> represents a person), gets 'flipped' so rows represent a participant
> (standard transposing).
>

> *The problem: * I have successfully transposed the data (not the
> *usernames
> row*), but only after figuring out that SPSS doesn't like dealing with

> columns that are set as "string", and the fields below it are mixed
> between
> string (only the top of the column) and numeric (everything else).  Once I
> stripped the string away, SPSS transposed the data, took column1 as the
> variable names, and allowed me to change all the numeric variables to
> numeric field type.  But now I am left with a column that says "Username"
> that is blank - due to my ineptness to successfully tell SPSS exactly what
> I wanted it to do.
>

> *My current solution: * In summary, I will "select" casenum 1, copy to a
> new dataset, transpose that, *then *merge into the existing dataset with
> the already transposed data.
>
> *My question:  *Does anyone have a better solution?  I'd like to be able

> to
> tell SPSS to just look at row1 of the original dataset, and just "paste
> vertically" (for lack of a better description); it seems a bit... tedious
> (?) to go through the process of Selecting row1, copy to a new dataset,
> transpose, merge, etc.  So much so, that I suspect there is a better way.
>  Advice?
>
> Thanks in advanced (hope I included enough clarity).
>
> -J
>

-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Transposing-data-with-mixed-fields-tp5169616p5178018.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

[J. R. Carroll]

...(replied to Bruce/ListServ under the wrong email - sorry if this gets posted twice)...

Bruce,

I want the columns to be rows and rows to be columns - not sure how else to describe what I want it to look like.  David's example does the flip, in the same way I am doing it (but I still have the original issue, and maybe David's response addresses it, and it's is just too early on a Monday for me as well).  

To repost (with a properly adjusted number of variables), my data currently looks like:

Username    Cindy    John    Mark    Bob

V1    10    4    1   10
V2    19    2    3    4
V3    12    2    4    1
V4    11    2    3    4

What I need it to look like is:

Username    V1    V2    V3    V4
Cindy     10     19     12     11
John     4     2     2     2
Mark     1     3     4     3
Bob     10     4     1    4

////

David,

Thanks!  Your syntax did part of what I needed it to do (but you were manually entering in variable names, whereas my file had about 200, and I didn't want to put all of those into syntax).  Based on what you did, I was able to poke around to get it to perform how I needed it to!

This is what I ended up with:

GET TRANSLATE FILE= 'H:\CAARR\_RAW DATA for JAQ Nov 2011\CAARR_Survey_Data ALL Nov 2011.txt'

/type=TAB 

/fieldnames.

 

FLIP /NEWNAMES=username.

I went back to the original xls file and saved it out as a text/tab delim, then read the first line in as the variable names - then simple flip.  WAY easier than what I was trying to do!

-J

On Mon, Jan 23, 2012 at 1:11 PM, Bruce Weaver <[hidden email]> wrote:

Maybe I'm just a bit slow on a Monday afternoon (if not generally), but it
would help me if you showed what you want the data to look like after you've
finished.  We've got the "before" below.  Now show us the "after" please.
;-)



J. R. Carroll wrote

>
> Listers,
>
> I have a dataset where row1 has string data (Ss ID's), column1 has
> variable
> names, and the rest of it is data.
>

> *Example:*

>
> Username    John    Mark    Bob
> V1    10    4    1   10
> V2    19    2    3    4
> V3    12    2    4    1
> V4    11    2    3    4
>
>

> *The dataset:*  I need to transpose the data (so my column1 becomes my

> variable names), and the numerical data stored therein (where each column
> represents a person), gets 'flipped' so rows represent a participant
> (standard transposing).
>

> *The problem: * I have successfully transposed the data (not the
> *usernames
> row*), but only after figuring out that SPSS doesn't like dealing with

> columns that are set as "string", and the fields below it are mixed
> between
> string (only the top of the column) and numeric (everything else).  Once I
> stripped the string away, SPSS transposed the data, took column1 as the
> variable names, and allowed me to change all the numeric variables to
> numeric field type.  But now I am left with a column that says "Username"
> that is blank - due to my ineptness to successfully tell SPSS exactly what
> I wanted it to do.
>

> *My current solution: * In summary, I will "select" casenum 1, copy to a
> new dataset, transpose that, *then *merge into the existing dataset with
> the already transposed data.
>
> *My question:  *Does anyone have a better solution?  I'd like to be able

> to
> tell SPSS to just look at row1 of the original dataset, and just "paste
> vertically" (for lack of a better description); it seems a bit... tedious
> (?) to go through the process of Selecting row1, copy to a new dataset,
> transpose, merge, etc.  So much so, that I suspect there is a better way.
>  Advice?
>
> Thanks in advanced (hope I included enough clarity).
>
> -J
>

-----
--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Transposing-data-with-mixed-fields-tp5169616p5178018.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]

If this is the representation in SPSS then your data *ARE STRINGS NOT NUMERIC!!!!!*
FLIP does *NOT* work with strings (it turns them SYSMIS).
My original question.  How did your data get FUBied in the first place?
If they for example came from Excel you can have SPSS use the first row as field names and then just FLIP.
If there is a variable called CASE_LBL then it will use that variable for the new variable names after the flip.

[J. R. Carroll]

You're right David.

I identified early on that FLIP did not like strings (which is why I was getting the sysmis).  The data are "FUBied" (assuming you meant screwed up?) for reasons beyond me; I just get the data in whatever state the client prepares it, and then I work with them on how to prepare it for me in the future - they gave me lemons and I made lemonade as best I could.  

For the purposes of my question and original problem, it was solved by reading the first line as variables then attempting the flip, whereas initially I was reading the entire file in, with no variable names, then trying to tell SPSS what/where the variable names were - I should have known better, but assumed this process was flawed 'out the gate'.  

All-the-same, problem solved; and again, thank you for pointing out that CASE_LBL in the variable dict will be used as a default in the FLIP procedure - that's very useful to know.

(hopefully I understood what you were asking, and answered it to your satisfaction).

-J

On Mon, Jan 23, 2012 at 2:56 PM, David Marso <[hidden email]> wrote:

If this is the representation in SPSS then your data *ARE STRINGS NOT
NUMERIC!!!!!*
FLIP does *NOT* work with strings (it turns them SYSMIS).
My original question.  How did your data get FUBied in the first place?
If they for example came from Excel you can have SPSS use the first row as
field names and then just FLIP.
If there is a variable called CASE_LBL then it will use that variable for
the new variable names after the flip.


--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Transposing-data-with-mixed-fields-tp5169616p5198876.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

[Albert-Jan Roskam]

FUBied? The correct conjugation is FUBARified ;-))

 

Cheers!!

Albert-Jan

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

All right, but apart from the sanitation, the medicine, education, wine, public order, irrigation, roads, a fresh

water system, and public health, what have the Romans ever done for us?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

From: J. R. Carroll <[hidden email]>
To: [hidden email]
Sent: Tuesday, January 24, 2012 12:10 AM
Subject: Re: [SPSSX-L] Transposing data with mixed fields...

You're right David.

I identified early on that FLIP did not like strings (which is why I was getting the sysmis).  The data are "FUBied" (assuming you meant screwed up?) for reasons beyond me; I just get the data in whatever state the client prepares it, and then I work with them on how to prepare it for me in the future - they gave me lemons and I made lemonade as best I could.  

For the purposes of my question and original problem, it was solved by reading the first line as variables then attempting the flip, whereas initially I was reading the entire file in, with no variable names, then trying to tell SPSS what/where the variable names were - I should have known better, but assumed this process was flawed 'out the gate'.  

All-the-same, problem solved; and again, thank you for pointing out that CASE_LBL in the variable dict will be used as a default in the FLIP procedure - that's very useful to know.

(hopefully I understood what you were asking, and answered it to your satisfaction).

-J

On Mon, Jan 23, 2012 at 2:56 PM, David Marso <[hidden email]> wrote:

If this is the representation in SPSS then your data *ARE STRINGS NOT
NUMERIC!!!!!*
FLIP does *NOT* work with strings (it turns them SYSMIS).
My original question.  How did your data get FUBied in the first place?
If they for example came from Excel you can have SPSS use the first row as
field names and then just FLIP.
If there is a variable called CASE_LBL then it will use that variable for
the new variable names after the flip.


--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Transposing-data-with-mixed-fields-tp5169616p5198876.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]

Correct that the term is derived from FUBAR ;-)
It originated in my head one morning when my girlfriend attempted
Huevos Rancheros .
Chips were burnt, the whites weren't set and the yolks were broken.
I looked at her and said damn woman, you Fubied the Huevos Rancheros.
She said yeah, they are totally FUBAR (we ate them anyway)!
BTW:  I do 99% of the cooking around here.  I love to do it.  She
hates to do it.

On Tue, Jan 24, 2012 at 5:20 AM, Albert-Jan Roskam [via SPSSX
Discussion] <[hidden email]> wrote:

> FUBied? The correct conjugation is FUBARified ;-))
>
> Cheers!!
> Albert-Jan
>  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> All right, but apart from the sanitation, the medicine, education, wine,
> public order, irrigation, roads, a fresh
> water system, and public health, what have the Romans ever done for us?
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
> From: J. R. Carroll <[hidden email]>
> To: [hidden email]
> Sent: Tuesday, January 24, 2012 12:10 AM
> Subject: Re: [SPSSX-L] Transposing data with mixed fields...
>
> You're right David.
>
> I identified early on that FLIP did not like strings (which is why I was
> getting the sysmis).  The data are "FUBied" (assuming you meant screwed up?)
> for reasons beyond me; I just get the data in whatever state the client
> prepares it, and then I work with them on how to prepare it for me in the
> future - they gave me lemons and I made lemonade as best I could.
>
> For the purposes of my question and original problem, it was solved by
> reading the first line as variables then attempting the flip, whereas
> initially I was reading the entire file in, with no variable names, then
> trying to tell SPSS what/where the variable names were - I should have known
> better, but assumed this process was flawed 'out the gate'.
>
> All-the-same, problem solved; and again, thank you for pointing out that
> CASE_LBL in the variable dict will be used as a default in the FLIP
> procedure - that's very useful to know.
>
> (hopefully I understood what you were asking, and answered it to your
> satisfaction).
>
> -J
>
>
>
>
> On Mon, Jan 23, 2012 at 2:56 PM, David Marso <[hidden email]> wrote:
>
> If this is the representation in SPSS then your data *ARE STRINGS NOT
> NUMERIC!!!!!*
> FLIP does *NOT* work with strings (it turns them SYSMIS).
> My original question.  How did your data get FUBied in the first place?
> If they for example came from Excel you can have SPSS use the first row as
> field names and then just FLIP.
> If there is a variable called CASE_LBL then it will use that variable for
> the new variable names after the flip.
>
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Transposing-data-with-mixed-fields-tp5169616p5198876.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
>
>
>
>
>
>
> ________________________________
> If you reply to this email, your message will be added to the discussion
> below:
> http://spssx-discussion.1045642.n5.nabble.com/Transposing-data-with-mixed-fields-tp5169616p5281296.html
> To unsubscribe from Transposing data with mixed fields..., click here.
> NAML

[Bruce Weaver]

Thanks for clarifying.  One more question:  What format is that original file in when you receive it?

J. R. Carroll-2 wrote

Bruce,

I want the columns to be rows and rows to be columns - not sure how else to
describe what I want it to look like.  David's example does the flip, in
the same way I am doing it (but I still have the original issue, and maybe
David's response addresses it, and it's is just too early on a Monday for
me as well).

To repost (with a properly adjusted number of variables), my data currently
looks like:

Username    Cindy    John    Mark    Bob
> V1    10    4    1   10
> V2    19    2    3    4
> V3    12    2    4    1
> V4    11    2    3    4


What I need it to look like is:

Username    V1    V2    V3    V4
> Cindy     10     19     12     11
> John     4     2     2     2
> Mark     1     3     4     3
> Bob     10     4     1    4


////

David,

Thanks!  Your syntax did part of what I needed it to do (but you were
manually entering in variable names, whereas my file had about 200, and I
didn't want to put all of those into syntax).  Based on what you did, I was
able to poke around to get it to perform how I needed it to!

This is what I ended up with:

GET TRANSLATE FILE= 'H:\CAARR\_RAW DATA for JAQ Nov 2011\CAARR_Survey_Data
> ALL Nov 2011.txt'

/type=TAB

/fieldnames.



FLIP /NEWNAMES=username.


I went back to the original xls file and saved it out as a text/tab delim,
then read the first line in as the variable names - then simple flip.  WAY
easier than what I was trying to do!

-J


----


'J.' R. Carroll
Comira - 'Test Us!'
Psychometric Specialist
Office:  1-800-947-4228 xt 244
Cell:  [phone]
Email: [hidden email]



On Mon, Jan 23, 2012 at 1:11 PM, Bruce Weaver <[hidden email]>wrote:

> Maybe I'm just a bit slow on a Monday afternoon (if not generally), but it
> would help me if you showed what you want the data to look like after
> you've
> finished.  We've got the "before" below.  Now show us the "after" please.
> ;-)
>
>
>
> J. R. Carroll wrote
> >
> > Listers,
> >
> > I have a dataset where row1 has string data (Ss ID's), column1 has
> > variable
> > names, and the rest of it is data.
> >
> > *Example:*
> >
> > Username    John    Mark    Bob
> > V1    10    4    1   10
> > V2    19    2    3    4
> > V3    12    2    4    1
> > V4    11    2    3    4
> >
> >
> > *The dataset:*  I need to transpose the data (so my column1 becomes my
> > variable names), and the numerical data stored therein (where each column
> > represents a person), gets 'flipped' so rows represent a participant
> > (standard transposing).
> >
> > *The problem: * I have successfully transposed the data (not the
> > *usernames
> > row*), but only after figuring out that SPSS doesn't like dealing with
> > columns that are set as "string", and the fields below it are mixed
> > between
> > string (only the top of the column) and numeric (everything else).  Once
> I
> > stripped the string away, SPSS transposed the data, took column1 as the
> > variable names, and allowed me to change all the numeric variables to
> > numeric field type.  But now I am left with a column that says "Username"
> > that is blank - due to my ineptness to successfully tell SPSS exactly
> what
> > I wanted it to do.
> >
> > *My current solution: * In summary, I will "select" casenum 1, copy to a
> > new dataset, transpose that, *then *merge into the existing dataset with
> > the already transposed data.
> >
> > *My question:  *Does anyone have a better solution?  I'd like to be able
> > to
> > tell SPSS to just look at row1 of the original dataset, and just "paste
> > vertically" (for lack of a better description); it seems a bit... tedious
> > (?) to go through the process of Selecting row1, copy to a new dataset,
> > transpose, merge, etc.  So much so, that I suspect there is a better way.
> >  Advice?
> >
> > Thanks in advanced (hope I included enough clarity).
> >
> > -J
> >
>
>
> -----
> --
> Bruce Weaver
> [hidden email]
> http://sites.google.com/a/lakeheadu.ca/bweaver/
>
> "When all else fails, RTFM."
>
> NOTE: My Hotmail account is not monitored regularly.
> To send me an e-mail, please use the address shown above.
>
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Transposing-data-with-mixed-fields-tp5169616p5178018.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
>