Converting scale value into binary columns

[Juliana-7]

It would be great if anyone could help me out with the following:

I have a series of contact dates such as

Jan02  May02  July02  Aug02

I would eventually like to convert this into binary coding month-by-month
such that if a contact occurred in a given month the value is 1 and
otherwise it is 0. I calculated the difference between the contact dates
(in months) such that I have the following:


datediff1 datediff2 datediff3 datediff4
1         4         2         1


I would like to conver this into a new series of columns with (n-1) zeroes
in between ones such that the above would become:

1  0  0  0  1  0  1  1

This would reflect the months January to August, with a 1 for a contact
and a 0 for no contact.

Again, if anyone can help me out, I would appreciate it.
Thanks!

[Marks, Jim]

Here is a solution-- I assume you have multiple contacts per row.


*** sample data.
DATA LIST LIST (",") /id (f8.0) contact_1 TO contact_4  (4moyr8).
BEGIN DATA
1 , JAN 2002,  MAY 2002, JUL 2002, AUG 2002
2 , MAR 2003, APR 2003,
3 , FEB 2002, JUN 2002, OCT 2002, DEC 2002
END DATA.


**** create a variable for each contact date with the month of the
contact.
DO REPEAT
  x = contact_1 to contact_4
  /y = cmth_1 to cmth_4.
COMPUTE y = xdate.month(x).
END REPEAT.

EXECUTE.

*** loop thru the contact months and create a month variable mth_1 =
January).

VECTOR cm = cmth_1 TO cmth_4.
VECTOR mth_(12F8.0).
LOOP #j = 1 TO 4.
LOOP #i = 1 TO 12.
IF  cm(#j) = (#i) mth_(#i) = 1.
END LOOP.
END LOOP.

RECODE mth_1 to mth_12 (sysmis = 0).
EXECUTE.

The loop for #j and the do repeat should match the nbr of contacts per
row

Cheers
--jim



-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Juliana
Sent: Thursday, June 21, 2007 9:29 AM
To: [hidden email]
Subject: Converting scale value into binary columns

It would be great if anyone could help me out with the following:

I have a series of contact dates such as

Jan02  May02  July02  Aug02

I would eventually like to convert this into binary coding
month-by-month such that if a contact occurred in a given month the
value is 1 and otherwise it is 0. I calculated the difference between
the contact dates (in months) such that I have the following:


datediff1 datediff2 datediff3 datediff4
1         4         2         1


I would like to conver this into a new series of columns with (n-1)
zeroes in between ones such that the above would become:

1  0  0  0  1  0  1  1

This would reflect the months January to August, with a 1 for a contact
and a 0 for no contact.

Again, if anyone can help me out, I would appreciate it.
Thanks!

[Juliana-7]

Thanks for that solution. The format is what I am looking for, but I need
the cmth variables to be different depending on the subject's start date.
For example, I would want the sample data you gave:

>*** sample data.
>DATA LIST LIST (",") /id (f8.0) contact_1 TO contact_4  (4moyr8).
>BEGIN DATA
>1 , JAN 2002,  MAY 2002, JUL 2002, AUG 2002
>2 , MAR 2003, APR 2003,
>3 , FEB 2002, JUN 2002, OCT 2002, DEC 2002
>END DATA.

to end up as:
1 , 1 0 0 0 1 0 1 1 0 0
2 , 1 1 0 0 0 0 0 0 0 0
3 , 1 0 0 1 0 0 0 1 0 1

Also, there is 5 years worth of data, so I'm not sure how it would work
after the first 12 months. I have been trying to work with a series
of 'datediff' variables that denotes the difference between the date
series in months. So the above data would be:

1 , 4 2 1
2 , 1
3 , 3 4 2

Is there a way I can do this?

Thanks for your help!

[Marks, Jim]

The sequence is based on the start of each case, not the start of a
calendar year. (Case 3 and case 9 have the same pattern in different
years, so they have the same output)

The sequence can be extended to multiple years by extending the VECTOR
for mth_ and LOOP for #i beyond 12 to 60 (or 72 if there is activity at
5 yrs 8 months for e.g.

Here is a solution:

**** more sample data.

DATA LIST LIST (",") /id (f8.0) contact_1 TO contact_4  (4moyr8).
BEGIN DATA
1 , JAN 2002,  MAY 2002, JUL 2002, AUG 2002
2 , MAR 2003, APR 2003,
3 , FEB 2002, JUN 2002, OCT 2002, DEC 2002
4 , FEB 2002, SEP 2002, MAR 2003, OCT 2004
5 , FEB 2004, JUN 2005, OCT 2006, DEC 2006
6 , FEB 2002, JUN 2005, JUL 2005, FEB 2007
7 , NOV 2002, DEC 2002, MAY 2006,
8 , JAN 2002, JAN 2004, JAN 2005, OCT 2007
9 , FEB 2003, JUN 2003, OCT 2003, DEC 2003
END DATA.


*** calculate the difference in months
***     (+1 to include the 1st month, and get the correct place in the
sequence).

DO REPEAT
  x = contact_1 to contact_4
  /y = cmth_1 to cmth_4.
COMPUTE y = DATEDIFF(x,contact_1,"months")+1 .
END REPEAT.

EXECUTE.

*** loop thru the contact months and create a month variable
***  mth_1 = FIRST MONTH FOR THE CASE not the calendar).

VECTOR cm = cmth_1 TO cmth_4.
VECTOR mth_(72F8.0).
LOOP #j = 1 TO 4.
LOOP #i = 1 TO 72.
IF  cm(#j) = (#i) mth_(#i) = 1.
END LOOP.
END LOOP.

RECODE mth_1 to mth_72 (sysmis = 0).
EXECUTE.

Remember,
        the DO REPEAT ; VECTOR "cm = " ; and LOOP "#j =" can be extended
for more contacts per case
        The VECTOR mth_ and LOOP "#i =" can be extended for additional
months to analyse

--jim






-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of
Juliana
Sent: Friday, June 22, 2007 8:40 AM
To: [hidden email]
Subject: Re: Converting scale value into binary columns

Thanks for that solution. The format is what I am looking for, but I
need the cmth variables to be different depending on the subject's start
date.
For example, I would want the sample data you gave:

>*** sample data.
>DATA LIST LIST (",") /id (f8.0) contact_1 TO contact_4  (4moyr8).
>BEGIN DATA
>1 , JAN 2002,  MAY 2002, JUL 2002, AUG 2002
>2 , MAR 2003, APR 2003,
>3 , FEB 2002, JUN 2002, OCT 2002, DEC 2002 END DATA.

to end up as:
1 , 1 0 0 0 1 0 1 1 0 0
2 , 1 1 0 0 0 0 0 0 0 0
3 , 1 0 0 1 0 0 0 1 0 1

Also, there is 5 years worth of data, so I'm not sure how it would work
after the first 12 months. I have been trying to work with a series of
'datediff' variables that denotes the difference between the date series
in months. So the above data would be:

1 , 4 2 1
2 , 1
3 , 3 4 2

Is there a way I can do this?

Thanks for your help!