## How to compute a global score (in %) excluding missing value

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

JazzFinder
Posts: 4
Joined: Wed Jul 09, 2014 1:23 pm

### How to compute a global score (in %) excluding missing value

Please bare with me. I'm not a statistician and I'm doing my best in English (obviously it is not my native language.

I'm having a simple problem (for you) but a huge mountain for a musician like me (one of my hobbies between gigs and being on the road is statistics... Yeah, crazy, I know...) It reminds me of the good ol' time when I was a college student.

I have a data set resulting from a simple questionnaire to an audience for a giving bar. It was intended mainly for measuring their actual satisfaction and future requests/suggestions. Most of the satisfaction questions have a simple 4-point scale (1 to 4 - 'Strongly disagree' thru 'Strongly agree'). Usually, positions 3-4 are positive responses (but sometimes the questions are reversed, so 1-2 become the positive responses).

I recoded these questions to 1 (ex-1-2) or 2 (ex-3-4), with 5 being missing values (I can recode them to 0 or to any givibg values if needed).

So here is the fun:
I'm not interested in analysing each satisfaction variable by itself. Questions have a given logic that I developed to suit specific needs. For example, variables (questions) V01, V02, V03 and V04 treat all one aspect of the same subject: quality of service at the music restaurant/bar. Questions V05 to V10 are about quality of sound, etc.

My objective:
I need to compute a combined total score (% satisfaction), for example of V01 to V04, where:
V01 and V02 are positive at value 2
V03 and V04 are positive at value 1

Satisfaction Service (%)= SUM (V01(2) V02(2) V03(1) V04(1)
DIVIDED BY The total number of answers recieved (excluding missing values)

I don't know if I made myself clear. I've tried COUNT but it creates a new variable (with a score for each row). I would like to obtain a value that I can store 'somewhere' for later statistical analyses (to analyse for instance differences in perceptions according to different categories of age, sex, experience with bar, etc.).

Please keep you replies simple.

Thank you in advance.
GerineL
Moderator
Posts: 1477
Joined: Tue Jun 10, 2008 4:50 pm

### Re: How to compute a global score (in %) excluding missing v

There is a few ways you can go about this.

First of all, I wonder why you would recode it to 1/2 instead of using all the scores.
Basically, more variability in your data is good, it gives a more nuanced picture.

If you were to keep all data, you could think about doing this:

1. Recode variables.

Recode your variables so they are all in the same direction.
You can do that using syntax.

Below is an example in which you recode V3 and V4 so that the higher responses refer to higher quality.
recode V3 V4
(1 = 4) (2 = 3) (3 = 2) (4 = 1)
into V3_recode V4_recode
execute.
this creates 2 new variables: V3_recode and V4_recode, that you can then use.
(of course, you can also do this with the 1/2 variables, by recoding (1=2) (2=1) for the appropriate variables).

It is normal procedure to create new variables and use those in other analyses.

You can sum them up like this:
compute V_sum = sum(v1, v2, v3_recode, v4_recode).
execute.
or compute a mean:
compute V_mean = mean(v1, v2, v3_recode, v4_recode).
execute.

Now, can you please indicate what you want to do with this percentage variable? I.e., what the percentage should be about? (... percent of ...)?
RubenGeert
Posts: 100
Joined: Mon May 19, 2014 6:06 am

### Re: How to compute a global score (in %) excluding missing v

There's a nice tool for reverse coding variables that corrects the value labels automatically. You do need to have the Python Essentials installed but then you can run it from the menu. See http://www.spss-tutorials.com/reverse-c ... ue-labels/.

Kind regards,

Ruben Geert van den Berg
www.spss-tutorials.com
JazzFinder
Posts: 4
Joined: Wed Jul 09, 2014 1:23 pm

### Re: How to compute a global score (in %) excluding missing v

Thank you GerineL and RubenGeert for your help. And Sorry for the delay, I was a bit overwhelmed with work.

So the decoding is done, it worked like a charm. Thank you once again. I knew how to do it the hard way, variable by variable, but I was unaware one could perform it bulk.

1. First, about recoding and the reducing the variables (reducing the variability):
You are right. I use both variables, the original scale and the reduced one, for different statistics and analyses.

2. Regarding the percentage variable:
Like I said in my original post, consider it as a performance variable, for example a satisfaction index, a positive perception index, etc.

If V01 to V04 represent satisfaction with different aspects of music and sound quality, then the overall percentage of satisfaction towards sound quality would be:

The percent of satisfied people (code 2 of V01 to V04 for all respondents) BY the number of total respondents (codes 1,2) (excluding missing values (5))

I've used this measure for years with no problem. I just would like to find a way to do it automatically - rather than manually- since my samples keep becoming larger and larger.

Thus, I'm not interested in summing a combined variable, but rather summing occurrences (individuals) i.e. counting the number of people that answered 2 among all people who answered 1 or 2.

The next step for me would be comparing different categories of respondents (by age, gender, etc.) For example:

- Smusic (which is the combined satisfaction index of V01 to V04) for the whole sample (let's say = 85%)

I would like to represent data graphically (and use statistic comparisons if necessary) between:

- Overall: 85%
- Males: 90%
- Females: 83%

or,
- Overall: 85%
- <30 y/o: 95
- 30-45 y/o: 90%
- 46-65 y/o: 83%
- >65 y/o: 79%

Thank you once again.
RubenGeert
Posts: 100
Joined: Mon May 19, 2014 6:06 am

### Re: How to compute a global score (in %) excluding missing v

First, why don't you use 1 for "satisfied" and 0 for "not satisfied"? Like so, it becomes very easy to get proportions or (with a tiny modification) percentages in tables. For a nice example, see http://www.spss-tutorials.com/temporary/

You can also use MEAN for computing the within-person proportion of satisfied answers using his valid answers as base.
Thus, I'm not interested in summing a combined variable, but rather summing occurrences (individuals) i.e. counting the number of people that answered 2 among all people who answered 1 or 2.
The next step for me would be comparing different categories of respondents (by age, gender, etc.) For example:
This sounds kinda confused. "counting people" suggests you're looking for AGGREGATE (see http://www.spss-tutorials.com/aggregate/) but "comparing different categories of respondents" sounds as if you'd like summary statistics over variables (MEAN) instead of over respondents (AGGREGATE).

Kind regards,

Ruben Geert van den Berg
www.spss-tutorials.com
JazzFinder
Posts: 4
Joined: Wed Jul 09, 2014 1:23 pm

### Re: How to compute a global score (in %) excluding missing v

Thank you RubenGeert, I will take a look at the AGGREGATE command.
RubenGeert
Posts: 100
Joined: Mon May 19, 2014 6:06 am

### Re: How to compute a global score (in %) excluding missing v

You're welcome!

If it's not what you're looking for, could you somehow share a sample of your data and a screenshot/.xls file of the output you'd like in its desired form? That could be helpful for us in assisting you.
JazzFinder
Posts: 4
Joined: Wed Jul 09, 2014 1:23 pm

### Re: How to compute a global score (in %) excluding missing v

Thank you. I'll do my best, once again...

I spent hours preparing a data file (a Data view) and a sample file, a variable view, the ideal output in its desired form. But I'm faced with an error "Sorry, the board attachment quota has been reached." I can't upload anything, not even a small gif.

Anyways,

Data view: http://www.bayimg.com/gapffAafE
In the sample data view below, you will notice that all variables are nominal.
V01 to V10 are dichotomous (with 0-1 values, where 0 is a negative evaluation, and 1 is a positive one). Depending on the measure,
these variables could be negative/positive perceptions, satisfaction, beliefs, etc.

The other two variables are 'Gender' (1-2) and 'AgeCat' (age category) (1-4).

Variable view: http://www.bayimg.com/GApfjAaFE

The 4 first variables (V01 to V04) are used to calculate an overall score pertaining to 'Sound quality' (call it an index, a ratio, a scale, a combined measure or a global score, etc.). Let's call it an Index for now: Index 1 = Sound Quality. An index number is a number (as a ratio) derived from a series of observations and used as an indicator or measure (The Merriam Webster Dictionary).

Index 2 ( 'Choice of bands') will be derived from the 6 following variables (V05 to V10).

Ideal output: http://bayimg.com/HApFaAaFe

Two (2) things are highly desirable here:
1- To be able to compute the overall index number for any giving index (for the whole sample, excluding missing values).
2- To be able to compare sub-samples (on the base of indices) as in the image above, and of course to find a way to know if the diffrences between sub-samples are statistically significant.

So, the Index overall score =
Overall % positive evaluations = Total Sum of positive evaluations / Total number of evaluations
For example, for males: 149 / 165 = 90,3%

This overal score could also be obtained by a weighted mean(weighted mean of all values of column '% positive evaluations'), not by an arithmetic one:
For example, for male respondents:
= [ (86% x 42) + (90% x 41) + etc.] / 165 = 90,3 %

Finally, here is the way I used to present the results when sample sizes where smaller. The only thing missing in this presentation is statistical significance.
(FYI: I used to use ViewSav, a a free utility viewer for SPSS data files and a real-time codebook).

[http://bayimg.com/GAPFlAafE

Thank you for your patience.
RubenGeert
Posts: 100
Joined: Mon May 19, 2014 6:06 am

### Re: How to compute a global score (in %) excluding missing v

About the uploads: I believe you can upload after zipping stuff. However, the available options on this forum has been steadily decreasing over the last year or so. When I joined, you could use hyperlinks in a normal way but then the forum was filled with spam for about a month and the only way the mods seem to have found to combat that, is completely disabling all kinds of links. Perhaps something similar took place for uploads. But anyway, the quality is not exactly improving...

Anyway, you are working from syntax, right? I'm kinda wondering how this could have taken you so long -unless you work from the GUI, then everything may take ages.

Still not entirely clear what you're aiming at but to start with some hints:

-1. Use something like SUM for summing over columns (within rows (=cases)), for instance see http://www.spss-tutorials.com/spss-sum-cautionary-note/

-2. Use AGGREGATE with MODE = ADDVARIABLES to sum over cases (within columns), see http://www.spss-tutorials.com/aggregate/ Combine with 1. for a grand total (over cases AND columns).

-3. Do you have CTABLES available? If so, it may be able to generate the table for you. I don't use it myself so I'm not sure about the required syntax for that.

-4. Regarding significance, A) what is your target population and B) how does your sample relate to that? Note that all standard siginifcance tests silently assume that you drew a completely random sample that is at most 10% of your target population. If that's not the case, you may wonder whether it's even worthwhile to try significance testing.

-5. Regarding significance, what exactly are your null hypotheses? Consider using separate procedures for generating your result overview table and your significance tests.

-6. Since you didn't upload actual data, it's impossible to verify the numbers you mentioned in your question. Also, since your questions are quite elaborate, try to assist us by uploading .sav data and the syntax you've been using so far. It may get us halfway the process and may facilitate the discussion.

Best,

Ruben

### Who is online

Users browsing this forum: No registered users and 1 guest