change varying records into one record per individual

[msherman]

Dear list: I have the following situation.
I have students applying to anywhere from 1 to 111 schools. If they apply to one school they only have one record, but if they apply to more schools then they will have a record for each school they apply to. In addition, for each school they apply to I have whether the school accepted them (0 vs 1-yes) and also whether they attended that school (0 vs. 1-yes). I can add up these two variables and come up with missing, 0, 1, or 2.  Missing being that they did not apply to that school. 0 is they applied but was not accepted, 1 if the were accepted but did not enroll, and 2 if they were accepted and enrolled. I now want to have a single record for each student so that I have the students status in regard to all 111 schools. Looking across the record I should see missing values, 0's, 1's, and only one 2. What is the most convenient way of converting what I currently have to what I would want to have. Suggestions appreciated. thanks,   martin sherman

Martin F. Sherman, Ph.D.
Professor of Psychology
Director of Masters Education, Thesis Track
4501 North Charles Street
Psychology Department
222 B Beatty Hall
Baltimore, MD 21210

Phone: [phone]
Fax: [phone]
email: [hidden email]

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

[Oliver, Richard]

Look at the CASESTOVARS command (or the Restructure Data Wizard on the Data menu).

---

From: SPSSX(r) Discussion on behalf of Martin Sherman
Sent: Mon 2/16/2009 12:34 PM
To: [hidden email]
Subject: change varying records into one record per individual

Dear list: I have the following situation.
I have students applying to anywhere from 1 to 111 schools. If they apply to one school they only have one record, but if they apply to more schools then they will have a record for each school they apply to. In addition, for each school they apply to I have whether the school accepted them (0 vs 1-yes) and also whether they attended that school (0 vs. 1-yes). I can add up these two variables and come up with missing, 0, 1, or 2.  Missing being that they did not apply to that school. 0 is they applied but was not accepted, 1 if the were accepted but did not enroll, and 2 if they were accepted and enrolled. I now want to have a single record for each student so that I have the students status in regard to all 111 schools. Looking across the record I should see missing values, 0's, 1's, and only one 2. What is the most convenient way of converting what I currently have to what I would want to have. Suggestions appreciated. thanks,   martin sherman

Martin F. Sherman, Ph.D.
Professor of Psychology
Director of Masters Education, Thesis Track
4501 North Charles Street
Psychology Department
222 B Beatty Hall
Baltimore, MD 21210

Phone: [phone]
Fax: [phone]
email: [hidden email]

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

[Clive Downs]

Hi Martin,

In case, by any chance, you haven't worked out how to use the Restructure
command (which can be a bit obscure) I hope the following example will show
you.

If doing via the menus, choose Data -> Restructure - > then, in the wizard
(assuming the data set up, and names as in the syntax below:

Step 1: choose 'cases into variables'
Step 2: move id -> identifier variable(s)
        move school - > index variable(s)
Step 3 : yes to Sort
Step 4: group by original variable
then Paste syntax , then run the syntax.

If doing it via syntax (equivalent to the menu method):

* ------------------------------------

*  set up data  - 2  students and 3 schools.
* student 001 appliied to school 1 but was not accepted.
* student 001 didn't apply to school 2.
* student 001 applied to school 3 and was accepted., etc.
*--------------------------------------------------------------------.

DATA LIST FREE/  id(A3)  school apply accept.
BEGIN DATA

001 1 1 0
001 2 0 0
001 3 1 1
002 1 0 0
002 3 1 1

END DATA.
DATASET NAME school.

* restructure data.
*------------------------.

SORT CASES BY id school .
CASESTOVARS
 /ID = id
 /INDEX = school
 /GROUPBY = VARIABLE .

LIST.

* this should produce the following output:

id  apply.1 apply.2 apply.3 accept.1 accept.2 accept.3

001       1       0       1        0        0        1
002       0       .       1        0        .        1

You may want to change the coding conventions used (eg for missing data),
but I hope the basic method will work for you.

Regards,

Clive.

-----------------------------------------


>Dear list: I have the following situation.
>I have students applying to anywhere from 1 to 111 schools. If they apply
to one school they only have one record, but if they apply to more schools
then they will have a record for each school they apply to. In addition,
for each school they apply to I have whether the school accepted them (0 vs
1-yes) and also whether they attended that school (0 vs. 1-yes). I can add
up these two variables and come up with missing, 0, 1, or 2.  Missing being
that they did not apply to that school. 0 is they applied but was not
accepted, 1 if the were accepted but did not enroll, and 2 if they were
accepted and enrolled. I now want to have a single record for each student
so that I have the students status in regard to all 111 schools. Looking
across the record I should see missing values, 0's, 1's, and only one 2.
What is the most convenient way of converting what I currently have to what
I would want to have. Suggestions appreciated. thanks,   martin sherman
>
>Martin F. Sherman, Ph.D.
>Professor of Psychology
>Director of Masters Education, Thesis Track
>4501 North Charles Street
>Psychology Department
>222 B Beatty Hall
>Baltimore, MD 21210
>

=====================
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 01:34 PM 2/16/2009, Martin Sherman wrote:

>I have students applying to anywhere from 1 to 111 schools. They
>will have a record for each school they apply to. I have whether the
>school accepted them (0 vs 1-yes) and also whether they attended
>that school (0 vs. 1-yes). I can add up these two variables and come
>up with missing, 0, 1, or 2.  Missing being that they did not apply
>to that school. 0 is they applied but was not accepted, 1 if the
>were accepted but did not enroll, and 2 if they were accepted and
>enrolled. I now want to have a single record for each student so
>that I have the students status in regard to all 111 schools.
>Looking across the record I should see missing values, 0's, 1's, and
>only one 2.

You have seen Richard Oliver's and Clive Downs's (correct) advice how
to do this.

My advice is, don't do it. Any record with 111 instance of something
is awkward. And (once more), SPSS is very good at handling multiple
records, far clumsier at handling multiple instances within a record.

The following (though untested) should be a lot easier. Remember that
AGGREGATE *replaces* the active file; make sure the original is
stored safely and accessibly.

If each student has a value of a variable called "StdtID", and the
two 0-1 variables are called "Accepted" and "Attended",

AGGREGATE  OUTFILE=*
   /BREAK = StdtID
   /NAppl  'Number of schools applied to'  = NU
   /NAcc   'Number of schools accepted at' = SUM(Accepted)
   /NAttnd 'Number of schools attended'    = SUM(Attended).

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

[Clive Downs]

Hi Richard,

Thank you for pointing this out - AGGREGATE hadn't occurred to me. It is a
much neater way to do it, and I agree it is very awkward dealing with 100
plus variables.

btw, of course, when using AGGREGATE, you can also produce a new file with
the aggregated information,  thus avoiding overwriting the original

Regards,

Clive.

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