SPSSX Discussion

Data restructure

Classic

List

Threaded

13 messages Options

GauravSrivastava

Data restructure

Hi All,

I am trying to restructure my below data.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101 */ attribute 3rd 4th 6th 7th 8th and 10th selected by 1st case
1110110110
1100110011
end data.

I have to create final data which look like
030406070810 */ I am placing 03 in 1st variable 04 in 2nd variable for 3rd attribute 4th attribute ect. selected by 1st case
0102030506080910 */ same as above
010205060910
01020304050607080910

I have to do this repeatedly. Can anyone help me with some script. Thanks for giving your valuable time.

Regards,
Gaurav

Johan Pauwels

Re: Data restructure

This should work.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101 */ attribute 3rd 4th 6th 7th 8th and 10th selected by 1st case
1110110110
1100110011
end data.

*create additional variables.
Numeric tv1 to tv10.

*fills tv1 to tv10 with 'numeric' values.

Do repeat xx=A1 to A10/yy=tv1 to tv10/zz=1 to 10.
If (xx=1) yy=zz.
If (xx=0) yy=0.
End repeat.
exe.

*Moves all to beginning.
loop #x=1 to 10.
do repeat xx= tv1 to tv9/ yy=tv2 to tv10.
do if (xx=0 and yy<>0).
compute xx=yy.
compute yy=0.
end if.
end repeat.
end loop.
exe.

GauravSrivastava

Re: Data restructure

Hi Johan,

Thanks for this. It's working fine but when it get some missing value for any case, i noticed some irregularities.

would it not work when we have missing value in data.

Gaurav

On Mon, Oct 29, 2012 at 10:21 PM, Johan Pauwels <[hidden email]> wrote:

This should work.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101 */ attribute 3rd 4th 6th 7th 8th and 10th selected by 1st case
1110110110
1100110011
end data.

*create additional variables.
Numeric tv1 to tv10.

*fills tv1 to tv10 with 'numeric' values.

Do repeat xx=A1 to A10/yy=tv1 to tv10/zz=1 to 10.
If (xx=1) yy=zz.
If (xx=0) yy=0.
End repeat.
exe.

*Moves all to beginning.
loop #x=1 to 10.
do repeat xx= tv1 to tv9/ yy=tv2 to tv10.
do if (xx=0 and yy<>0).
compute xx=yy.
compute yy=0.
end if.
end repeat.
end loop.
exe.

--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Data-restructure-tp5715924p5715925.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

GauravSrivastava

Re: Data restructure

In reply to this post by Johan Pauwels

Hi Johan,

Thanks for this. It's working fine but when it get some missing value for any case, i noticed some irregularities.
would it not work when we have missing value in data.

Gaurav

Bruce Weaver

Re: Data restructure

Administrator

In reply to this post by GauravSrivastava

You haven't said so explicitly, but it looks like you want your new variable to be a string. Try this:

NEW FILE.
DATASET close all.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101
1110110110
1100110011
1111111111
0000000000
end data.

STRING NewVar #key (a20) .
COMPUTE #key = "01020304050607080910".
COMPUTE NewVar = "".
DO REPEAT a = A1 to A10 / i = 1 to 10.
- IF (a EQ 1) NewVar = concat(RTRIM(NewVar),char.substr(#key,i*2-1,2)).
END REPEAT PRINT.
LIST.

Output:

A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 NewVar

0 0 1 1 0 1 1 1 0 1 030406070810
1 1 1 0 1 1 0 1 1 0 01020305060809
1 1 0 0 1 1 0 0 1 1 010205060910
1 1 1 1 1 1 1 1 1 1 01020304050607080910
0 0 0 0 0 0 0 0 0 0

Number of cases read: 5 Number of cases listed: 5

Notice that the RTRIM in that CONCAT is crucial. I omitted it at first, and couldn't work out why NewVar was blank for all cases. ;-)

HTH.

GauravSrivastava wrote

Hi All,

I am trying to restructure my below data.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101 */ attribute 3rd 4th 6th 7th 8th and 10th selected by 1st case
1110110110
1100110011
end data.

I have to create final data which look like
030406070810 */ I am placing 03 in 1st variable 04 in 2nd variable for 3rd attribute 4th attribute ect. selected by 1st case
0102030506080910 */ same as above
010205060910
01020304050607080910

I have to do this repeatedly. Can anyone help me with some script. Thanks for giving your valuable time.

Regards,
Gaurav

--
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/).

GauravSrivastava

Re: Data restructure

Hi Bruce,

Thanks for your reply, but I don't have to concatenate my variable. I want exactly what Johan's replied, but in that script if we have a missing value for any case, it wouldn't work correctly.

Regards,

Gaurav

On Tue, Oct 30, 2012 at 12:24 AM, Bruce Weaver <[hidden email]> wrote:

You haven't said so explicitly, but it looks like you want your new variable
to be a string. Try this:

NEW FILE.
DATASET close all.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101
1110110110
1100110011
1111111111
0000000000
end data.

STRING NewVar #key (a20) .
COMPUTE #key = "01020304050607080910".
COMPUTE NewVar = "".
DO REPEAT a = A1 to A10 / i = 1 to 10.
- IF (a EQ 1) NewVar = concat(RTRIM(NewVar),char.substr(#key,i*2-1,2)).
END REPEAT PRINT.
LIST.

Output:

A1 A2 A3 A4 A5 A6 A7 A8 A9 A10 NewVar

0 0 1 1 0 1 1 1 0 1 030406070810
1 1 1 0 1 1 0 1 1 0 01020305060809
1 1 0 0 1 1 0 0 1 1 010205060910
1 1 1 1 1 1 1 1 1 1 01020304050607080910
0 0 0 0 0 0 0 0 0 0

Number of cases read: 5 Number of cases listed: 5

Notice that the RTRIM in that CONCAT is crucial. I omitted it at first, and
couldn't work out why NewVar was blank for all cases. ;-)

HTH.

GauravSrivastava wrote
> Hi All,
>
> I am trying to restructure my below data.
>
> DATA LIST /A1 TO A10 1-10.
> begin data
> 0011011101 */ attribute 3rd 4th 6th 7th 8th and 10th selected by 1st case
> 1110110110
> 1100110011
> end data.
>
> I have to create final data which look like
> 030406070810 */ I am placing 03 in 1st variable 04 in 2nd variable for 3rd
> attribute 4th attribute ect. selected by 1st case
> 0102030506080910 */ same as above
> 010205060910
> 01020304050607080910
>
> I have to do this repeatedly. Can anyone help me with some script. Thanks
> for giving your valuable time.
>
> Regards,
> Gaurav

-----
--
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/Data-restructure-tp5715924p5715928.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

Jon K Peck

Re: Data restructure

In reply to this post by Bruce Weaver

[snip] Notice that the RTRIM in that CONCAT is crucial. I omitted it at first, and couldn't work out why NewVar was blank for all cases. ;-) >>>In Unicode mode, there is no need for the rtrim calls, because strings are automatically trimmed.

-Jon Peck

HTH. GauravSrivastava wrote > Hi All, > > I am trying to restructure my below data. > > DATA LIST /A1 TO A10 1-10. > begin data > 0011011101 */ attribute 3rd 4th 6th 7th 8th and 10th selected by 1st case > 1110110110 > 1100110011 > end data. > > I have to create final data which look like > 030406070810 */ I am placing 03 in 1st variable 04 in 2nd variable for 3rd > attribute 4th attribute ect. selected by 1st case > 0102030506080910 */ same as above > 010205060910 > 01020304050607080910 > > I have to do this repeatedly. Can anyone help me with some script. Thanks > for giving your valuable time. > > Regards, > Gaurav ----- -- 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/Data-restructure-tp5715924p5715928.htmlSent 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

Re: Data restructure

Administrator

In reply to this post by GauravSrivastava

You need to define what you are experiencing with a specific example!!!

GauravSrivastava wrote

Hi Johan,

Thanks for this. It's working fine but when it get some ??missing value?? for any case, i noticed some ??irregularities??.
would it ??not work?? when we have missing value in data.

Gaurav

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

GauravSrivastava

Re: Data restructure

HI David,

See below script. I have added missing value for case 1 and 3 highlighted in yellow.

DATA LIST /A1 TO A10 1-10.

begin data

00110 1101

1110110110

110011 11

end data.

Do repeat xx=A1 to A10/yy=tv1 to tv10/zz=1 to 10.

If (xx=1) yy=zz.

If (xx=0) yy=0.

End repeat.

exe.

*Moves all to beginning.

loop #x=1 to 10.

do repeat xx= tv1 to tv9/ yy=tv2 to tv10.

do if (xx=0 and yy<>0).

compute xx=yy.

compute yy=0.

end if.

end repeat.

end loop.

exe.

and output is coming like this:

3.00 4.00 .00 .00 .00 7.00 8.00 10.00 .00

1.00 2.00 3.00 5.00 6.00 8.00 9.00 .00 .00 .00

1.00 2.00 5.00 6.00 .00 .00 9.00 10.00

I need it like 3.00 4.00 7.00 8.00 10.00 for 1st case starting from tv1 to tv5 same for case 3rd (1,2,5,6,9,10).

Hope this make sense.

Regards,

Gaurav

On Tue, Oct 30, 2012 at 12:40 AM, David Marso <[hidden email]> wrote:

You need to define what you are experiencing with a specific example!!!

GauravSrivastava wrote
> Hi Johan,
>
> Thanks for this. It's working fine but when it get some ??
*
> missing value
*
> ?? for any case, i noticed some ??
*
> irregularities
*
> ??.
> would it ??
*
> not work
*
> ?? when we have missing value in data.
>
> Gaurav

-----
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Data-restructure-tp5715924p5715930.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

GauravSrivastava

Re: Data restructure

In reply to this post by David Marso

Thanks all,
I slightly change the code for missing value now it's working perfect.

DATA LIST /A1 TO A10 1-10.
begin data
00110 1101
1110110110
110011 11
end data.
Do repeat xx=A1 to A10/yy=tv1 to tv10/zz=1 to 10.
If (xx=1) yy=zz.
If (xx=0) yy=0.
End repeat.
exe.

*Moves all to beginning.
loop #x=1 to 10.
do repeat xx= tv1 to tv9/ yy=tv2 to tv10.
do if ((xx=0 or sysmiss(xx))and yy<>0).
compute xx=yy.
compute yy=0.
end if.
end repeat.
end loop.
exe.

Regards,
Gaurav

Bruce Weaver

Re: Data restructure

Administrator

In reply to this post by GauravSrivastava

It *now* appears that you want (up to) 10 separate variables.

NEW FILE.
DATASET close all.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101
1110110110
1100110011
1111111111
0000000000
end data.

VECTOR New(10).
STRING #key(a20).
COMPUTE #key = "01020304050607080910".
COMPUTE # = 0.
DO REPEAT a = A1 to A10 / i = 1 to 10.
DO IF (a EQ 1).
- COMPUTE # = # + 1.
- COMPUTE New(#) = number(substr(#key,i*2-1,2),f2).
END IF.
END REPEAT PRINT.
FORMATS New1 to New10 (f2.0).
LIST New1 to New10.

Output:

New1 New2 New3 New4 New5 New6 New7 New8 New9 New10

3 4 6 7 8 10 . . . .
1 2 3 5 6 8 9 . . .
1 2 5 6 9 10 . . . .
1 2 3 4 5 6 7 8 9 10
. . . . . . . . . .

Number of cases read: 5 Number of cases listed: 5

GauravSrivastava wrote

HI David,

See below script. I have added missing value for case 1 and 3 highlighted
in yellow.

DATA LIST /A1 TO A10 1-10.
begin data
00110 1101
1110110110
110011 11
end data.
Do repeat xx=A1 to A10/yy=tv1 to tv10/zz=1 to 10.
If (xx=1) yy=zz.
If (xx=0) yy=0.
End repeat.
exe.

*Moves all to beginning.
loop #x=1 to 10.
do repeat xx= tv1 to tv9/ yy=tv2 to tv10.
do if (xx=0 and yy<>0).
compute xx=yy.
compute yy=0.
end if.
end repeat.
end loop.
exe.

and output is coming like this:

3.00 4.00 .00 .00 .00 7.00 8.00 10.00 .00
1.00 2.00 3.00 5.00 6.00 8.00 9.00 .00 .00 .00
1.00 2.00 5.00 6.00 .00 .00 9.00 10.00

I need it like 3.00 4.00 7.00 8.00 10.00 for 1st case starting from tv1 to
tv5 same for case 3rd (1,2,5,6,9,10).

Hope this make sense.

Regards,
Gaurav

On Tue, Oct 30, 2012 at 12:40 AM, David Marso <[hidden email]> wrote:

> You need to define what you are experiencing with a specific example!!!
>
> GauravSrivastava wrote
> > Hi Johan,
> >
> > Thanks for this. It's working fine but when it get some ??
> *
> > missing value
> *
> > ?? for any case, i noticed some ??
> *
> > irregularities
> *
> > ??.
> > would it ??
> *
> > not work
> *
> > ?? when we have missing value in data.
> >
> > Gaurav
>
>
>
>
>
> -----
> Please reply to the list and not to my personal email.
> Those desiring my consulting or training services please feel free to
> email me.
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Data-restructure-tp5715924p5715930.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

Re: Data restructure

Administrator

VECTOR New(10).
COMPUTE # = 0.
DO REPEAT a = A1 to A10 / i = 1 to 10.
- DO IF (a EQ 1).
- COMPUTE # = # + 1.
- COMPUTE New(#) = i.
- END IF.
END REPEAT .
* OR*
VECTOR New(10) / a=a1 TO a10.
COMPUTE # = 0.
LOOP i = 1 to 10.
- DO IF (a(i) EQ 1).
- COMPUTE # = # + 1.
- COMPUTE New(#) = i.
- END IF.
END LOOP.

Bruce Weaver wrote

It *now* appears that you want (up to) 10 separate variables.

NEW FILE.
DATASET close all.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101
1110110110
1100110011
1111111111
0000000000
end data.

VECTOR New(10).
STRING #key(a20).
COMPUTE #key = "01020304050607080910".
COMPUTE # = 0.
DO REPEAT a = A1 to A10 / i = 1 to 10.
DO IF (a EQ 1).
- COMPUTE # = # + 1.
- COMPUTE New(#) = number(substr(#key,i*2-1,2),f2).
END IF.
END REPEAT PRINT.
FORMATS New1 to New10 (f2.0).
LIST New1 to New10.

Output:

New1 New2 New3 New4 New5 New6 New7 New8 New9 New10

3 4 6 7 8 10 . . . .
1 2 3 5 6 8 9 . . .
1 2 5 6 9 10 . . . .
1 2 3 4 5 6 7 8 9 10
. . . . . . . . . .

Number of cases read: 5 Number of cases listed: 5

GauravSrivastava wrote

HI David,

See below script. I have added missing value for case 1 and 3 highlighted
in yellow.

DATA LIST /A1 TO A10 1-10.
begin data
00110 1101
1110110110
110011 11
end data.
Do repeat xx=A1 to A10/yy=tv1 to tv10/zz=1 to 10.
If (xx=1) yy=zz.
If (xx=0) yy=0.
End repeat.
exe.

*Moves all to beginning.
loop #x=1 to 10.
do repeat xx= tv1 to tv9/ yy=tv2 to tv10.
do if (xx=0 and yy<>0).
compute xx=yy.
compute yy=0.
end if.
end repeat.
end loop.
exe.

and output is coming like this:

3.00 4.00 .00 .00 .00 7.00 8.00 10.00 .00
1.00 2.00 3.00 5.00 6.00 8.00 9.00 .00 .00 .00
1.00 2.00 5.00 6.00 .00 .00 9.00 10.00

I need it like 3.00 4.00 7.00 8.00 10.00 for 1st case starting from tv1 to
tv5 same for case 3rd (1,2,5,6,9,10).

Hope this make sense.

Regards,
Gaurav

On Tue, Oct 30, 2012 at 12:40 AM, David Marso <[hidden email]> wrote:

> You need to define what you are experiencing with a specific example!!!
>
> GauravSrivastava wrote
> > Hi Johan,
> >
> > Thanks for this. It's working fine but when it get some ??
> *
> > missing value
> *
> > ?? for any case, i noticed some ??
> *
> > irregularities
> *
> > ??.
> > would it ??
> *
> > not work
> *
> > ?? when we have missing value in data.
> >
> > Gaurav
>
>
>
>
>
> -----
> Please reply to the list and not to my personal email.
> Those desiring my consulting or training services please feel free to
> email me.
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Data-restructure-tp5715924p5715930.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
>

Bruce Weaver

Re: Data restructure

Administrator

DM: "- COMPUTE New(#) = i."

Right. That's what I meant. :-|

Crikey...I had a Rube Goldberg moment there, didn't I! LOL

David Marso wrote

VECTOR New(10).
COMPUTE # = 0.
DO REPEAT a = A1 to A10 / i = 1 to 10.
- DO IF (a EQ 1).
- COMPUTE # = # + 1.
- COMPUTE New(#) = i.
- END IF.
END REPEAT .
* OR*
VECTOR New(10) / a=a1 TO a10.
COMPUTE # = 0.
LOOP i = 1 to 10.
- DO IF (a(i) EQ 1).
- COMPUTE # = # + 1.
- COMPUTE New(#) = i.
- END IF.
END LOOP.

Bruce Weaver wrote

It *now* appears that you want (up to) 10 separate variables.

NEW FILE.
DATASET close all.

DATA LIST /A1 TO A10 1-10.
begin data
0011011101
1110110110
1100110011
1111111111
0000000000
end data.

VECTOR New(10).
STRING #key(a20).
COMPUTE #key = "01020304050607080910".
COMPUTE # = 0.
DO REPEAT a = A1 to A10 / i = 1 to 10.
DO IF (a EQ 1).
- COMPUTE # = # + 1.
- COMPUTE New(#) = number(substr(#key,i*2-1,2),f2).
END IF.
END REPEAT PRINT.
FORMATS New1 to New10 (f2.0).
LIST New1 to New10.

Output:

New1 New2 New3 New4 New5 New6 New7 New8 New9 New10

3 4 6 7 8 10 . . . .
1 2 3 5 6 8 9 . . .
1 2 5 6 9 10 . . . .
1 2 3 4 5 6 7 8 9 10
. . . . . . . . . .

Number of cases read: 5 Number of cases listed: 5

GauravSrivastava wrote

HI David,

See below script. I have added missing value for case 1 and 3 highlighted
in yellow.

DATA LIST /A1 TO A10 1-10.
begin data
00110 1101
1110110110
110011 11
end data.
Do repeat xx=A1 to A10/yy=tv1 to tv10/zz=1 to 10.
If (xx=1) yy=zz.
If (xx=0) yy=0.
End repeat.
exe.

*Moves all to beginning.
loop #x=1 to 10.
do repeat xx= tv1 to tv9/ yy=tv2 to tv10.
do if (xx=0 and yy<>0).
compute xx=yy.
compute yy=0.
end if.
end repeat.
end loop.
exe.

and output is coming like this:

3.00 4.00 .00 .00 .00 7.00 8.00 10.00 .00
1.00 2.00 3.00 5.00 6.00 8.00 9.00 .00 .00 .00
1.00 2.00 5.00 6.00 .00 .00 9.00 10.00

I need it like 3.00 4.00 7.00 8.00 10.00 for 1st case starting from tv1 to
tv5 same for case 3rd (1,2,5,6,9,10).

Hope this make sense.

Regards,
Gaurav

On Tue, Oct 30, 2012 at 12:40 AM, David Marso <[hidden email]> wrote:

> You need to define what you are experiencing with a specific example!!!
>
> GauravSrivastava wrote
> > Hi Johan,
> >
> > Thanks for this. It's working fine but when it get some ??
> *
> > missing value
> *
> > ?? for any case, i noticed some ??
> *
> > irregularities
> *
> > ??.
> > would it ??
> *
> > not work
> *
> > ?? when we have missing value in data.
> >
> > Gaurav
>
>
>
>
>
> -----
> Please reply to the list and not to my personal email.
> Those desiring my consulting or training services please feel free to
> email me.
> --
> View this message in context:
> http://spssx-discussion.1045642.n5.nabble.com/Data-restructure-tp5715924p5715930.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
>