Count number of appearances and computing a variable with the result
Locked
 
|
Hi,
I have the following data brands Rank a 1 b 3 c 2 a 1 b 2 c 3 a 3 b 2 c 1 a 3 b 1 c 2 and I would like to count the number of times "brand a" was rank 1, 2, 3; and do the same for "brand b" and "brand c". Something like this: rank_1 rank_2 rank_3 a 2 0 2 b 1 2 1 c 1 2 1 I have SPSS version 22. Thanks in advance!
mils
|
|
|
Now that is a nice simple question! Here is one way I would go about, first aggregate the counts to brand+rank pairs, and then reshape to the wide format. Example below.
********************************. DATA LIST FREE / brand (A1) Rank (F1.0). BEGIN DATA a 1 b 3 c 2 a 1 b 2 c 3 a 3 b 2 c 1 a 3 b 1 c 2 END DATA DATASET NAME Orig. *Aggregate to brank+rank pairs. DATASET DECLARE AggRanks. AGGREGATE OUTFILE='AggRanks' /BREAK brand Rank /Total = N. DATASET ACTIVATE AggRanks. *Reshape long to wide. CASESTOVARS /ID = brand /INDEX = Rank /RENAME Total = rank /SEPARATOR = "_". *Recode 0 to system missing. RECODE rank_1 TO rank_3 (SYSMIS = 0). ********************************. |
Re: Count number of appearances and computing a variable with the result
Administrator
|
Even simpler is to use the CIN function on AGGREGATE as Melissa Ives pointed out yesterday after I suggested PIN as mils recent question was about percentages.
AGGREGATE OUTFILE * /BREAK brand /Rank1=CIN(rank,1,1) / Rank2=CIN(rank,2,2) / Rank3=CIN(rank,3,3) . To mils (OP) BTW! any reason for starting a NEW thread for this? You may have taken the time to read up on AGGREGATE as previously suggested! You are not a green newbie. Make use of the documented resources after they have been pointed out to you.
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?" |
|
|
Hi David,
I did look at the documentation you pointed out, but as much as I have been working with SPSS for a long time now, I'm still a green newbie with the aggregated command, and to be honest, I can't get my head around with the examples provided in the documentation. My ultimate goal is to understand and replicate with SPSS an example of MaxDiff I found online (http://surveyanalysis.org/wiki/Max-Diff_Analysis_Case_Study_Using_R), that uses R. Since I can't ask anyone to replicate the entire R code into SPSS for me, I've been posting different questions that refer to specific steps of that example. I was hoping to put together a final script/syntax that will replicate that example using SPSS. Sorry for the "similar" postings throughout the week... But I'm a bit desperate now, and I need to understand this simple MaxDiff example and be able to replicate it in SPSS in order to use it at work with a more complicate real data. However, I won't throw in the towel yet! I will come back with more questions, probably next week.... PD: If anyone is still interested in helping me out, this is the syntax I have so far: comp Apple=mean(maxDiff1alt1,maxDiff2alt1,maxDiff3alt1,maxDiff4alt1,maxDiff5alt1,maxDiff6alt1). comp Microsoft=mean(maxDiff1alt2,maxDiff2alt2,maxDiff3alt2,maxDiff4alt2,maxDiff5alt2,maxDiff6alt2). comp IBM=mean(maxDiff1alt3,maxDiff2alt3,maxDiff3alt3,maxDiff4alt3,maxDiff5alt3,maxDiff6alt3). comp Google=mean(maxDiff1alt4,maxDiff2alt4,maxDiff3alt4,maxDiff4alt4,maxDiff5alt4,maxDiff6alt4). comp Intel=mean(maxDiff1alt5,maxDiff2alt5,maxDiff3alt5,maxDiff4alt5,maxDiff5alt5,maxDiff6alt5). comp HewlettPackard=mean(maxDiff1alt6,maxDiff2alt6,maxDiff3alt6,maxDiff4alt6,maxDiff5alt6,maxDiff6alt6). comp Sony=mean(maxDiff1alt7,maxDiff2alt7,maxDiff3alt7,maxDiff4alt7,maxDiff5alt7,maxDiff6alt7). comp Dell=mean(maxDiff1alt8,maxDiff2alt8,maxDiff3alt8,maxDiff4alt8,maxDiff5alt8,maxDiff6alt8). comp Yahoo=mean(maxDiff1alt9,maxDiff2alt9,maxDiff3alt9,maxDiff4alt9,maxDiff5alt9,maxDiff6alt9). comp Nokia=mean(maxDiff1alt10,maxDiff2alt10,maxDiff3alt10,maxDiff4alt10,maxDiff5alt10,maxDiff6alt10). desc Apple to Nokia. DATASET COPY Unroll. DATASET ACTIVATE Unroll WINDOW=FRONT. VARSTOCASES /MAKE Score FROM Apple to Nokia /INDEX = MsgName(Score) /NULL = DROP. DATASET DECLARE Summary. AGGREGATE OUTFILE=Summary /BREAK=MsgName /Mean 'Mean score' = MEAN(Score). DATASET ACTIVATE Summary WINDOW=FRONT. SORT CASES BY Mean (D). FORMATS Mean (F7.4). DATASET DECLARE Summary. NUMERIC Rank (F3). COMPUTE Rank = $CASENUM. LIST. DATASET ACTIVATE Unroll. SORT CASES BY USERID(A) Score (D). NUMERIC Rank (F3). DO IF $CASENUM EQ 1. . COMPUTE Rank = 1. ELSE IF USERID NE LAG(USERID). . COMPUTE Rank = 1. ELSE. . COMPUTE Rank = LAG(Rank) + 1. END IF. SORT CASES BY USERID(A) MsgName (A). exe. AGGREGATE OUTFILE * /BREAK MsgName / Rank1=Pin(rank,1,1) / Rank2=Pin(rank,2,2) / Rank3=Pin(rank,3,3) / Rank4=Pin(rank,4,4) / Rank5=Pin(rank,5,5) / Rank6=Pin(rank,6,6) / Rank7=Pin(rank,7,7) / Rank8=Pin(rank,8,8) / Rank9=Pin(rank,9,9) / Rank10=Pin(rank,10,10).
mils
|
Re: Count number of appearances and computing a variable with the result
|
|
In reply to this post by mils
I think I'd do an aggregate, breaking on brands and rank, and counting occurrences. That will give you a long format file but you want a wide format file, so I'd next do a casestovars.
Gene Maguin -----Original Message----- From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of mils Sent: Friday, April 24, 2015 7:43 AM To: [hidden email] Subject: Count number of appearances and computing a variable with the result Hi, I have the following data brands Rank a 1 b 3 c 2 a 1 b 2 c 3 a 3 b 2 c 1 a 3 b 1 c 2 and I would like to count the number of times "brand a" was rank 1, 2, 3; and do the same for "brand b" and "brand c". Something like this: rank_1 rank_2 rank_3 a 2 0 2 b 1 2 1 c 1 2 1 I have SPSS version 22. Thanks in advance! ----- mils -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Count-number-of-appearances-and-computing-a-variable-with-the-result-tp5729348.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 |
Re: Count number of appearances and computing a variable with the result
|
Administrator
|
But as illustrated by my posting it can be done with a single AGGREGATE command using NIN functions for each category (rating value).  On Fri, Apr 24, 2015 at 1:56 PM, Maguin, Eugene [via SPSSX Discussion] <[hidden email]> wrote: I think I'd do an aggregate, breaking on brands and rank, and counting  occurrences. That will give you a long format file but you want a wide format file, so I'd next do a casestovars.
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: Count number of appearances and computing a variable with the result
|
|
I had the syntax ref open and I don’t see a NIN function for aggregate. Might you mean CIN?
That would be one step to the desired result.
Gene Maguin From: SPSSX(r) Discussion [mailto:[hidden email]]
On Behalf Of David Marso But as illustrated by my posting it can be done with a single AGGREGATE command using NIN functions for each category (rating value). On Fri, Apr 24, 2015 at 1:56 PM, Maguin, Eugene [via SPSSX Discussion] <[hidden email]> wrote:
Please reply to the list and not to my personal email.
View this message in context:
Re: Count number of appearances and computing a variable with the result |
Re: Count number of appearances and computing a variable with the result
|
Administrator
|
OOPS my bad (Friday?).
I meant CIN and that will indeed yield a 1 liner. See my previous post. ----
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: Count number of appearances and computing a variable with the result
|
In reply to this post by David Marso
At 01:54 PM 4/24/2015, Maguin, Eugene wrote:
>I think I'd do an aggregate, breaking on brands and rank, and >counting occurrences. That will give you a long format file but you >want a wide format file, so I'd next do a casestovars. At 02:39 PM 4/24/2015, David Marso replied: >But as illustrated by my posting it can be done with a single >AGGREGATE command using CIN functions for each category (rating >value), i.e. [reformatted] > >AGGREGATE OUTFILE * > /BREAK brand > /Rank1=CIN(rank,1,1) > /Rank2=CIN(rank,2,2) > /Rank3=CIN(rank,3,3) . That's an interesting question in SPSS aesthetics. Since CASESTOVARS can be slow, David's single-AGGREGATE solution probably runs faster; though, since the file to be CASESTOVARed will have only 10 records for each of (I presume) less than 100 brands, the slowdown should be negligible. I'd probably go with Gene's approach, myself. I like to take full advantage of how naturally SPSS handles long-form files; and I don't like writing code with a lot of repetitions (i.e., ten "/RankN = CIN(rank,N,N)" clauses). But, there, that's aesthetics. However, AGGREGATE followed by CASESTOVARS will give you an indication if an unexpected value for "Rank" does occur; the multiple "CIN" clauses will give no warning if such a value exists. ===================== 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: Count number of appearances and computing a variable with the result
|
Administrator
|
WE can go ultra retro old skool and do what we did before CASESTOVARS existed.
VECTOR_Plop_Agg !!! Probably most of the youngsters around here don't even recall those fun times ;-) /* Deliberately uncommented */. /* Let them RTFM!!!!! */. DATA LIST FREE / brand (A1) Rank (F1.0). BEGIN DATA a 1 b 3 c 2 a 1 b 2 c 3 a 3 b 2 c 1 a 3 b 1 c 2 END DATA . DEFINE !Agg_Count (!POS !TOKENS(1)/ !POS !TOKENS(1) / !POS !TOKENS(1) / !POS !TOKENS(1) ) !LET !VarList = !CONCAT(!1,"_",!2," TO ",!1,"_",!3) NUMERIC !VarList (F1.0). RECODE !VarList (ELSE=0). VECTOR !1=!VarList . COMPUTE !1(!1)=1. AGGREGATE OUTFILE * /BREAK !4 / !VarList =SUM(!VarList ). !ENDDEFINE. !Agg_Count Rank 1 10 brand .
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: Count number of appearances and computing a variable with the result
Administrator
|
David, I'm afraid someone could experience some serious brain-strain trying to figure that out. Here's a modified version that might make a bit easier to see what your code is doing. Once people have understood it, then they can revert to the original. ;-)
DEFINE !Agg_Count (!POS !TOKENS(1)/ !POS !TOKENS(1) / !POS !TOKENS(1) / !POS !TOKENS(1) ) !LET !VarList = !CONCAT(!1,"_",!2," TO ",!1,"_",!3) NUMERIC !VarList (F1.0). RECODE !VarList (ELSE=0). VECTOR !1=!VarList . COMPUTE !1(!1)=1. /* AGGREGATE OUTFILE * /BREAK !4 / !VarList =SUM(!VarList ). */ !ENDDEFINE. DATA LIST FREE / brand (A1) Rank (F1.0). BEGIN DATA a 1 b 3 c 2 a 1 b 2 c 3 a 3 b 2 c 1 a 3 b 1 c 2 END DATA . LIST. FREQUENCIES brand. SET MPRINT = ON. !Agg_Count Rank 1 10 brand . SET MPRINT = OFF. LIST. * David's AGGREGATE moved outside of the macro to * make it a little more obvious what it is doing. AGGREGATE OUTFILE * /BREAK brand / Rank_1 to Rank_10 =SUM(Rank_1 to Rank_10). COMPUTE Rank_sum = SUM(Rank_1 to Rank_10). FORMATS Rank_1 to Rank_sum (F2.0). LIST.
--
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: Count number of appearances and computing a variable with the result
|
Administrator
|
In my opinion it is really rather obvious ;-)
What is so brain-strain about !LET and !CONCAT? If people are too lazy to read up on macros then they don't need to bother with it! --
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: Count number of appearances and computing a variable with the result
|
|
In reply to this post by mils
Leaving aside the SPSS syntax, MaxDiff
as an analysis method has a mixed reputation as some believe that its underlying
assumptions are quite unrealistic. Last time I checked, R also did
not have a package for MaxDiff. So don't let the syntax struggles
keep you from taking a hard look at the assumptions.
Jon Peck (no "h") aka Kim Senior Software Engineer, IBM [hidden email] phone: 720-342-5621 From: mils <[hidden email]> To: [hidden email] Date: 04/24/2015 05:47 PM Subject: Re: [SPSSX-L] Count number of appearances and computing a variable with the result Sent by: "SPSSX(r) Discussion" <[hidden email]> Hi David, I did look at the documentation you pointed out, but as much as I have been working with SPSS for a long time now, I'm still a green newbie with the aggregated command, and to be honest, I can't get my head around with the examples provided in the documentation. My ultimate goal is to understand and replicate with SPSS an example of MaxDiff I found online (http://surveyanalysis.org/wiki/Max-Diff_Analysis_Case_Study_Using_R), that uses R. Since I can't ask anyone to replicate the entire R code into SPSS for me, I've been posting different questions that refer to specific steps of that example. I was hoping to put together a final script/syntax that will replicate that example using SPSS. Sorry for the "similar" postings throughout the week... But I'm a bit desperate now, and I need to understand this simple MaxDiff example and be able to replicate it in SPSS in order to use it at work with a more complicate real data. However, I won't throw in the towel yet! I will come back with more questions, probably next week.... PD: If anyone is still interested in helping me out, this is the syntax I have so far: comp Apple=mean(maxDiff1alt1,maxDiff2alt1,maxDiff3alt1,maxDiff4alt1,maxDiff5alt1,maxDiff6alt1). comp Microsoft=mean(maxDiff1alt2,maxDiff2alt2,maxDiff3alt2,maxDiff4alt2,maxDiff5alt2,maxDiff6alt2). comp IBM=mean(maxDiff1alt3,maxDiff2alt3,maxDiff3alt3,maxDiff4alt3,maxDiff5alt3,maxDiff6alt3). comp Google=mean(maxDiff1alt4,maxDiff2alt4,maxDiff3alt4,maxDiff4alt4,maxDiff5alt4,maxDiff6alt4). comp Intel=mean(maxDiff1alt5,maxDiff2alt5,maxDiff3alt5,maxDiff4alt5,maxDiff5alt5,maxDiff6alt5). comp HewlettPackard=mean(maxDiff1alt6,maxDiff2alt6,maxDiff3alt6,maxDiff4alt6,maxDiff5alt6,maxDiff6alt6). comp Sony=mean(maxDiff1alt7,maxDiff2alt7,maxDiff3alt7,maxDiff4alt7,maxDiff5alt7,maxDiff6alt7). comp Dell=mean(maxDiff1alt8,maxDiff2alt8,maxDiff3alt8,maxDiff4alt8,maxDiff5alt8,maxDiff6alt8). comp Yahoo=mean(maxDiff1alt9,maxDiff2alt9,maxDiff3alt9,maxDiff4alt9,maxDiff5alt9,maxDiff6alt9). comp Nokia=mean(maxDiff1alt10,maxDiff2alt10,maxDiff3alt10,maxDiff4alt10,maxDiff5alt10,maxDiff6alt10). desc Apple to Nokia. DATASET COPY Unroll. DATASET ACTIVATE Unroll WINDOW=FRONT. VARSTOCASES /MAKE Score FROM Apple to Nokia /INDEX = MsgName(Score) /NULL = DROP. DATASET DECLARE Summary. AGGREGATE OUTFILE=Summary /BREAK=MsgName /Mean 'Mean score' = MEAN(Score). DATASET ACTIVATE Summary WINDOW=FRONT. SORT CASES BY Mean (D). FORMATS Mean (F7.4). DATASET DECLARE Summary. NUMERIC Rank (F3). COMPUTE Rank = $CASENUM. LIST. DATASET ACTIVATE Unroll. SORT CASES BY USERID(A) Score (D). NUMERIC Rank (F3). DO IF $CASENUM EQ 1. . COMPUTE Rank = 1. ELSE IF USERID NE LAG(USERID). . COMPUTE Rank = 1. ELSE. . COMPUTE Rank = LAG(Rank) + 1. END IF. SORT CASES BY USERID(A) MsgName (A). exe. AGGREGATE OUTFILE * /BREAK MsgName / Rank1=Pin(rank,1,1) / Rank2=Pin(rank,2,2) / Rank3=Pin(rank,3,3) / Rank4=Pin(rank,4,4) / Rank5=Pin(rank,5,5) / Rank6=Pin(rank,6,6) / Rank7=Pin(rank,7,7) / Rank8=Pin(rank,8,8) / Rank9=Pin(rank,9,9) / Rank10=Pin(rank,10,10). ----- mils -- View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Count-number-of-appearances-and-computing-a-variable-with-the-result-tp5729348p5729355.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 |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |