complex aggregate command
Locked
 
|
Dear List: I have been trying to use the Aggregate command with a complex data set but I am unable to figure out how to transform my data. Here is the how the data is
set up. You will notice that not all schools have the same number of rows of data. The new data set should have one row that contains the following. A variable for the total number of students, a variable for number of males, a variable for number of females,
a variable for number of Blacks, a variable for number of whites, a variable for number of Hispanics, a variable for two or more races,, a variable for number of students with disabilities, a variable for male attendance, a variable for female attendance,
a variable for male weapons, a variable for female weapons, etc. Can this even be done? Help appreciated. Thanks, School_no Description_of_Data All_student Attendance Weapons Sex_offenses Attack_threats_Fighting 602 All students 1 0 0 0
0 602 Male 1 0 0 0
0 602 White 1 0 0 0
0 606 All students 186 66 11 1 33 606 Male 112 30 10 1 18 606 Female 74 36 1 0 15 606 Black 29 12 1 0
5 606 White 153 51 10 1 28 606 Hispanic 3 2 0 0
0 606 Two or more races 1 1 0 0 0 606 Students with Disabilities 43 7 3 0 9 2801 All students 1 0 1 0 3 2801 Male 1 0 1 0
2 2801 Female 1 0 0 0
1 Martin F. Sherman, Ph.D. Professor of Psychology Director of Master’s Education: Thesis Track Department of Psychology 222 B Beatty Hall 4501 North Charles Street Baltimore, MD 21210 410-617-2417 tel 410-617-5341 fax |
Administrator
|
I don't have SPSS on this machine, so cannot do any testing right now. Does the CIN function work with string variables? From the FM:
CIN(varlist,value1,value2). Count of cases between value1 and value2, inclusive. If it does work with string variables, use it with your Description_of_Data variable to get the various counts you want, plugging in the same value for both value1 and value2. If it does NOT work with string variables, AUTORECODE your Description_of_Data into a numeric variable, then use CIN on that new numeric variable.
--
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/). |
|
In reply to this post by msherman
Maybe this will work? No spaces are allowed in the Description_of_data value labels as they will be part of the new generated variables (shorter variable names would give less bulky handling). Instead of renaming the field Description_of_data it could be recoded with AUTORECODE or the more controlled RECODE command. On the downside you would get variable names which are difficult to interpret (like All_student.1, All_student.2, ...).
HTH, PRogman NEW FILE. DATA LIST FIXED / School_no Description_of_Data All_student Attendance Weapons Sex_offenses Attack_threats_Fighting (F10.0 A30 F8.0 F8.0 F8.0 F8.0 F8.0). BEGIN DATA. 602 All students 1 0 0 0 0 602 Male 1 0 0 0 0 602 White 1 0 0 0 0 606 All students 186 66 11 1 33 606 Male 112 30 10 1 18 606 Female 74 36 1 0 15 606 Black 29 12 1 0 5 606 White 153 51 10 1 28 606 Hispanic 3 2 0 0 0 606 Two or more races 1 1 0 0 0 606 Students with Disabilities 43 7 3 0 9 2801 All students 1 0 1 0 3 2801 Male 1 0 1 0 2 2801 Female 1 0 0 0 1 END DATA. DATASET NAME SchoolData. *Make one-word value labels to conform with variable name rules*. IF (Description_of_Data='All students') Description_of_Data='All'. IF (Description_of_Data='Two or more races') Description_of_Data='TwoRacesOrMore'. IF (Description_of_Data='Students with Disabilities') Description_of_Data='Disabled'. SORT CASES BY School_no Description_of_Data. CASESTOVARS /ID=School_no /INDEX=Description_of_Data /GROUPBY=VARIABLE. LIST . ------- The variables are listed in the following order: LINE 1: School_no All_student.All All_student.Black All_student.Disabled All_student.Female All_student.Hispanic All_student.Male All_student.TwoRacesOrMore All_student.White Attendance.All Attendance.Black Attendance.Disabled Attendance.Female LINE 2: Attendance.Hispanic Attendance.Male Attendance.TwoRacesOrMore Attendance.White Weapons.All Weapons.Black Weapons.Disabled Weapons.Female Weapons.Hispanic Weapons.Male Weapons.TwoRacesOrMore Weapons.White Sex_offenses.All LINE 3: Sex_offenses.Black Sex_offenses.Disabled Sex_offenses.Female Sex_offenses.Hispanic Sex_offenses.Male Sex_offenses.TwoRacesOrMore Sex_offenses.White Attack_threats_Fighting.All Attack_threats_Fighting.Black Attack_threats_Fighting.Disabled Attack_threats_Fighting.Female Attack_threats_Fighting.Hispanic Attack_threats_Fighting.Male LINE 4: Attack_threats_Fighting.TwoRacesOrMore Attack_threats_Fighting.White School_no: 602 1 . . . . 1 . 1 0 . . . Attendance.H: . 0 . 0 0 . . . . 0 . 0 0 Sex_offenses: . . . . 0 . 0 0 . . . . 0 Attack_threa: . 0 School_no: 606 186 29 43 74 3 112 1 153 66 12 7 36 Attendance.H: 2 30 1 51 11 1 3 1 0 10 0 10 1 Sex_offenses: 0 0 0 0 1 0 1 33 5 9 15 0 18 Attack_threa: 0 28 School_no: 2801 1 . . 1 . 1 . . 0 . . 0 Attendance.H: . 0 . . 1 . . 0 . 1 . . 0 Sex_offenses: . . 0 . 0 . . 3 . . 1 . 2 Attack_threa: . . Number of cases read: 3 Number of cases listed: 3
|
Administrator
|
Much easier to use RTRIM and REPLACE functions ;-)
COMPUTE Description_of_Data=RTRIM(REPLACE(Description_of_Data,' ','_'),'_'). CASESTOVARS ID=School_no /INDEX=Description_of_Data.
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?" |
|
|
I'd say even better, as there is no need to specify each possible value in Description_of_Data, and possible trailing spaces are removed.
So, to save typing a few characters: COMPUTE Description_of_Data=REPLACE(RTRIM(Description_of_Data),' ','_'). ;-) /PRogman
|
|
|
As long as Statistics is in the default
Unicode mode, you don't need to type those RTRIM characters either.
Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] phone: 720-342-5621 From: PRogman <[hidden email]> To: [hidden email] Date: 07/15/2015 01:35 AM Subject: Re: [SPSSX-L] complex aggregate command Sent by: "SPSSX(r) Discussion" <[hidden email]> I'd say even better, as there is no need to specify each possible value in Description_of_Data, and possible trailing spaces are removed. So, to save typing a few characters: COMPUTE Description_of_Data=REPLACE(RTRIM(Description_of_Data),' ','_'). ;-) /PRogman David Marso wrote > Much easier to use RTRIM and REPLACE functions ;-) > > COMPUTE Description_of_Data=RTRIM(REPLACE(Description_of_Data,' > ','_'),'_'). > CASESTOVARS ID=School_no /INDEX=Description_of_Data. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/complex-aggregate-command-tp5730126p5730130.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 ===================== 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 |
|
Administrator
|
In reply to this post by Bruce Weaver
After a good night's sleep, and having seen some other posts in this thread, I retract everything I suggested below! (I don't know what I was thinking--I guess I was seduced by Martin's use of the word AGGREGATE in his original post.) CASESTOVARS is the way to go here, as in David's post:
COMPUTE Description_of_Data=RTRIM(REPLACE(Description_of_Data,' ','_'),'_'). CASESTOVARS ID=School_no /INDEX=Description_of_Data.
--
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/). |
|
|
Wow!! That seems to work. Let me check it out in detail. THANKS, that will save a huge amount of time. BEST, martin
-----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver Sent: Wednesday, July 15, 2015 8:47 AM To: [hidden email] Subject: Re: complex aggregate command After a good night's sleep, and having seen some other posts in this thread, I retract everything I suggested below! (I don't know what I was thinking--I guess I was seduced by Martin's use of the word AGGREGATE in his original post.) CASESTOVARS is the way to go here, as in David's post: COMPUTE Description_of_Data=RTRIM(REPLACE(Description_of_Data,' ','_'),'_'). CASESTOVARS ID=School_no /INDEX=Description_of_Data. Bruce Weaver wrote > I don't have SPSS on this machine, so cannot do any testing right now. > Does the CIN function work with string variables? From the FM: > > CIN(varlist,value1,value2). Count of cases between value1 and value2, > inclusive. > > If it does work with string variables, use it with your > Description_of_Data variable to get the various counts you want, > plugging in the same value for both value1 and value2. > > If it does NOT work with string variables, AUTORECODE your > Description_of_Data into a numeric variable, then use CIN on that new > numeric variable. > > > msherman wrote >> Dear List: I have been trying to use the Aggregate command with a >> complex data set but I am unable to figure out how to transform my >> data. Here is the how the data is set up. You will notice that not >> all schools have the same number of rows of data. The new data set >> should have one row that contains the following. A variable for the >> total number of students, a variable for number of males, a variable >> for number of females, a variable for number of Blacks, a variable >> for number of whites, a variable for number of Hispanics, a variable >> for two or more races,, a variable for number of students with >> disabilities, a variable for male attendance, a variable for female >> attendance, a variable for male weapons, a variable for female >> weapons, etc. Can this even be done? Help appreciated. Thanks, >> >> School_no Description_of_Data All_student >> Attendance Weapons Sex_offenses >> Attack_threats_Fighting >> 602 All students 1 >> 0 0 0 0 >> 602 Male 1 >> 0 0 0 0 >> 602 White 1 >> 0 0 0 0 >> 606 All students 186 >> 66 11 1 33 >> 606 Male 112 >> 30 10 1 18 >> 606 Female 74 >> 36 1 0 15 >> 606 Black 29 >> 12 1 0 5 >> 606 White 153 >> 51 10 1 28 >> 606 Hispanic 3 >> 2 0 0 0 >> 606 Two or more races 1 >> 1 0 0 0 >> 606 Students with Disabilities 43 >> 7 3 0 9 >> 2801 All students 1 >> 0 1 0 3 >> 2801 Male 1 >> 0 1 0 2 >> 2801 Female 1 >> 0 0 0 1 >> >> >> >> Martin F. Sherman, Ph.D. >> Professor of Psychology >> Director of Master's Education: Thesis Track >> >> Department of Psychology >> 222 B Beatty Hall >> 4501 North Charles Street >> Baltimore, MD 21210 >> msherman@ >> <mailto: >> msherman@ >> > >> 410-617-2417 tel >> 410-617-5341 fax >> >> >> ===================== >> 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. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/complex-aggregate-command-tp5730126p5730136.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 ===================== 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 |
|
|
Martin,
Would you clarify this sentence. "The new data set should have one row that contains the following." Perhaps I misunderstood this sentence but it seems you are asking for a one record dataset. The solutions posted will have as many rows as there are schools, which makes more sense but ... Gene Maguin -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Martin Sherman Sent: Wednesday, July 15, 2015 8:58 AM To: [hidden email] Subject: Re: complex aggregate command Wow!! That seems to work. Let me check it out in detail. THANKS, that will save a huge amount of time. BEST, martin -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Bruce Weaver Sent: Wednesday, July 15, 2015 8:47 AM To: [hidden email] Subject: Re: complex aggregate command After a good night's sleep, and having seen some other posts in this thread, I retract everything I suggested below! (I don't know what I was thinking--I guess I was seduced by Martin's use of the word AGGREGATE in his original post.) CASESTOVARS is the way to go here, as in David's post: COMPUTE Description_of_Data=RTRIM(REPLACE(Description_of_Data,' ','_'),'_'). CASESTOVARS ID=School_no /INDEX=Description_of_Data. Bruce Weaver wrote > I don't have SPSS on this machine, so cannot do any testing right now. > Does the CIN function work with string variables? From the FM: > > CIN(varlist,value1,value2). Count of cases between value1 and value2, > inclusive. > > If it does work with string variables, use it with your > Description_of_Data variable to get the various counts you want, > plugging in the same value for both value1 and value2. > > If it does NOT work with string variables, AUTORECODE your > Description_of_Data into a numeric variable, then use CIN on that new > numeric variable. > > > msherman wrote >> Dear List: I have been trying to use the Aggregate command with a >> complex data set but I am unable to figure out how to transform my >> data. Here is the how the data is set up. You will notice that not >> all schools have the same number of rows of data. The new data set >> should have one row that contains the following. A variable for the >> total number of students, a variable for number of males, a variable >> for number of females, a variable for number of Blacks, a variable >> for number of whites, a variable for number of Hispanics, a variable >> for two or more races,, a variable for number of students with >> disabilities, a variable for male attendance, a variable for female >> attendance, a variable for male weapons, a variable for female >> weapons, etc. Can this even be done? Help appreciated. Thanks, >> >> School_no Description_of_Data All_student >> Attendance Weapons Sex_offenses >> Attack_threats_Fighting >> 602 All students 1 >> 0 0 0 0 >> 602 Male 1 >> 0 0 0 0 >> 602 White 1 >> 0 0 0 0 >> 606 All students 186 >> 66 11 1 33 >> 606 Male 112 >> 30 10 1 18 >> 606 Female 74 >> 36 1 0 15 >> 606 Black 29 >> 12 1 0 5 >> 606 White 153 >> 51 10 1 28 >> 606 Hispanic 3 >> 2 0 0 0 >> 606 Two or more races 1 >> 1 0 0 0 >> 606 Students with Disabilities 43 >> 7 3 0 9 >> 2801 All students 1 >> 0 1 0 3 >> 2801 Male 1 >> 0 1 0 2 >> 2801 Female 1 >> 0 0 0 1 >> >> >> >> Martin F. Sherman, Ph.D. >> Professor of Psychology >> Director of Master's Education: Thesis Track >> >> Department of Psychology >> 222 B Beatty Hall >> 4501 North Charles Street >> Baltimore, MD 21210 >> msherman@ >> <mailto: >> msherman@ >> > >> 410-617-2417 tel >> 410-617-5341 fax >> >> >> ===================== >> 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. -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/complex-aggregate-command-tp5730126p5730136.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 ===================== 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 ===================== 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 |
|
Administrator
|
Well spotted, Gene. I think all respondents took it to mean one row per school. But Martin should confirm.
--
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/). |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |