Aggregate command problem with sum function
Locked
12
12
 
|
Hi Everyone!
I need some help with the aggregate command. Below is the command I'm using: DATASET DECLARE test. SORT CASES BY UserID. AGGREGATE /OUTFILE='test' /PRESORTED /BREAK=UserID /t1f_Enbrel_sum=sum(t1f_Enbrel) /t1f_Humira_sum=sum(t1f_Humira) /t1f_Remicade_sum=sum(t1f_Remicade) /t1f_MabThera_sum=sum(t1f_MabThera) /t1f_Orencia_sum=sum(t1f_Orencia) /t1f_RoActemra_sum=sum(t1f_RoActemra) /t1f_Cimzia_sum=sum(t1f_Cimzia) /t1f_Simponi_sum=sum(t1f_Simponi) /N_BREAK=N. The issue is that for those variables (t1f_) with just one case the sum seems to fail. The aggregate is 0 when it should be 1. I have seen some suggestions online recommending sum.1, but that did not work. Can anyone help me out. I would really appreciated it. Thanks in advance!!!
mils
|
Re: Aggregate command problem with sum function
Administrator
|
Simple data illustrating the problem?
----
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?" |
Re: Aggregate command problem with sum function
|
|
In reply to this post by mils
First, by default Aggregate will sum all
the non-missing case values; so sum.1 is essentially redundant since the
.n argument specifies the minimum number of non-missing cases, which by
default is 1.
Second, the sum of one case will only be 1 if the value of the variable for that case is 1. If the value is 0 rather than missing, then the sum will be 0. This example seems to generate the correct results dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mils <[hidden email]> To: [hidden email], Date: 11/30/2012 08:25 AM Subject: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Everyone! I need some help with the aggregate command. Below is the command I'm using: DATASET DECLARE test. SORT CASES BY UserID. AGGREGATE /OUTFILE='test' /PRESORTED /BREAK=UserID /t1f_Enbrel_sum=sum(t1f_Enbrel) /t1f_Humira_sum=sum(t1f_Humira) /t1f_Remicade_sum=sum(t1f_Remicade) /t1f_MabThera_sum=sum(t1f_MabThera) /t1f_Orencia_sum=sum(t1f_Orencia) /t1f_RoActemra_sum=sum(t1f_RoActemra) /t1f_Cimzia_sum=sum(t1f_Cimzia) /t1f_Simponi_sum=sum(t1f_Simponi) /N_BREAK=N. The issue is that for those variables (t1f_) with just one case the sum seems to fail. The aggregate is 0 when it should be 1. I have seen some suggestions online recommending sum.1, but that did not work. Can anyone help me out. I would really appreciated it. Thanks in advance!!! ----- mils -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538.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 |
|
|
Hi Everyone,
Thanks for
your quick answers. You aren't going to believe that, but (at least for me) sum
function won't work properly if you select the option “sort file before aggregating”.
So what I did, was to sort the data first (by the variable use in the aggregate
command) and then run the aggregate command. Now it works!
mils Date: Fri, 30 Nov 2012 07:15:24 -0800 From: [hidden email] To: [hidden email] Subject: Re: Aggregate command problem with sum function First, by default Aggregate will sum all the non-missing case values; so sum.1 is essentially redundant since the .n argument specifies the minimum number of non-missing cases, which by default is 1. Second, the sum of one case will only be 1 if the value of the variable for that case is 1. If the value is 0 rather than missing, then the sum will be 0. This example seems to generate the correct results dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mils <[hidden email]> To: [hidden email], Date: 11/30/2012 08:25 AM Subject: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Everyone! I need some help with the aggregate command. Below is the command I'm using: DATASET DECLARE test. SORT CASES BY UserID. AGGREGATE /OUTFILE='test' /PRESORTED /BREAK=UserID /t1f_Enbrel_sum=sum(t1f_Enbrel) /t1f_Humira_sum=sum(t1f_Humira) /t1f_Remicade_sum=sum(t1f_Remicade) /t1f_MabThera_sum=sum(t1f_MabThera) /t1f_Orencia_sum=sum(t1f_Orencia) /t1f_RoActemra_sum=sum(t1f_RoActemra) /t1f_Cimzia_sum=sum(t1f_Cimzia) /t1f_Simponi_sum=sum(t1f_Simponi) /N_BREAK=N. The issue is that for those variables (t1f_) with just one case the sum seems to fail. The aggregate is 0 when it should be 1. I have seen some suggestions online recommending sum.1, but that did not work. Can anyone help me out. I would really appreciated it. Thanks in advance!!! ----- mils -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538.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 If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716545.html
mils
|
RE: Aggregate command problem with sum function
|
Administrator
|
"You aren't going to believe that...."
Nope! I am a born skeptic and you provide only anecdotal support for your claim without any sort of replicable context. If your claim were correct there would be a hell of a lot of stink about broken functionality. --
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?" |
|
|
Sorry! You are right, I need some kind of a proof. Please find below what I'm trying to demonstrate. The following syntax (which I've just copied and pasted from Rick) works perfectly. dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. My example (using the previous data) assumes that the data is not sorted, therefore I have selected "pressorted": DATASET DECLARE agg2. SORT CASES BY breakvar. AGGREGATE /OUTFILE='agg2' /PRESORTED /BREAK=breakvar /var1_sum=SUM(var1) /var2_sum=SUM(var2) /N_BREAK=N. The above syntax won't sum variables with just one respondent. am I still doing something wrong? As I said I solved my problem by just sorting the data before. Thanks in advance, Mils. Date: Mon, 3 Dec 2012 05:09:11 -0800 From: [hidden email] To: [hidden email] Subject: RE: Aggregate command problem with sum function "You aren't going to believe that...." Nope! I am a born skeptic and you provide only anecdotal support for your claim without any sort of replicable context. If your claim were correct there would be a hell of a lot of stink about broken functionality. --
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716597.html
mils
|
RE: Aggregate command problem with sum function
|
Administrator
|
"The above syntax won't sum variables with just one respondent. am I still doing something wrong? As I said I solved my problem by just sorting the data before."
BUT you have not demonstrated this! Maybe a LIST command following the aggregate would add credence (but I am in doubt). **PRESORTED can be useful if the data are already sorted on the break variable(s)! Using it on a non sorted file will yield unusual results. It appears the file in question is sorted so specifying PRESORTED is fine but you have not provided evidence of any anomaly.
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?" |
Re: Aggregate command problem with sum function
|
Can you cobble together
a example set of syntax that demonstrates the
problem you are having?
This syntax works. Please edit it so that it reflects the problem you are having. data list list/id (f2) group(f1) x(f2). begin data 1 1 20 2 1 30 3 2 5 4 3 55 5 1 22 end data. aggregate outfile=* /break = group /xsum = sum(x). list. Art Kendall Social Research ConsultantsOn 12/3/2012 9:53 AM, David Marso wrote: "The above syntax won't sum variables with just one respondent. am I still doing something wrong? As I said I solved my problem by just sorting the data before." BUT you have not demonstrated this! Maybe a LIST command following the aggregate would add credence (but I am in doubt). **PRESORTED can be useful if the data are already sorted on the break variable(s)! Using it on a non sorted file will yield unusual results. It appears the file in question is sorted so specifying PRESORTED is fine but you have not provided evidence of any anomaly. ----- 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/Aggregate-command-problem-with-sum-function-tp5716538p5716602.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
Art Kendall
Social Research Consultants |
Automatic reply: Aggregate command problem with sum function
|
|
I am currently out of the office and will be returning Tuesday, December 4. |
Re: Aggregate command problem with sum function
|
|
In reply to this post by mils
Interesting. Presorted means the exact
opposite of what you think, but since the data are in fact already sorted
by breakvar, PRESORTED shouldn't make any difference.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mils <[hidden email]> To: [hidden email], Date: 12/03/2012 11:39 AM Subject: Re: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> Sorry! You are right, I need some kind of a proof. Please find below what I'm trying to demonstrate. The following syntax (which I've just copied and pasted from Rick) works perfectly. dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. My example (using the previous data) assumes that the data is not sorted, therefore I have selected "pressorted": DATASET DECLARE agg2. SORT CASES BY breakvar. AGGREGATE /OUTFILE='agg2' /PRESORTED /BREAK=breakvar /var1_sum=SUM(var1) /var2_sum=SUM(var2) /N_BREAK=N. The above syntax won't sum variables with just one respondent. am I still doing something wrong? As I said I solved my problem by just sorting the data before. Thanks in advance, Mils. Date: Mon, 3 Dec 2012 05:09:11 -0800 From: [hidden email] To: [hidden email] Subject: RE: Aggregate command problem with sum function "You aren't going to believe that...." Nope! I am a born skeptic and you provide only anecdotal support for your claim without any sort of replicable context. If your claim were correct there would be a hell of a lot of stink about broken functionality. -- mils wrote Hi Everyone, Thanks for your quick answers. You aren't going to believe that, but (at least for me) sum function won't work properly if you select the option “sort file before aggregatingâ€. So what I did, was to sort the data first (by the variable use in the aggregate command) and then run the aggregate command. Now it works! Hey Rick, could you confirm that? Thanks, mils Date: Fri, 30 Nov 2012 07:15:24 -0800 From: [hidden email] To: [hidden email] Subject: Re: Aggregate command problem with sum function First, by default Aggregate will sum all the non-missing case values; so sum.1 is essentially redundant since the .n argument specifies the minimum number of non-missing cases, which by default is 1. Second, the sum of one case will only be 1 if the value of the variable for that case is 1. If the value is 0 rather than missing, then the sum will be 0. This example seems to generate the correct results dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mils <[hidden email]> To: [hidden email], Date: 11/30/2012 08:25 AM Subject: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Everyone! I need some help with the aggregate command. Below is the command I'm using: DATASET DECLARE test. SORT CASES BY UserID. AGGREGATE /OUTFILE='test' /PRESORTED /BREAK=UserID /t1f_Enbrel_sum=sum(t1f_Enbrel) /t1f_Humira_sum=sum(t1f_Humira) /t1f_Remicade_sum=sum(t1f_Remicade) /t1f_MabThera_sum=sum(t1f_MabThera) /t1f_Orencia_sum=sum(t1f_Orencia) /t1f_RoActemra_sum=sum(t1f_RoActemra) /t1f_Cimzia_sum=sum(t1f_Cimzia) /t1f_Simponi_sum=sum(t1f_Simponi) /N_BREAK=N. The issue is that for those variables (t1f_) with just one case the sum seems to fail. The aggregate is 0 when it should be 1. I have seen some suggestions online recommending sum.1, but that did not work. Can anyone help me out. I would really appreciated it. Thanks in advance!!! ----- mils -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538.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 If you reply to this email, your message will be added to the discussion below: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716545.html To unsubscribe from Aggregate command problem with sum function, click here. NAML Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. If you reply to this email, your message will be added to the discussion below: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716597.html To unsubscribe from Aggregate command problem with sum function, click here. NAML mils
View this message in context: RE: Aggregate command problem with sum function Sent from the SPSSX Discussion mailing list archive at Nabble.com. |
Re: Aggregate command problem with sum function
|
|
In reply to this post by mils
Aggregate does not require sorted data.
PRESORTED should only be used when the data are already sorted and even
then is only useful for very large data files.
If the file is not sorted and you specify PRESORTED, you should see this warning: Warning # 10950 The working file read by AGGREGATE was not in ascending sequence on the break variables. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Rick Oliver/Chicago/IBM To: mils <[hidden email]>, Cc: [hidden email] Date: 12/03/2012 11:45 AM Subject: Re: Aggregate command problem with sum function Interesting. Presorted means the exact opposite of what you think, but since the data are in fact already sorted by breakvar, PRESORTED shouldn't make any difference. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mils <[hidden email]> To: [hidden email], Date: 12/03/2012 11:39 AM Subject: Re: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> Sorry! You are right, I need some kind of a proof. Please find below what I'm trying to demonstrate. The following syntax (which I've just copied and pasted from Rick) works perfectly. dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. My example (using the previous data) assumes that the data is not sorted, therefore I have selected "pressorted": DATASET DECLARE agg2. SORT CASES BY breakvar. AGGREGATE /OUTFILE='agg2' /PRESORTED /BREAK=breakvar /var1_sum=SUM(var1) /var2_sum=SUM(var2) /N_BREAK=N. The above syntax won't sum variables with just one respondent. am I still doing something wrong? As I said I solved my problem by just sorting the data before. Thanks in advance, Mils. Date: Mon, 3 Dec 2012 05:09:11 -0800 From: [hidden email] To: [hidden email] Subject: RE: Aggregate command problem with sum function "You aren't going to believe that...." Nope! I am a born skeptic and you provide only anecdotal support for your claim without any sort of replicable context. If your claim were correct there would be a hell of a lot of stink about broken functionality. -- mils wrote Hi Everyone, Thanks for your quick answers. You aren't going to believe that, but (at least for me) sum function won't work properly if you select the option “sort file before aggregatingâ€. So what I did, was to sort the data first (by the variable use in the aggregate command) and then run the aggregate command. Now it works! Hey Rick, could you confirm that? Thanks, mils Date: Fri, 30 Nov 2012 07:15:24 -0800 From: [hidden email] To: [hidden email] Subject: Re: Aggregate command problem with sum function First, by default Aggregate will sum all the non-missing case values; so sum.1 is essentially redundant since the .n argument specifies the minimum number of non-missing cases, which by default is 1. Second, the sum of one case will only be 1 if the value of the variable for that case is 1. If the value is 0 rather than missing, then the sum will be 0. This example seems to generate the correct results dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mils <[hidden email]> To: [hidden email], Date: 11/30/2012 08:25 AM Subject: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Everyone! I need some help with the aggregate command. Below is the command I'm using: DATASET DECLARE test. SORT CASES BY UserID. AGGREGATE /OUTFILE='test' /PRESORTED /BREAK=UserID /t1f_Enbrel_sum=sum(t1f_Enbrel) /t1f_Humira_sum=sum(t1f_Humira) /t1f_Remicade_sum=sum(t1f_Remicade) /t1f_MabThera_sum=sum(t1f_MabThera) /t1f_Orencia_sum=sum(t1f_Orencia) /t1f_RoActemra_sum=sum(t1f_RoActemra) /t1f_Cimzia_sum=sum(t1f_Cimzia) /t1f_Simponi_sum=sum(t1f_Simponi) /N_BREAK=N. The issue is that for those variables (t1f_) with just one case the sum seems to fail. The aggregate is 0 when it should be 1. I have seen some suggestions online recommending sum.1, but that did not work. Can anyone help me out. I would really appreciated it. Thanks in advance!!! ----- mils -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538.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 If you reply to this email, your message will be added to the discussion below: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716545.html To unsubscribe from Aggregate command problem with sum function, click here. NAML Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. If you reply to this email, your message will be added to the discussion below: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716597.html To unsubscribe from Aggregate command problem with sum function, click here. NAML mils
View this message in context: RE: Aggregate command problem with sum function Sent from the SPSSX Discussion mailing list archive at Nabble.com. |
|
|
Hi Rick, Thanks for your answer. As I guessed from the beginning I
was doing something wrong. Sorry, I made the assumption that the aggregate
commend wasn’t working properly (my mistake). Below is a mock example of what I
was doing, so you can understand why I thought there was something wrong with the
command (hope it helps): DATA LIST LIST / doctor (A15) prod1 prod2 prod3 prod4. BEGIN DATA. "AMC" 1 0 1 0 "AMC" 1 5 7 0 "AMC" 1 0 8 9 "AMC" 1 5 1 7 "AMB" 0 9 6 1 "AMB" 0 2 3 1 "AMB" 0 2 9 1 "AMB" 0 2 6 4 "AMB" 0 3 3 1 "ACS" 1 0 3 0 "ACS" 1 1 1 1 "ACS" 1 0 0 0 "AMM" 0 5 4 1 END DATA. fre prod1 prod2 prod3 prod4. AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /BREAK=make /prod1_sum=SUM(prod1) /prod2_sum=SUM(prod2) /prod3_sum=SUM(prod3) /prod4_sum=SUM(prod4). ****output******. prod1_sum prod2_sum prod3_sum prod4_sum 3.00 1.00 4.00 1.00 3.00 1.00 4.00 1.00 3.00 1.00 4.00 1.00 .00 18.00 27.00 8.00 .00 18.00 27.00 8.00 .00 18.00 27.00 8.00 .00 18.00 27.00 8.00 .00 18.00 27.00 8.00 4.00 10.00 17.00 16.00 4.00 10.00 17.00 16.00 4.00 10.00 17.00 16.00 4.00 10.00 17.00 16.00 .00 5.00 4.00 1.00 Using the above “aggregate” results are as expected. Everything is fine. However using the below aggregate command: SORT CASES BY make. AGGREGATE /OUTFILE=* MODE=ADDVARIABLES /PRESORTED /BREAK=make /prod1_sum_2=SUM(prod1) /prod2_sum_2=SUM(prod2) /prod3_sum_2=SUM(prod3) /prod4_sum_2=SUM(prod4). ****output******. prod1_sum_2 prod2_sum_2 prod3_sum_2 prod4_sum_2 3.00 .00 4.00 .00 3.00 .00 4.00 .00 .00 .00 4.00 .00 .00 18.00 27.00 8.00 .00 18.00 27.00 8.00 .00 18.00 27.00 8.00 .00 18.00 27.00 8.00 .00 18.00 27.00 8.00 4.00 10.00 17.00 16.00 4.00 10.00 17.00 16.00 4.00 10.00 17.00 16.00 4.00 10.00 17.00 16.00 .00 5.00 4.00 .00 Here results are not as I was expecting. There are products where
the sum now is 0, when they should be 1. I was using
the “Presorted” command when I shouldn’t (and I guess that's why the results are different). So, what I thought it was an SPSS error
it was actually my mistake in doing a wrong assumption of the data. Thanks a lot for your help! mils Date: Mon, 3 Dec 2012 10:06:59 -0800 From: [hidden email] To: [hidden email] Subject: Re: Aggregate command problem with sum function Aggregate does not require sorted data. PRESORTED should only be used when the data are already sorted and even then is only useful for very large data files. If the file is not sorted and you specify PRESORTED, you should see this warning: Warning # 10950 The working file read by AGGREGATE was not in ascending sequence on the break variables. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Rick Oliver/Chicago/IBM To: mils <[hidden email]>, Cc: [hidden email] Date: 12/03/2012 11:45 AM Subject: Re: Aggregate command problem with sum function Interesting. Presorted means the exact opposite of what you think, but since the data are in fact already sorted by breakvar, PRESORTED shouldn't make any difference. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mils <[hidden email]> To: [hidden email], Date: 12/03/2012 11:39 AM Subject: Re: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> Sorry! You are right, I need some kind of a proof. Please find below what I'm trying to demonstrate. The following syntax (which I've just copied and pasted from Rick) works perfectly. dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. My example (using the previous data) assumes that the data is not sorted, therefore I have selected "pressorted": DATASET DECLARE agg2. SORT CASES BY breakvar. AGGREGATE /OUTFILE='agg2' /PRESORTED /BREAK=breakvar /var1_sum=SUM(var1) /var2_sum=SUM(var2) /N_BREAK=N. The above syntax won't sum variables with just one respondent. am I still doing something wrong? As I said I solved my problem by just sorting the data before. Thanks in advance, Mils. Date: Mon, 3 Dec 2012 05:09:11 -0800 From: [hidden email] To: [hidden email] Subject: RE: Aggregate command problem with sum function "You aren't going to believe that...." Nope! I am a born skeptic and you provide only anecdotal support for your claim without any sort of replicable context. If your claim were correct there would be a hell of a lot of stink about broken functionality. -- mils wrote Hi Everyone, Thanks for your quick answers. You aren't going to believe that, but (at least for me) sum function won't work properly if you select the option “sort file before aggregating�. So what I did, was to sort the data first (by the variable use in the aggregate command) and then run the aggregate command. Now it works! Hey Rick, could you confirm that? Thanks, mils Date: Fri, 30 Nov 2012 07:15:24 -0800 From: [hidden email] To: [hidden email] Subject: Re: Aggregate command problem with sum function First, by default Aggregate will sum all the non-missing case values; so sum.1 is essentially redundant since the .n argument specifies the minimum number of non-missing cases, which by default is 1. Second, the sum of one case will only be 1 if the value of the variable for that case is 1. If the value is 0 rather than missing, then the sum will be 0. This example seems to generate the correct results dataset close all. new file. preserve. set mxwarns=0. data list list (",") /breakvar var1 var2. begin data 1,1,1 1,1, 1,0, 2,1, 2,, 3,,1 4,0,0 end data. restore. dataset name original. dataset declare agg. AGGREGATE /OUTFILE=agg /BREAK=breakvar /var1_sum=SUM.1(var1) /var2_sum=SUM(var2) /nbreak=n. dataset activate agg. Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: mils <[hidden email]> To: [hidden email], Date: 11/30/2012 08:25 AM Subject: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi Everyone! I need some help with the aggregate command. Below is the command I'm using: DATASET DECLARE test. SORT CASES BY UserID. AGGREGATE /OUTFILE='test' /PRESORTED /BREAK=UserID /t1f_Enbrel_sum=sum(t1f_Enbrel) /t1f_Humira_sum=sum(t1f_Humira) /t1f_Remicade_sum=sum(t1f_Remicade) /t1f_MabThera_sum=sum(t1f_MabThera) /t1f_Orencia_sum=sum(t1f_Orencia) /t1f_RoActemra_sum=sum(t1f_RoActemra) /t1f_Cimzia_sum=sum(t1f_Cimzia) /t1f_Simponi_sum=sum(t1f_Simponi) /N_BREAK=N. The issue is that for those variables (t1f_) with just one case the sum seems to fail. The aggregate is 0 when it should be 1. I have seen some suggestions online recommending sum.1, but that did not work. Can anyone help me out. I would really appreciated it. Thanks in advance!!! ----- mils -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538.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 If you reply to this email, your message will be added to the discussion below: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716545.html To unsubscribe from Aggregate command problem with sum function, click here. NAML Please reply to the list and not to my personal email. Those desiring my consulting or training services please feel free to email me. If you reply to this email, your message will be added to the discussion below: http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716597.html To unsubscribe from Aggregate command problem with sum function, click here. NAML mils View this message in context: RE: Aggregate command problem with sum function Sent from the SPSSX Discussion mailing list archive at Nabble.com. If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716615.html
mils
|
RE: Aggregate command problem with sum function
|
Administrator
|
doctor?
make? You should straighten that discrepancy and list whatever BREAKS along with the SUMs otherwise it is very confusing because the data are not in the original order and frankly I am not going to bother trying to line it up. Also: Have you looked at the FM to study the various nuances of ADDVARIABLES and PRESORTED?
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?" |
|
|
it should be "make". Where can I find the " FM to study the various nuances of ADDVARIABLES and PRESORTED"? Date: Thu, 6 Dec 2012 04:33:33 -0800 From: [hidden email] To: [hidden email] Subject: RE: Aggregate command problem with sum function doctor? make? You should straighten that discrepancy and list whatever BREAKS along with the SUMs otherwise it is very confusing because the data are not in the original order and frankly I am not going to bother trying to line it up. Also: Have you looked at the FM to study the various nuances of ADDVARIABLES and PRESORTED?
Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me. If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716714.html
mils
|
RE: Aggregate command problem with sum function
Administrator
|
To open the FM, click on Help > Command Syntax Reference.
--
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/). |
RE: Aggregate command problem with sum function
|
Administrator
|
You can also download the pdf from the insufferable labyrinth known as the IBM website ;-(
There is some root for Documentation and then product. I posted a link the other day so you can also search on my recent posts and you should find the link. --
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?" |
Re: Aggregate command problem with sum function
|
|
There are direct links to the SPSS Statistics
documentation in both help and pdf formats in the Important Bookmarks section
of the SPSS Community website at www.ibm.com/developerworks/spssdevcentral.
Not hard at all to navigate.
Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] new phone: 720-342-5621 From: David Marso <[hidden email]> To: [hidden email], Date: 12/06/2012 07:49 AM Subject: Re: [SPSSX-L] Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> You can also download the pdf from the insufferable labyrinth known as the IBM website ;-( There is some root for Documentation and then product. I posted a link the other day so you can also search on my recent posts and you should find the link. -- Bruce Weaver wrote > To open the FM, click on Help > Command Syntax Reference. > > mils wrote >> it should be "make". Where can I find the " FM to study the various >> nuances of ADDVARIABLES and PRESORTED"? Date: Thu, 6 Dec 2012 04:33:33 >> -0800 >> From: >> ml-node+s1045642n5716714h97@.nabble >> To: >> jcasellasvega@.CO >> Subject: RE: Aggregate command problem with sum function >> >> >> >> doctor? >> >> make? >> >> You should straighten that discrepancy and list whatever BREAKS along >> with the SUMs otherwise it is very confusing because the data are not in >> the original order and frankly I am not going to bother trying to line it >> up. >> >> Also: Have you looked at the FM to study the various nuances of >> ADDVARIABLES and PRESORTED? >> >> >> >> mils wrote >> Hi Rick, >> >> >> Thanks for your answer. As I guessed from the beginning I >> >> was doing something wrong. Sorry, I made the assumption that the >> aggregate >> >> commend wasn’t working properly (my mistake). Below is a mock example of >> what I >> >> was doing, so you can understand why I thought there was something wrong >> with the >> >> command (hope it helps): >> >> >> >> >> DATA LIST LIST >> >> >> / doctor (A15) prod1 >> >> prod2 prod3 prod4. >> >> >> BEGIN DATA. >> >> >> "AMC" 1 0 1 0 >> >> >> "AMC" 1 5 7 0 >> >> >> "AMC" 1 0 8 9 >> >> >> "AMC" 1 5 1 7 >> >> >> "AMB" 0 9 6 1 >> >> >> "AMB" 0 2 3 1 >> >> >> "AMB" 0 2 9 1 >> >> >> "AMB" 0 2 6 4 >> >> >> "AMB" 0 3 3 1 >> >> >> "ACS" 1 0 3 0 >> >> >> "ACS" 1 1 1 1 >> >> >> "ACS" 1 0 0 0 >> >> >> "AMM" 0 5 4 1 >> >> >> END DATA. >> >> >> fre prod1 prod2 prod3 prod4. >> >> >> >> >> AGGREGATE >> >> >> /OUTFILE=* >> >> MODE=ADDVARIABLES >> >> >> /BREAK=make >> >> >> >> /prod1_sum=SUM(prod1) >> >> >> >> >> /prod2_sum=SUM(prod2) >> >> >> /prod3_sum=SUM(prod3) >> >> >> >> >> >> /prod4_sum=SUM(prod4). >> >> >> >> >> >> ****output******. >> >> >> >> >> >> prod1_sum prod2_sum prod3_sum prod4_sum >> >> >> 3.00 1.00 4.00 1.00 >> >> >> 3.00 1.00 4.00 1.00 >> >> >> 3.00 1.00 4.00 1.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> .00 5.00 4.00 1.00 >> >> >> >> >> >> Using the above “aggregate” results are as expected. Everything is >> >> fine. However using the below aggregate command: >> >> >> >> >> >> SORT CASES BY make. >> >> >> AGGREGATE >> >> >> /OUTFILE=* >> >> MODE=ADDVARIABLES >> >> >> /PRESORTED >> >> >> /BREAK=make >> >> >> >> >> /prod1_sum_2=SUM(prod1) >> >> >> >> >> /prod2_sum_2=SUM(prod2) >> >> >> >> >> /prod3_sum_2=SUM(prod3) >> >> >> >> >> /prod4_sum_2=SUM(prod4). >> >> >> >> >> >> ****output******. >> >> >> >> >> >> prod1_sum_2 prod2_sum_2 prod3_sum_2 prod4_sum_2 >> >> >> 3.00 .00 4.00 .00 >> >> >> 3.00 .00 4.00 .00 >> >> >> .00 .00 4.00 .00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> .00 5.00 4.00 .00 >> >> >> Here results are not as I was expecting. There are products where >> >> the sum now is 0, when they should be 1. I was using >> >> the “Presorted” command when I shouldn’t (and I guess that's why the >> results are different). So, what I thought it was an SPSS error >> >> it was actually my mistake in doing a wrong assumption of the data. >> >> >> Thanks a lot for your help! >> >> >> mils >> >> >> >> Date: Mon, 3 Dec 2012 10:06:59 -0800 >> >> From: [hidden email] >> To: [hidden email] >> Subject: Re: Aggregate command problem with sum function >> >> >> >> >> Aggregate does not require sorted data. >> >> PRESORTED should only be used when the data are already sorted and even >> >> then is only useful for very large data files. >> >> >> >> >> If the file is not sorted and you specify >> >> PRESORTED, you should see this warning: >> >> >> >> >> Warning # 10950 >> >> >> The working file read by AGGREGATE >> >> was not in ascending sequence on the break >> >> >> variables. >> >> >> >> >> >> >> >> Rick Oliver >> >> >> Senior Information Developer >> >> >> IBM Business Analytics (SPSS) >> >> >> E-mail: [hidden email] >> >> >> >> >> >> >> >> >> From: >> >> Rick Oliver/Chicago/IBM >> >> >> To: >> >> mils <[hidden email]>, >> >> >> >> Cc: >> >> [hidden email] >> >> >> Date: >> >> 12/03/2012 11:45 AM >> >> >> Subject: >> >> Re: Aggregate >> >> command problem with sum function >> >> >> >> >> >> >> >> Interesting. Presorted means the exact >> >> opposite of what you think, but since the data are in fact already sorted >> >> by breakvar, PRESORTED shouldn't make any difference. >> >> >> >> >> Rick Oliver >> >> >> Senior Information Developer >> >> >> IBM Business Analytics (SPSS) >> >> >> E-mail: [hidden email] >> >> >> >> >> >> >> >> >> >> >> From: >> >> mils <[hidden email]> >> >> >> To: >> >> [hidden email], >> >> >> >> Date: >> >> 12/03/2012 11:39 AM >> >> >> Subject: >> >> Re: Aggregate >> >> command problem with sum function >> >> >> Sent by: >> >> "SPSSX(r) >> >> Discussion" <[hidden email]> >> >> >> >> >> >> >> >> >> >> >> >> Sorry! You are right, I need some kind of a proof. Please find below what >> >> I'm trying to demonstrate. >> >> >> >> >> The following syntax (which I've just copied and pasted from Rick) works >> >> perfectly. >> >> >> >> >> dataset close all. >> >> >> new file. >> >> >> preserve. >> >> >> set mxwarns=0. >> >> >> data list list (",") /breakvar var1 var2. >> >> >> begin data >> >> >> 1,1,1 >> >> >> 1,1, >> >> >> 1,0, >> >> >> 2,1, >> >> >> 2,, >> >> >> 3,,1 >> >> >> 4,0,0 >> >> >> end data. >> >> >> restore. >> >> >> dataset name original. >> >> >> dataset declare agg. >> >> >> AGGREGATE >> >> >> /OUTFILE=agg >> >> >> /BREAK=breakvar >> >> >> /var1_sum=SUM.1(var1) >> >> >> /var2_sum=SUM(var2) >> >> >> /nbreak=n. >> >> >> dataset activate agg. >> >> >> >> >> My example (using the previous data) assumes that the data is not sorted, >> >> therefore I have selected "pressorted": >> >> >> >> >> DATASET DECLARE agg2. >> >> >> SORT CASES BY breakvar. >> >> >> AGGREGATE >> >> >> /OUTFILE='agg2' >> >> >> /PRESORTED >> >> >> /BREAK=breakvar >> >> >> /var1_sum=SUM(var1) >> >> >> /var2_sum=SUM(var2) >> >> >> /N_BREAK=N. >> >> >> >> >> The above syntax won't sum variables with just one respondent. am I still >> >> doing something wrong? As I said I solved my problem by just sorting the >> >> data before. >> >> >> >> >> Thanks in advance, >> >> >> >> >> Mils. >> >> >> >> >> >> Date: Mon, 3 Dec 2012 05:09:11 -0800 >> >> >> From: [hidden >> >> email] >> >> >> To: [hidden >> >> email] >> >> >> Subject: RE: Aggregate command problem with sum function >> >> >> >> >> "You aren't going to believe that...." >> >> >> Nope! I am a born skeptic and you provide only anecdotal support >> >> for your claim without any sort of replicable context. If your claim >> >> were correct there would be a hell of a lot of stink about broken >> functionality. >> >> >> >> -- >> >> >> mils wrote >> >> >> Hi Everyone, >> >> >> >> >> Thanks for >> >> >> your quick answers. You aren't going to believe that, but (at least for >> >> me) sum >> >> >> function won't work properly if you select the option “sort file before >> >> aggregatingâ€�. >> >> >> So what I did, was to sort the data first (by the variable use in the >> aggregate >> >> >> >> command) and then run the aggregate command. Now it works! >> >> >> >> >> Hey Rick, could you confirm that? >> >> >> >> >> Thanks, mils >> >> >> >> >> >> >> Date: Fri, 30 Nov 2012 07:15:24 -0800 >> >> >> From: [hidden >> >> email] >> >> >> To: [hidden >> >> email] >> >> >> Subject: Re: Aggregate command problem with sum function >> >> >> >> >> >> >> >> >> First, by default Aggregate will sum all >> >> >> the non-missing case values; so sum.1 is essentially redundant since the >> >> >> >> .n argument specifies the minimum number of non-missing cases, which by >> >> >> >> default is 1. >> >> >> >> >> >> >> >> >> Second, the sum of one case will only >> >> >> be 1 if the value of the variable for that case is 1. If the value is 0 >> >> >> >> rather than missing, then the sum will be 0. >> >> >> >> >> >> >> >> >> This example seems to generate the correct >> >> >> results >> >> >> >> >> >> >> >> >> >> >> >> >> dataset close all. >> >> >> >> >> new file. >> >> >> >> >> preserve. >> >> >> >> >> set mxwarns=0. >> >> >> >> >> data list list (",") /breakvar >> >> >> var1 var2. >> >> >> >> >> begin data >> >> >> >> >> 1,1,1 >> >> >> >> >> 1,1, >> >> >> >> >> 1,0, >> >> >> >> >> 2,1, >> >> >> >> >> 2,, >> >> >> >> >> 3,,1 >> >> >> >> >> 4,0,0 >> >> >> >> >> end data. >> >> >> >> >> restore. >> >> >> >> >> dataset name original. >> >> >> >> >> dataset declare agg. >> >> >> >> >> AGGREGATE >> >> >> >> >> /OUTFILE=agg >> >> >> >> >> /BREAK=breakvar >> >> >> >> >> /var1_sum=SUM.1(var1) >> >> >> >> >> /var2_sum=SUM(var2) >> >> >> >> >> /nbreak=n. >> >> >> >> >> dataset activate agg. >> >> >> >> >> >> >> >> >> Rick Oliver >> >> >> >> >> Senior Information Developer >> >> >> >> >> IBM Business Analytics (SPSS) >> >> >> >> >> E-mail: [hidden email] >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> From: >> >> >> mils <[hidden email]> >> >> >> >> >> To: >> >> >> [hidden email], >> >> >> >> >> >> >> Date: >> >> >> 11/30/2012 08:25 AM >> >> >> >> >> Subject: >> >> >> Aggregate command >> >> >> problem with sum function >> >> >> >> >> Sent by: >> >> >> "SPSSX(r) >> >> >> Discussion" <[hidden email]> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> Hi Everyone! >> >> >> >> >> >> >> >> >> I need some help with the aggregate command. Below is the command I'm >> using: >> >> >> >> >> >> >> >> >> >> DATASET DECLARE test. >> >> >> >> >> SORT CASES BY UserID. >> >> >> >> >> AGGREGATE >> >> >> >> >> /OUTFILE='test' >> >> >> >> >> /PRESORTED >> >> >> >> >> /BREAK=UserID >> >> >> >> >> /t1f_Enbrel_sum=sum(t1f_Enbrel) >> >> >> >> >> /t1f_Humira_sum=sum(t1f_Humira) >> >> >> >> >> /t1f_Remicade_sum=sum(t1f_Remicade) >> >> >> >> >> /t1f_MabThera_sum=sum(t1f_MabThera) >> >> >> >> >> /t1f_Orencia_sum=sum(t1f_Orencia) >> >> >> >> >> /t1f_RoActemra_sum=sum(t1f_RoActemra) >> >> >> >> >> /t1f_Cimzia_sum=sum(t1f_Cimzia) >> >> >> >> >> /t1f_Simponi_sum=sum(t1f_Simponi) >> >> >> >> >> /N_BREAK=N. >> >> >> >> >> >> >> >> >> The issue is that for those variables (t1f_) with just one case the sum >> >> >> >> >> >> seems to fail. The aggregate is 0 when it should be 1. I have seen some >> >> >> >> >> >> suggestions online recommending sum.1, but that did not work. >> >> >> >> >> >> >> >> >> Can anyone help me out. I would really appreciated it. >> >> >> >> >> >> >> >> >> Thanks in advance!!! >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> ----- >> >> >> >> >> mils >> >> >> >> >> -- >> >> >> >> >> View this message in context: >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538.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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> If you reply to >> >> this email, your message will be added to the discussion below: >> >> >> >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716545.html >> >> >> >> >> >> >> >> >> >> >> To unsubscribe >> >> from Aggregate command problem with sum function, click here. >> >> >> >> >> NAML >> >> >> Please reply to the list and not to my personal >> >> email. >> >> >> Those desiring my consulting or training services please feel free to >> >> me. >> >> >> >> >> >> >> >> >> >> If you reply to this email, your message >> >> will be added to the discussion below: >> >> >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716597.html >> >> >> To unsubscribe from Aggregate command problem >> >> with sum function, click >> >> here. >> >> >> NAML >> >> >> mils >> >> >> >> >> >> View this message in context: RE: >> >> Aggregate command problem with sum function >> >> >> Sent from the SPSSX >> >> Discussion mailing list archive at Nabble.com. >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> If you reply to this email, your message will be added to >> the discussion below: >> >> >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716615.html >> >> >> >> >> >> >> To unsubscribe from Aggregate command problem with sum >> function, click here. >> >> >> NAML >> >> >> >> >> >> >> >> Please reply to the list and not to my personal email. >> >> Those desiring my consulting or training services please feel free to >> email me. >> >> >> >> >> >> >> >> If you reply to this email, your message will be added to the >> discussion below: >> >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716714.html >> >> >> >> To unsubscribe from Aggregate command problem with sum function, click >> here. >> >> NAML ----- 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/Aggregate-command-problem-with-sum-function-tp5716538p5716718.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 |
RE: Aggregate command problem with sum function
|
Administrator
|
In reply to this post by David Marso
It's often easier to get to the desired corner of the labyrinth if you enter via Google. E.g., Googling on <spss command syntax reference> took me to:
http://www-01.ibm.com/support/docview.wss?uid=swg27021213 Then a CTL-F search for "command" on that page took me to a link for the PDF.
--
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/). |
Re: Aggregate command problem with sum function
|
|
If you are Googling for it, I suggest writing
the query as
site:ibm.com spss command syntax reference Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] new phone: 720-342-5621 From: Bruce Weaver <[hidden email]> To: [hidden email], Date: 12/06/2012 08:13 AM Subject: Re: [SPSSX-L] Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> It's often easier to get to the desired corner of the labyrinth if you enter via Google. E.g., Googling on <spss command syntax reference> took me to: http://www-01.ibm.com/support/docview.wss?uid=swg27021213 Then a CTL-F search for "command" on that page took me to a link for the PDF. David Marso wrote > You can also download the pdf from the insufferable labyrinth known as the > IBM website ;-( > There is some root for Documentation and then product. I posted a link > the other day so you can also search on my recent posts and you should > find the link. > -- > Bruce Weaver wrote >> To open the FM, click on Help > Command Syntax Reference. >> >> mils wrote >>> it should be "make". Where can I find the " FM to study the various >>> nuances of ADDVARIABLES and PRESORTED"? Date: Thu, 6 Dec 2012 04:33:33 >>> -0800 >>> From: >>> ml-node+s1045642n5716714h97@.nabble >>> To: >>> jcasellasvega@.CO >>> Subject: RE: Aggregate command problem with sum function >>> >>> >>> >>> doctor? >>> >>> make? >>> >>> You should straighten that discrepancy and list whatever BREAKS along >>> with the SUMs otherwise it is very confusing because the data are not in >>> the original order and frankly I am not going to bother trying to line >>> it up. >>> >>> Also: Have you looked at the FM to study the various nuances of >>> ADDVARIABLES and PRESORTED? >>> >>> >>> >>> mils wrote >>> Hi Rick, >>> >>> >>> Thanks for your answer. As I guessed from the beginning I >>> >>> was doing something wrong. Sorry, I made the assumption that the >>> aggregate >>> >>> commend wasn’t working properly (my mistake). Below is a mock example of >>> what I >>> >>> was doing, so you can understand why I thought there was something wrong >>> with the >>> >>> command (hope it helps): >>> >>> >>> >>> >>> DATA LIST LIST >>> >>> >>> / doctor (A15) prod1 >>> >>> prod2 prod3 prod4. >>> >>> >>> BEGIN DATA. >>> >>> >>> "AMC" 1 0 1 0 >>> >>> >>> "AMC" 1 5 7 0 >>> >>> >>> "AMC" 1 0 8 9 >>> >>> >>> "AMC" 1 5 1 7 >>> >>> >>> "AMB" 0 9 6 1 >>> >>> >>> "AMB" 0 2 3 1 >>> >>> >>> "AMB" 0 2 9 1 >>> >>> >>> "AMB" 0 2 6 4 >>> >>> >>> "AMB" 0 3 3 1 >>> >>> >>> "ACS" 1 0 3 0 >>> >>> >>> "ACS" 1 1 1 1 >>> >>> >>> "ACS" 1 0 0 0 >>> >>> >>> "AMM" 0 5 4 1 >>> >>> >>> END DATA. >>> >>> >>> fre prod1 prod2 prod3 prod4. >>> >>> >>> >>> >>> AGGREGATE >>> >>> >>> /OUTFILE=* >>> >>> MODE=ADDVARIABLES >>> >>> >>> /BREAK=make >>> >>> >>> >>> /prod1_sum=SUM(prod1) >>> >>> >>> >>> >>> /prod2_sum=SUM(prod2) >>> >>> >>> /prod3_sum=SUM(prod3) >>> >>> >>> >>> >>> >>> /prod4_sum=SUM(prod4). >>> >>> >>> >>> >>> >>> ****output******. >>> >>> >>> >>> >>> >>> prod1_sum prod2_sum prod3_sum prod4_sum >>> >>> >>> 3.00 1.00 4.00 1.00 >>> >>> >>> 3.00 1.00 4.00 1.00 >>> >>> >>> 3.00 1.00 4.00 1.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> 4.00 10.00 17.00 16.00 >>> >>> >>> 4.00 10.00 17.00 16.00 >>> >>> >>> 4.00 10.00 17.00 16.00 >>> >>> >>> 4.00 10.00 17.00 16.00 >>> >>> >>> .00 5.00 4.00 1.00 >>> >>> >>> >>> >>> >>> Using the above “aggregate” results are as expected. Everything is >>> >>> fine. However using the below aggregate command: >>> >>> >>> >>> >>> >>> SORT CASES BY make. >>> >>> >>> AGGREGATE >>> >>> >>> /OUTFILE=* >>> >>> MODE=ADDVARIABLES >>> >>> >>> /PRESORTED >>> >>> >>> /BREAK=make >>> >>> >>> >>> >>> /prod1_sum_2=SUM(prod1) >>> >>> >>> >>> >>> /prod2_sum_2=SUM(prod2) >>> >>> >>> >>> >>> /prod3_sum_2=SUM(prod3) >>> >>> >>> >>> >>> /prod4_sum_2=SUM(prod4). >>> >>> >>> >>> >>> >>> ****output******. >>> >>> >>> >>> >>> >>> prod1_sum_2 prod2_sum_2 prod3_sum_2 prod4_sum_2 >>> >>> >>> 3.00 .00 4.00 .00 >>> >>> >>> 3.00 .00 4.00 .00 >>> >>> >>> .00 .00 4.00 .00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> .00 18.00 27.00 8.00 >>> >>> >>> 4.00 10.00 17.00 16.00 >>> >>> >>> 4.00 10.00 17.00 16.00 >>> >>> >>> 4.00 10.00 17.00 16.00 >>> >>> >>> 4.00 10.00 17.00 16.00 >>> >>> >>> .00 5.00 4.00 .00 >>> >>> >>> Here results are not as I was expecting. There are products where >>> >>> the sum now is 0, when they should be 1. I was using >>> >>> the “Presorted” command when I shouldn’t (and I guess that's why the >>> results are different). So, what I thought it was an SPSS error >>> >>> it was actually my mistake in doing a wrong assumption of the data. >>> >>> >>> Thanks a lot for your help! >>> >>> >>> mils >>> >>> >>> >>> Date: Mon, 3 Dec 2012 10:06:59 -0800 >>> >>> From: [hidden email] >>> To: [hidden email] >>> Subject: Re: Aggregate command problem with sum function >>> >>> >>> >>> >>> Aggregate does not require sorted data. >>> >>> PRESORTED should only be used when the data are already sorted and even >>> >>> then is only useful for very large data files. >>> >>> >>> >>> >>> If the file is not sorted and you specify >>> >>> PRESORTED, you should see this warning: >>> >>> >>> >>> >>> Warning # 10950 >>> >>> >>> The working file read by AGGREGATE >>> >>> was not in ascending sequence on the break >>> >>> >>> variables. >>> >>> >>> >>> >>> >>> >>> >>> Rick Oliver >>> >>> >>> Senior Information Developer >>> >>> >>> IBM Business Analytics (SPSS) >>> >>> >>> E-mail: [hidden email] >>> >>> >>> >>> >>> >>> >>> >>> >>> From: >>> >>> Rick Oliver/Chicago/IBM >>> >>> >>> To: >>> >>> mils <[hidden email]>, >>> >>> >>> >>> Cc: >>> >>> [hidden email] >>> >>> >>> Date: >>> >>> 12/03/2012 11:45 AM >>> >>> >>> Subject: >>> >>> Re: Aggregate >>> >>> command problem with sum function >>> >>> >>> >>> >>> >>> >>> >>> Interesting. Presorted means the exact >>> >>> opposite of what you think, but since the data are in fact already >>> sorted >>> >>> by breakvar, PRESORTED shouldn't make any difference. >>> >>> >>> >>> >>> Rick Oliver >>> >>> >>> Senior Information Developer >>> >>> >>> IBM Business Analytics (SPSS) >>> >>> >>> E-mail: [hidden email] >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> From: >>> >>> mils <[hidden email]> >>> >>> >>> To: >>> >>> [hidden email], >>> >>> >>> >>> Date: >>> >>> 12/03/2012 11:39 AM >>> >>> >>> Subject: >>> >>> Re: Aggregate >>> >>> command problem with sum function >>> >>> >>> Sent by: >>> >>> "SPSSX(r) >>> >>> Discussion" <[hidden email]> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> Sorry! You are right, I need some kind of a proof. Please find below >>> what >>> >>> I'm trying to demonstrate. >>> >>> >>> >>> >>> The following syntax (which I've just copied and pasted from Rick) works >>> >>> perfectly. >>> >>> >>> >>> >>> dataset close all. >>> >>> >>> new file. >>> >>> >>> preserve. >>> >>> >>> set mxwarns=0. >>> >>> >>> data list list (",") /breakvar var1 var2. >>> >>> >>> begin data >>> >>> >>> 1,1,1 >>> >>> >>> 1,1, >>> >>> >>> 1,0, >>> >>> >>> 2,1, >>> >>> >>> 2,, >>> >>> >>> 3,,1 >>> >>> >>> 4,0,0 >>> >>> >>> end data. >>> >>> >>> restore. >>> >>> >>> dataset name original. >>> >>> >>> dataset declare agg. >>> >>> >>> AGGREGATE >>> >>> >>> /OUTFILE=agg >>> >>> >>> /BREAK=breakvar >>> >>> >>> /var1_sum=SUM.1(var1) >>> >>> >>> /var2_sum=SUM(var2) >>> >>> >>> /nbreak=n. >>> >>> >>> dataset activate agg. >>> >>> >>> >>> >>> My example (using the previous data) assumes that the data is not >>> sorted, >>> >>> therefore I have selected "pressorted": >>> >>> >>> >>> >>> DATASET DECLARE agg2. >>> >>> >>> SORT CASES BY breakvar. >>> >>> >>> AGGREGATE >>> >>> >>> /OUTFILE='agg2' >>> >>> >>> /PRESORTED >>> >>> >>> /BREAK=breakvar >>> >>> >>> /var1_sum=SUM(var1) >>> >>> >>> /var2_sum=SUM(var2) >>> >>> >>> /N_BREAK=N. >>> >>> >>> >>> >>> The above syntax won't sum variables with just one respondent. am I >>> still >>> >>> doing something wrong? As I said I solved my problem by just sorting the >>> >>> data before. >>> >>> >>> >>> >>> Thanks in advance, >>> >>> >>> >>> >>> Mils. >>> >>> >>> >>> >>> >>> Date: Mon, 3 Dec 2012 05:09:11 -0800 >>> >>> >>> From: [hidden >>> >>> email] >>> >>> >>> To: [hidden >>> >>> email] >>> >>> >>> Subject: RE: Aggregate command problem with sum function >>> >>> >>> >>> >>> "You aren't going to believe that...." >>> >>> >>> Nope! I am a born skeptic and you provide only anecdotal support >>> >>> for your claim without any sort of replicable context. If your claim >>> >>> were correct there would be a hell of a lot of stink about broken >>> functionality. >>> >>> >>> >>> -- >>> >>> >>> mils wrote >>> >>> >>> Hi Everyone, >>> >>> >>> >>> >>> Thanks for >>> >>> >>> your quick answers. You aren't going to believe that, but (at least for >>> >>> me) sum >>> >>> >>> function won't work properly if you select the option “sort file >>> before >>> >>> aggregatingâ€�. >>> >>> >>> So what I did, was to sort the data first (by the variable use in the >>> aggregate >>> >>> >>> >>> command) and then run the aggregate command. Now it works! >>> >>> >>> >>> >>> Hey Rick, could you confirm that? >>> >>> >>> >>> >>> Thanks, mils >>> >>> >>> >>> >>> >>> >>> Date: Fri, 30 Nov 2012 07:15:24 -0800 >>> >>> >>> From: [hidden >>> >>> email] >>> >>> >>> To: [hidden >>> >>> email] >>> >>> >>> Subject: Re: Aggregate command problem with sum function >>> >>> >>> >>> >>> >>> >>> >>> >>> First, by default Aggregate will sum all >>> >>> >>> the non-missing case values; so sum.1 is essentially redundant since the >>> >>> >>> >>> .n argument specifies the minimum number of non-missing cases, which by >>> >>> >>> >>> default is 1. >>> >>> >>> >>> >>> >>> >>> >>> >>> Second, the sum of one case will only >>> >>> >>> be 1 if the value of the variable for that case is 1. If the value is 0 >>> >>> >>> >>> rather than missing, then the sum will be 0. >>> >>> >>> >>> >>> >>> >>> >>> >>> This example seems to generate the correct >>> >>> >>> results >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> dataset close all. >>> >>> >>> >>> >>> new file. >>> >>> >>> >>> >>> preserve. >>> >>> >>> >>> >>> set mxwarns=0. >>> >>> >>> >>> >>> data list list (",") /breakvar >>> >>> >>> var1 var2. >>> >>> >>> >>> >>> begin data >>> >>> >>> >>> >>> 1,1,1 >>> >>> >>> >>> >>> 1,1, >>> >>> >>> >>> >>> 1,0, >>> >>> >>> >>> >>> 2,1, >>> >>> >>> >>> >>> 2,, >>> >>> >>> >>> >>> 3,,1 >>> >>> >>> >>> >>> 4,0,0 >>> >>> >>> >>> >>> end data. >>> >>> >>> >>> >>> restore. >>> >>> >>> >>> >>> dataset name original. >>> >>> >>> >>> >>> dataset declare agg. >>> >>> >>> >>> >>> AGGREGATE >>> >>> >>> >>> >>> /OUTFILE=agg >>> >>> >>> >>> >>> /BREAK=breakvar >>> >>> >>> >>> >>> /var1_sum=SUM.1(var1) >>> >>> >>> >>> >>> /var2_sum=SUM(var2) >>> >>> >>> >>> >>> /nbreak=n. >>> >>> >>> >>> >>> dataset activate agg. >>> >>> >>> >>> >>> >>> >>> >>> >>> Rick Oliver >>> >>> >>> >>> >>> Senior Information Developer >>> >>> >>> >>> >>> IBM Business Analytics (SPSS) >>> >>> >>> >>> >>> E-mail: [hidden email] >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> From: >>> >>> >>> mils <[hidden email]> >>> >>> >>> >>> >>> To: >>> >>> >>> [hidden email], >>> >>> >>> >>> >>> >>> >>> Date: >>> >>> >>> 11/30/2012 08:25 AM >>> >>> >>> >>> >>> Subject: >>> >>> >>> Aggregate command >>> >>> >>> problem with sum function >>> >>> >>> >>> >>> Sent by: >>> >>> >>> "SPSSX(r) >>> >>> >>> Discussion" <[hidden email]> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> Hi Everyone! >>> >>> >>> >>> >>> >>> >>> >>> >>> I need some help with the aggregate command. Below is the command I'm >>> using: >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> DATASET DECLARE test. >>> >>> >>> >>> >>> SORT CASES BY UserID. >>> >>> >>> >>> >>> AGGREGATE >>> >>> >>> >>> >>> /OUTFILE='test' >>> >>> >>> >>> >>> /PRESORTED >>> >>> >>> >>> >>> /BREAK=UserID >>> >>> >>> >>> >>> /t1f_Enbrel_sum=sum(t1f_Enbrel) >>> >>> >>> >>> >>> /t1f_Humira_sum=sum(t1f_Humira) >>> >>> >>> >>> >>> /t1f_Remicade_sum=sum(t1f_Remicade) >>> >>> >>> >>> >>> /t1f_MabThera_sum=sum(t1f_MabThera) >>> >>> >>> >>> >>> /t1f_Orencia_sum=sum(t1f_Orencia) >>> >>> >>> >>> >>> /t1f_RoActemra_sum=sum(t1f_RoActemra) >>> >>> >>> >>> >>> /t1f_Cimzia_sum=sum(t1f_Cimzia) >>> >>> >>> >>> >>> /t1f_Simponi_sum=sum(t1f_Simponi) >>> >>> >>> >>> >>> /N_BREAK=N. >>> >>> >>> >>> >>> >>> >>> >>> >>> The issue is that for those variables (t1f_) with just one case the sum >>> >>> >>> >>> >>> >>> seems to fail. The aggregate is 0 when it should be 1. I have seen some >>> >>> >>> >>> >>> >>> suggestions online recommending sum.1, but that did not work. >>> >>> >>> >>> >>> >>> >>> >>> >>> Can anyone help me out. I would really appreciated it. >>> >>> >>> >>> >>> >>> >>> >>> >>> Thanks in advance!!! >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> ----- >>> >>> >>> >>> >>> mils >>> >>> >>> >>> >>> -- >>> >>> >>> >>> >>> View this message in context: >>> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538.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 >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> If you reply to >>> >>> this email, your message will be added to the discussion below: >>> >>> >>> >>> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716545.html >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> To unsubscribe >>> >>> from Aggregate command problem with sum function, click here. >>> >>> >>> >>> >>> NAML >>> >>> >>> Please reply to the list and not to my personal >>> >>> email. >>> >>> >>> Those desiring my consulting or training services please feel free to >>> >>> me. >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> If you reply to this email, your message >>> >>> will be added to the discussion below: >>> >>> >>> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716597.html >>> >>> >>> To unsubscribe from Aggregate command problem >>> >>> with sum function, click >>> >>> here. >>> >>> >>> NAML >>> >>> >>> mils >>> >>> >>> >>> >>> >>> View this message in context: RE: >>> >>> Aggregate command problem with sum function >>> >>> >>> Sent from the SPSSX >>> >>> Discussion mailing list archive at Nabble.com. >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> >>> If you reply to this email, your message will be added >>> to the discussion below: >>> >>> >>> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716615.html >>> >>> >>> >>> >>> >>> >>> To unsubscribe from Aggregate command problem with sum >>> function, click here. >>> >>> >>> NAML >>> >>> >>> >>> >>> >>> >>> >>> Please reply to the list and not to my personal email. >>> >>> Those desiring my consulting or training services please feel free to >>> email me. >>> >>> >>> >>> >>> >>> >>> >>> If you reply to this email, your message will be added to the >>> discussion below: >>> >>> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716714.html >>> >>> >>> >>> To unsubscribe from Aggregate command problem with sum function, click >>> here. >>> >>> NAML ----- -- 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/Aggregate-command-problem-with-sum-function-tp5716538p5716720.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 |
Re: Aggregate command problem with sum function
|
|
In reply to this post by Jon K Peck
The Command Syntax Reference is installed
with the product. You can access it from the menus: Help>Command Syntax
Reference. The same information is also provided in the help system. In
a syntax window, just place the cursor on the line containing the command
and press F1. This will take you directly to the section on that command.
Rick Oliver Senior Information Developer IBM Business Analytics (SPSS) E-mail: [hidden email] From: Jon K Peck/Chicago/IBM@IBMUS To: [hidden email], Date: 12/06/2012 09:19 AM Subject: Re: Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> There are direct links to the SPSS Statistics documentation in both help and pdf formats in the Important Bookmarks section of the SPSS Community website at www.ibm.com/developerworks/spssdevcentral. Not hard at all to navigate. Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] new phone: 720-342-5621 From: David Marso <[hidden email]> To: [hidden email], Date: 12/06/2012 07:49 AM Subject: Re: [SPSSX-L] Aggregate command problem with sum function Sent by: "SPSSX(r) Discussion" <[hidden email]> You can also download the pdf from the insufferable labyrinth known as the IBM website ;-( There is some root for Documentation and then product. I posted a link the other day so you can also search on my recent posts and you should find the link. -- Bruce Weaver wrote > To open the FM, click on Help > Command Syntax Reference. > > mils wrote >> it should be "make". Where can I find the " FM to study the various >> nuances of ADDVARIABLES and PRESORTED"? Date: Thu, 6 Dec 2012 04:33:33 >> -0800 >> From: >> ml-node+s1045642n5716714h97@.nabble >> To: >> jcasellasvega@.CO >> Subject: RE: Aggregate command problem with sum function >> >> >> >> doctor? >> >> make? >> >> You should straighten that discrepancy and list whatever BREAKS along >> with the SUMs otherwise it is very confusing because the data are not in >> the original order and frankly I am not going to bother trying to line it >> up. >> >> Also: Have you looked at the FM to study the various nuances of >> ADDVARIABLES and PRESORTED? >> >> >> >> mils wrote >> Hi Rick, >> >> >> Thanks for your answer. As I guessed from the beginning I >> >> was doing something wrong. Sorry, I made the assumption that the >> aggregate >> >> commend wasn’t working properly (my mistake). Below is a mock example of >> what I >> >> was doing, so you can understand why I thought there was something wrong >> with the >> >> command (hope it helps): >> >> >> >> >> DATA LIST LIST >> >> >> / doctor (A15) prod1 >> >> prod2 prod3 prod4. >> >> >> BEGIN DATA. >> >> >> "AMC" 1 0 1 0 >> >> >> "AMC" 1 5 7 0 >> >> >> "AMC" 1 0 8 9 >> >> >> "AMC" 1 5 1 7 >> >> >> "AMB" 0 9 6 1 >> >> >> "AMB" 0 2 3 1 >> >> >> "AMB" 0 2 9 1 >> >> >> "AMB" 0 2 6 4 >> >> >> "AMB" 0 3 3 1 >> >> >> "ACS" 1 0 3 0 >> >> >> "ACS" 1 1 1 1 >> >> >> "ACS" 1 0 0 0 >> >> >> "AMM" 0 5 4 1 >> >> >> END DATA. >> >> >> fre prod1 prod2 prod3 prod4. >> >> >> >> >> AGGREGATE >> >> >> /OUTFILE=* >> >> MODE=ADDVARIABLES >> >> >> /BREAK=make >> >> >> >> /prod1_sum=SUM(prod1) >> >> >> >> >> /prod2_sum=SUM(prod2) >> >> >> /prod3_sum=SUM(prod3) >> >> >> >> >> >> /prod4_sum=SUM(prod4). >> >> >> >> >> >> ****output******. >> >> >> >> >> >> prod1_sum prod2_sum prod3_sum prod4_sum >> >> >> 3.00 1.00 4.00 1.00 >> >> >> 3.00 1.00 4.00 1.00 >> >> >> 3.00 1.00 4.00 1.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> .00 5.00 4.00 1.00 >> >> >> >> >> >> Using the above “aggregate” results are as expected. Everything is >> >> fine. However using the below aggregate command: >> >> >> >> >> >> SORT CASES BY make. >> >> >> AGGREGATE >> >> >> /OUTFILE=* >> >> MODE=ADDVARIABLES >> >> >> /PRESORTED >> >> >> /BREAK=make >> >> >> >> >> /prod1_sum_2=SUM(prod1) >> >> >> >> >> /prod2_sum_2=SUM(prod2) >> >> >> >> >> /prod3_sum_2=SUM(prod3) >> >> >> >> >> /prod4_sum_2=SUM(prod4). >> >> >> >> >> >> ****output******. >> >> >> >> >> >> prod1_sum_2 prod2_sum_2 prod3_sum_2 prod4_sum_2 >> >> >> 3.00 .00 4.00 .00 >> >> >> 3.00 .00 4.00 .00 >> >> >> .00 .00 4.00 .00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> .00 18.00 27.00 8.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> 4.00 10.00 17.00 16.00 >> >> >> .00 5.00 4.00 .00 >> >> >> Here results are not as I was expecting. There are products where >> >> the sum now is 0, when they should be 1. I was using >> >> the “Presorted” command when I shouldn’t (and I guess that's why the >> results are different). So, what I thought it was an SPSS error >> >> it was actually my mistake in doing a wrong assumption of the data. >> >> >> Thanks a lot for your help! >> >> >> mils >> >> >> >> Date: Mon, 3 Dec 2012 10:06:59 -0800 >> >> From: [hidden email] >> To: [hidden email] >> Subject: Re: Aggregate command problem with sum function >> >> >> >> >> Aggregate does not require sorted data. >> >> PRESORTED should only be used when the data are already sorted and even >> >> then is only useful for very large data files. >> >> >> >> >> If the file is not sorted and you specify >> >> PRESORTED, you should see this warning: >> >> >> >> >> Warning # 10950 >> >> >> The working file read by AGGREGATE >> >> was not in ascending sequence on the break >> >> >> variables. >> >> >> >> >> >> >> >> Rick Oliver >> >> >> Senior Information Developer >> >> >> IBM Business Analytics (SPSS) >> >> >> E-mail: [hidden email] >> >> >> >> >> >> >> >> >> From: >> >> Rick Oliver/Chicago/IBM >> >> >> To: >> >> mils <[hidden email]>, >> >> >> >> Cc: >> >> [hidden email] >> >> >> Date: >> >> 12/03/2012 11:45 AM >> >> >> Subject: >> >> Re: Aggregate >> >> command problem with sum function >> >> >> >> >> >> >> >> Interesting. Presorted means the exact >> >> opposite of what you think, but since the data are in fact already sorted >> >> by breakvar, PRESORTED shouldn't make any difference. >> >> >> >> >> Rick Oliver >> >> >> Senior Information Developer >> >> >> IBM Business Analytics (SPSS) >> >> >> E-mail: [hidden email] >> >> >> >> >> >> >> >> >> >> >> From: >> >> mils <[hidden email]> >> >> >> To: >> >> [hidden email], >> >> >> >> Date: >> >> 12/03/2012 11:39 AM >> >> >> Subject: >> >> Re: Aggregate >> >> command problem with sum function >> >> >> Sent by: >> >> "SPSSX(r) >> >> Discussion" <[hidden email]> >> >> >> >> >> >> >> >> >> >> >> >> Sorry! You are right, I need some kind of a proof. Please find below what >> >> I'm trying to demonstrate. >> >> >> >> >> The following syntax (which I've just copied and pasted from Rick) works >> >> perfectly. >> >> >> >> >> dataset close all. >> >> >> new file. >> >> >> preserve. >> >> >> set mxwarns=0. >> >> >> data list list (",") /breakvar var1 var2. >> >> >> begin data >> >> >> 1,1,1 >> >> >> 1,1, >> >> >> 1,0, >> >> >> 2,1, >> >> >> 2,, >> >> >> 3,,1 >> >> >> 4,0,0 >> >> >> end data. >> >> >> restore. >> >> >> dataset name original. >> >> >> dataset declare agg. >> >> >> AGGREGATE >> >> >> /OUTFILE=agg >> >> >> /BREAK=breakvar >> >> >> /var1_sum=SUM.1(var1) >> >> >> /var2_sum=SUM(var2) >> >> >> /nbreak=n. >> >> >> dataset activate agg. >> >> >> >> >> My example (using the previous data) assumes that the data is not sorted, >> >> therefore I have selected "pressorted": >> >> >> >> >> DATASET DECLARE agg2. >> >> >> SORT CASES BY breakvar. >> >> >> AGGREGATE >> >> >> /OUTFILE='agg2' >> >> >> /PRESORTED >> >> >> /BREAK=breakvar >> >> >> /var1_sum=SUM(var1) >> >> >> /var2_sum=SUM(var2) >> >> >> /N_BREAK=N. >> >> >> >> >> The above syntax won't sum variables with just one respondent. am I still >> >> doing something wrong? As I said I solved my problem by just sorting the >> >> data before. >> >> >> >> >> Thanks in advance, >> >> >> >> >> Mils. >> >> >> >> >> >> Date: Mon, 3 Dec 2012 05:09:11 -0800 >> >> >> From: [hidden >> >> email] >> >> >> To: [hidden >> >> email] >> >> >> Subject: RE: Aggregate command problem with sum function >> >> >> >> >> "You aren't going to believe that...." >> >> >> Nope! I am a born skeptic and you provide only anecdotal support >> >> for your claim without any sort of replicable context. If your claim >> >> were correct there would be a hell of a lot of stink about broken >> functionality. >> >> >> >> -- >> >> >> mils wrote >> >> >> Hi Everyone, >> >> >> >> >> Thanks for >> >> >> your quick answers. You aren't going to believe that, but (at least for >> >> me) sum >> >> >> function won't work properly if you select the option “sort file before >> >> aggregatingâ€�. >> >> >> So what I did, was to sort the data first (by the variable use in the >> aggregate >> >> >> >> command) and then run the aggregate command. Now it works! >> >> >> >> >> Hey Rick, could you confirm that? >> >> >> >> >> Thanks, mils >> >> >> >> >> >> >> Date: Fri, 30 Nov 2012 07:15:24 -0800 >> >> >> From: [hidden >> >> email] >> >> >> To: [hidden >> >> email] >> >> >> Subject: Re: Aggregate command problem with sum function >> >> >> >> >> >> >> >> >> First, by default Aggregate will sum all >> >> >> the non-missing case values; so sum.1 is essentially redundant since the >> >> >> >> .n argument specifies the minimum number of non-missing cases, which by >> >> >> >> default is 1. >> >> >> >> >> >> >> >> >> Second, the sum of one case will only >> >> >> be 1 if the value of the variable for that case is 1. If the value is 0 >> >> >> >> rather than missing, then the sum will be 0. >> >> >> >> >> >> >> >> >> This example seems to generate the correct >> >> >> results >> >> >> >> >> >> >> >> >> >> >> >> >> dataset close all. >> >> >> >> >> new file. >> >> >> >> >> preserve. >> >> >> >> >> set mxwarns=0. >> >> >> >> >> data list list (",") /breakvar >> >> >> var1 var2. >> >> >> >> >> begin data >> >> >> >> >> 1,1,1 >> >> >> >> >> 1,1, >> >> >> >> >> 1,0, >> >> >> >> >> 2,1, >> >> >> >> >> 2,, >> >> >> >> >> 3,,1 >> >> >> >> >> 4,0,0 >> >> >> >> >> end data. >> >> >> >> >> restore. >> >> >> >> >> dataset name original. >> >> >> >> >> dataset declare agg. >> >> >> >> >> AGGREGATE >> >> >> >> >> /OUTFILE=agg >> >> >> >> >> /BREAK=breakvar >> >> >> >> >> /var1_sum=SUM.1(var1) >> >> >> >> >> /var2_sum=SUM(var2) >> >> >> >> >> /nbreak=n. >> >> >> >> >> dataset activate agg. >> >> >> >> >> >> >> >> >> Rick Oliver >> >> >> >> >> Senior Information Developer >> >> >> >> >> IBM Business Analytics (SPSS) >> >> >> >> >> E-mail: [hidden email] >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> From: >> >> >> mils <[hidden email]> >> >> >> >> >> To: >> >> >> [hidden email], >> >> >> >> >> >> >> Date: >> >> >> 11/30/2012 08:25 AM >> >> >> >> >> Subject: >> >> >> Aggregate command >> >> >> problem with sum function >> >> >> >> >> Sent by: >> >> >> "SPSSX(r) >> >> >> Discussion" <[hidden email]> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> Hi Everyone! >> >> >> >> >> >> >> >> >> I need some help with the aggregate command. Below is the command I'm >> using: >> >> >> >> >> >> >> >> >> >> DATASET DECLARE test. >> >> >> >> >> SORT CASES BY UserID. >> >> >> >> >> AGGREGATE >> >> >> >> >> /OUTFILE='test' >> >> >> >> >> /PRESORTED >> >> >> >> >> /BREAK=UserID >> >> >> >> >> /t1f_Enbrel_sum=sum(t1f_Enbrel) >> >> >> >> >> /t1f_Humira_sum=sum(t1f_Humira) >> >> >> >> >> /t1f_Remicade_sum=sum(t1f_Remicade) >> >> >> >> >> /t1f_MabThera_sum=sum(t1f_MabThera) >> >> >> >> >> /t1f_Orencia_sum=sum(t1f_Orencia) >> >> >> >> >> /t1f_RoActemra_sum=sum(t1f_RoActemra) >> >> >> >> >> /t1f_Cimzia_sum=sum(t1f_Cimzia) >> >> >> >> >> /t1f_Simponi_sum=sum(t1f_Simponi) >> >> >> >> >> /N_BREAK=N. >> >> >> >> >> >> >> >> >> The issue is that for those variables (t1f_) with just one case the sum >> >> >> >> >> >> seems to fail. The aggregate is 0 when it should be 1. I have seen some >> >> >> >> >> >> suggestions online recommending sum.1, but that did not work. >> >> >> >> >> >> >> >> >> Can anyone help me out. I would really appreciated it. >> >> >> >> >> >> >> >> >> Thanks in advance!!! >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> ----- >> >> >> >> >> mils >> >> >> >> >> -- >> >> >> >> >> View this message in context: >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538.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 >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> If you reply to >> >> this email, your message will be added to the discussion below: >> >> >> >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716545.html >> >> >> >> >> >> >> >> >> >> >> To unsubscribe >> >> from Aggregate command problem with sum function, click here. >> >> >> >> >> NAML >> >> >> Please reply to the list and not to my personal >> >> email. >> >> >> Those desiring my consulting or training services please feel free to >> >> me. >> >> >> >> >> >> >> >> >> >> If you reply to this email, your message >> >> will be added to the discussion below: >> >> >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716597.html >> >> >> To unsubscribe from Aggregate command problem >> >> with sum function, click >> >> here. >> >> >> NAML >> >> >> mils >> >> >> >> >> >> View this message in context: RE: >> >> Aggregate command problem with sum function >> >> >> Sent from the SPSSX >> >> Discussion mailing list archive at Nabble.com. >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> If you reply to this email, your message will be added to >> the discussion below: >> >> >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716615.html >> >> >> >> >> >> >> To unsubscribe from Aggregate command problem with sum >> function, click here. >> >> >> NAML >> >> >> >> >> >> >> >> Please reply to the list and not to my personal email. >> >> Those desiring my consulting or training services please feel free to >> email me. >> >> >> >> >> >> >> >> If you reply to this email, your message will be added to the >> discussion below: >> >> http://spssx-discussion.1045642.n5.nabble.com/Aggregate-command-problem-with-sum-function-tp5716538p5716714.html >> >> >> >> To unsubscribe from Aggregate command problem with sum function, click >> here. >> >> NAML ----- 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/Aggregate-command-problem-with-sum-function-tp5716538p5716718.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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |