SPSSX Discussion

Help write a syntax - specific question

Classic

List

Threaded

14 messages Options

smalik

Help write a syntax - specific question

This post was updated on .

Dear SPSS Experts
I need help to write a syntax. I have about 30 thousand records (duplicates
included). Mostly a student is listed on 4 rows or 3 rows. I want to create
a flag by saying if student (ID=100000000) and his Code is EXCL in all 4
semesters then category=Exclusive. If another student (ID=100000001)
his Code is NONE in all 4 semesters then category=NONE. All others/remaining
or mixed types=SOME.

StudentID Semester Code Type
100000000 Fall 2019 EXCL Exclusive
100000000 Spring 2020 EXCL Exclusive
100000000 Summer 2019 EXCL Exclusive
100000000 Winter 2020 EXCL Exclusive

100000001 Fall 2019 NONE None
100000001 Spring 2020 NONE None
100000001 Summer 2019 NONE None
100000001 Winter 2020 NONE None

100000002 Fall 2019 NONE Some
100000002 Spring 2020 NONE Some
100000002 Summer 2019 EXCL Some
100000002 Winter 2020 EXCL Some

Thank you so much for your help.

--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
To manage your subscription to SPSSX-L, send a message to
LISTSERV@LISTSERV.UGA.EDU (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: Help write a syntax - specific question

Administrator

Thanks for providing some sample data. That makes it a lot easier to offer
help. Try this. But remove the final LIST command when running it on your
large file

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / StudentID (F10.0) Semester(A6) Year (F4.0) Code (A4).
BEGIN DATA
100000000 Fall 2019 EXCL
100000000 Spring 2020 EXCL
100000000 Summer 2019 EXCL
100000000 Winter 2020 EXCL
100000001 Fall 2019 NONE
100000001 Spring 2020 NONE
100000001 Summer 2019 NONE
100000001 Winter 2020 NONE
100000002 Fall 2019 NONE
100000002 Spring 2020 NONE
100000002 Summer 2019 EXCL
100000002 Winter 2020 EXCL
END DATA.

* Automatically recode Code to a numeric code,
* because a numeric variable will be easier to work with.

AUTORECODE VARIABLES=Code
/INTO CodeNo
/PRINT.

* Write MIN and MAX code values for each ID to all rows for that ID.
* Get the number of records per ID while we're at it.
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES
/BREAK=StudentID
/MinCode=MIN(CodeNo)
/MaxCode=Max(CodeNo)
/NumRecs=NU.

* If there is no compelling reason for making Type a string variable,
* I suggest you make it numeric and add value labels.

DO IF MinCode EQ MaxCode.
COMPUTE Type = CodeNo.
ELSE.
COMPUTE Type = 3.
END IF.
FORMATS Type (F1).
VALUE LABELS Type 1 "Exclusive" 2 "None" 3 "Some".
FREQUENCIES Type.

smalik wrote

> Dear SPSS Experts
> I need help to write a syntax. I have about 30 thousand records
> (duplicates
> included). Mostly a student is listed on 4 rows or 3 rows. I want to
> create
> a flag by saying if student (ID=100000000) and his Code is EXCL in all 4
> semesters then category=Exclusive. If another student (ID=100000001)
> his Code is NONE in all 4 semesters then category=NONE. All
> others/remaining
> or mixed types=SOME.
>
> --- snip ---

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

--
Sent from: http://spssx-discussion.1045642.n5.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
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/).

David Marso-2

Re: Help write a syntax - specific question

In reply to this post by smalik

[

Slight modification

DO IF MinCode EQ MaxCode.
COMPUTE Type = CodeNo.
ELSE.
COMPUTE Type = 3.
END IF.

Same as above but adds OP 4 semester clause.

COMPUTE Type=(MinCode EQ MaxCode)*Type*(NumRecs EQ 4).
VALUE LABELS Type 1 "Exclusive" 2 "None" 0 "Some".

Thanks for providing some sample data. That makes it a lot easier to offer
help. Try this. But remove the final LIST command when running it on your
large file

NEW FILE.
DATASET CLOSE ALL.
DATA LIST LIST / StudentID (F10.0) Semester(A6) Year (F4.0) Code (A4).
BEGIN DATA
100000000 Fall 2019 EXCL
100000000 Spring 2020 EXCL
100000000 Summer 2019 EXCL
100000000 Winter 2020 EXCL
100000001 Fall 2019 NONE
100000001 Spring 2020 NONE
100000001 Summer 2019 NONE
100000001 Winter 2020 NONE
100000002 Fall 2019 NONE
100000002 Spring 2020 NONE
100000002 Summer 2019 EXCL
100000002 Winter 2020 EXCL
END DATA.

* Automatically recode Code to a numeric code,
* because a numeric variable will be easier to work with.

AUTORECODE VARIABLES=Code
/INTO CodeNo
/PRINT.

* Write MIN and MAX code values for each ID to all rows for that ID.
* Get the number of records per ID while we're at it.
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES
/BREAK=StudentID
/MinCode=MIN(CodeNo)
/MaxCode=Max(CodeNo)
/NumRecs=NU.

* If there is no compelling reason for making Type a string variable,
* I suggest you make it numeric and add value labels.

DO IF MinCode EQ MaxCode.
COMPUTE Type = CodeNo.
ELSE.
COMPUTE Type = 3.
END IF.
FORMATS Type (F1).
VALUE LABELS Type 1 "Exclusive" 2 "None" 3 "Some".
FREQUENCIES Type.

smalik wrote

Bruce Weaver

Re: Help write a syntax - specific question

Administrator

Good catch, David, regarding my treatment of IDs with only 3 rows. Slight
typo in your COMPUTE command though: You need to change Type to CodeNo on
the right side of the equals sign:

COMPUTE Type=(MinCode EQ MaxCode)*CodeNo*(NumRecs EQ 4).
VALUE LABELS Type 1 "Exclusive" 2 "None" 0 "Some".

;-)

David Marso-2 wrote

> [
>
>
> Slight modification
>
> DO IF MinCode EQ MaxCode.
> COMPUTE Type = CodeNo.
> ELSE.
> COMPUTE Type = 3.
> END IF.
>
> Same as above but adds OP 4 semester clause.
>
> COMPUTE Type=(MinCode EQ MaxCode)*Type*(NumRecs EQ 4).
> VALUE LABELS Type 1 "Exclusive" 2 "None" 0 "Some".

smalik

Re: Help write a syntax - specific question

In reply to this post by David Marso-2

Thanks Bruce and David for your help. I will try now. Thanks so much.
Regards

--
Sent from: http://spssx-discussion.1045642.n5.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

smalik

Re: Help write a syntax - specific question

In reply to this post by smalik

NEW FILE.
Dear Bruce and David
Thanks so much for your help with my syntax. I summarize below the syntax
from your comments. It runs without any error. However, I was expecting
higher numbers of records for "None" category but I am getting higher number
for "SOME". It looks like these two points are reversed. Please see below
how I ran the syntax? Thank you so much for your time and help. I appreciate
it very much. Regards

*DATASET CLOSE ALL.
*DATA LIST LIST / StudentID (F10.0) Semester(A6) Year (F4.0) Code (A4).
*BEGIN DATA
100000000 Fall 2019 EXCL
100000000 Spring 2020 EXCL
100000000 Summer 2019 EXCL
100000000 Winter 2020 EXCL
100000001 Fall 2019 NONE
100000001 Spring 2020 NONE
100000001 Summer 2019 NONE
100000001 Winter 2020 NONE
100000002 Fall 2019 NONE
100000002 Spring 2020 NONE
100000002 Summer 2019 EXCL
100000002 Winter 2020 EXCL
END DATA.

AUTORECODE VARIABLES=Code
/INTO CodeNo
/PRINT.

* Write MIN and MAX code values for each ID to all rows for that ID.
* Get the number of records per ID while we're at it.
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES
/BREAK=StudentID
/MinCode=MIN(CodeNo)
/MaxCode=Max(CodeNo)
/NumRecs=NU.

* If there is no compelling reason for making Type a string variable,
* I suggest you make it numeric and add value labels.

DO IF MinCode EQ MaxCode.
COMPUTE Type = CodeNo.
ELSE.
COMPUTE Type = 3.
END IF.

COMPUTE Type=(MinCode EQ MaxCode)*CodeNo*(NumRecs EQ 4).
VALUE LABELS Type 1 "Exclusive" 2 "None" 0 "Some".

--
Sent from: http://spssx-discussion.1045642.n5.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: Help write a syntax - specific question

Administrator

I removed the DO-IF structure from the code you pasted, because it is no
longer needed--the one-line COMPUTE command David suggested replaces it. I
then ran the code using the sample data you supplied. Here is a screen
capture of the resulting data file with value labels displayed:

smalik.png
<http://spssx-discussion.1045642.n5.nabble.com/file/t7186/smalik.png>

The value labels for Type match the output you showed in your July 27 post
(http://spssx-discussion.1045642.n5.nabble.com/file/n5739388/spss_image.png).
So I don't understand what the problem is. Can you clarify?

Thanks.

smalik wrote

> NEW FILE.
> Dear Bruce and David
> Thanks so much for your help with my syntax. I summarize below the syntax
> from your comments. It runs without any error. However, I was expecting
> higher numbers of records for "None" category but I am getting higher
> number
> for "SOME". It looks like these two points are reversed. Please see below
> how I ran the syntax? Thank you so much for your time and help. I
> appreciate
> it very much. Regards

smalik

Re: Help write a syntax - specific question

Good morning Bruce
Thank you again for your help. May be I did not ask question accurately. I
ran my file using the following syntax.

AUTORECODE VARIABLES=Code
/INTO CodeNo
/PRINT.

* Write MIN and MAX code values for each ID to all rows for that ID.
* Get the number of records per ID while we're at it.
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES
/BREAK=StudentID
/MinCode=MIN(CodeNo)
/MaxCode=Max(CodeNo)
/NumRecs=NU.

COMPUTE Type=(MinCode EQ MaxCode)*CodeNo*(NumRecs EQ 4).
VALUE LABELS Type 1 "Exclusive" 2 "None" 0 "Some".

**** I got the following output. see image. Please read my comments in the
column.

<http://spssx-discussion.1045642.n5.nabble.com/file/t341788/spss_image1.png>

--
Sent from: http://spssx-discussion.1045642.n5.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: Help write a syntax - specific question

Administrator

Ah, okay. To get the result you want, remove *(NumRecs EQ 4) from the
COMPUTE command. Change this:

COMPUTE Type=(MinCode EQ MaxCode)*CodeNo*(NumRecs EQ 4).

to this:

COMPUTE Type=(MinCode EQ MaxCode)*CodeNo.

Give that a try and let us know if it gives the results you want.

smalik wrote

> Good morning Bruce
> Thank you again for your help. May be I did not ask question accurately. I
> ran my file using the following syntax.
>
> AUTORECODE VARIABLES=Code
> /INTO CodeNo
> /PRINT.
>
>
> * Write MIN and MAX code values for each ID to all rows for that ID.
> * Get the number of records per ID while we're at it.
> AGGREGATE
> /OUTFILE=* MODE=ADDVARIABLES
> /BREAK=StudentID
> /MinCode=MIN(CodeNo)
> /MaxCode=Max(CodeNo)
> /NumRecs=NU.
>
> COMPUTE Type=(MinCode EQ MaxCode)*CodeNo*(NumRecs EQ 4).
> VALUE LABELS Type 1 "Exclusive" 2 "None" 0 "Some".
>
>
> **** I got the following output. see image. Please read my comments in the
> column.
>
> <http://spssx-discussion.1045642.n5.nabble.com/file/t341788/spss_image1.png>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

> (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
[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.

--
Sent from: http://spssx-discussion.1045642.n5.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

smalik

Re: Help write a syntax - specific question

Dear Bruce
I am almost there. Thank you for your patience. I ran the frequencies for
"TYPE" and I get the following out put. What to do with "3.00"?

<http://spssx-discussion.1045642.n5.nabble.com/file/t341788/spss_image2.png>

--
Sent from: http://spssx-discussion.1045642.n5.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: Help write a syntax - specific question

Administrator

It would help if you provided a listing of the input data you used for that
run.

Alternatively, you could try this (possibly) simpler approach that just
works with the original string variables, and generates Type as another
string variable.

STRING MinCode Maxcode Type (A4).
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES
/BREAK=StudentID
/MinCode=MIN(Code)
/MaxCode=Max(Code)
/NumRecs=NU.

IF MinCode EQ MaxCode Type = Code.
IF MinCode NE MaxCode Type = "Some".
FREQUENCIES Type.
DELETE VARIABLES MinCode MaxCode.

smalik wrote

> Dear Bruce
> I am almost there. Thank you for your patience. I ran the frequencies for
> "TYPE" and I get the following out put. What to do with "3.00"?
>
> <http://spssx-discussion.1045642.n5.nabble.com/file/t341788/spss_image2.png>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

smalik

Re: Help write a syntax - specific question

Dear Bruce
Thanks so much for your help. I think I have the results what I want?
I ran the simple syntax; You can see my data and the output. Please take a
look at the Type='Some' and 'SOME'. I manually checked, they are categorized
the same which is OK. But can we not get this category at two places and it
should be one either "Some" or "SOME". You are extremely helpful and I am
very very thankful for your help. Regards

STRING MinCode Maxcode Type (A4).
AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES
/BREAK=StudentID
/MinCode=MIN(Code)
/MaxCode=Max(Code)
/NumRecs=NU.

IF MinCode EQ MaxCode Type = Code.
IF MinCode NE MaxCode Type = "Some".
FREQUENCIES Type.

<http://spssx-discussion.1045642.n5.nabble.com/file/t341788/spss_image4.png>

<http://spssx-discussion.1045642.n5.nabble.com/file/t341788/spss_image3.png>

--
Sent from: http://spssx-discussion.1045642.n5.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: Help write a syntax - specific question

Administrator

Change this...

IF MinCode NE MaxCode Type = "Some".

to this:

IF MinCode NE MaxCode Type = "SOME".

smalik wrote

> Dear Bruce
> Thanks so much for your help. I think I have the results what I want?
> I ran the simple syntax; You can see my data and the output. Please take a
> look at the Type='Some' and 'SOME'. I manually checked, they are
> categorized
> the same which is OK. But can we not get this category at two places and
> it
> should be one either "Some" or "SOME". You are extremely helpful and I am
> very very thankful for your help. Regards
>
> STRING MinCode Maxcode Type (A4).
> AGGREGATE
> /OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES
> /BREAK=StudentID
> /MinCode=MIN(Code)
> /MaxCode=Max(Code)
> /NumRecs=NU.
>
> IF MinCode EQ MaxCode Type = Code.
> IF MinCode NE MaxCode Type = "Some".
> FREQUENCIES Type.
>
> <http://spssx-discussion.1045642.n5.nabble.com/file/t341788/spss_image4.png>
>
> <http://spssx-discussion.1045642.n5.nabble.com/file/t341788/spss_image3.png>
>
>
>
> --
> Sent from: http://spssx-discussion.1045642.n5.nabble.com/
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

smalik

Re: Help write a syntax - specific question

Dear Bruce

Thank you so much for your help. It worked.

AGGREGATE
/OUTFILE=* MODE=ADDVARIABLES OVERWRITE=YES
/BREAK=StudentID
/MinCode=MIN(Code)
/MaxCode=Max(Code)
/NumRecs=NU.

IF MinCode EQ MaxCode Type = Code.
IF MinCode NE MaxCode Type = "SOME".
FREQUENCIES Type.
DELETE VARIABLES MinCode MaxCode.

--
Sent from: http://spssx-discussion.1045642.n5.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