## Creating new variable (ranking-based subtract)

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

drdunkel
Posts: 5
Joined: Thu Jul 24, 2014 5:31 pm

### Creating new variable (ranking-based subtract)

Hi everyone.

I understand my last post came out a bit unclear. I'm still struggling with this, so would be very greatful for any tips. First of all, example of how my data is organized. Products are ranked based on the total amount (breaks on each ID & month). Highest amounts have 1 or 1,5 (if ties), and second highest 2 or 3 or 2,5 (if ties), and so on. I want to focus on highest/second highest ranks.

ID | month | product | total_amount | rank
1 | 4 | 12 | 12 | 1,5
1 | 4 | 14 | 12 | 1,5
1 | 4 | 17 | 9 | 3
1 | 4 | 15 | 8 | 4
2 | 5 | 11 | 11 | 1
2 | 5 | 13 | 4 | 2 ...

- How could I create a new continuous variable from 0 to 1 showing the difference in total amount between 1st and 2nd ranked? (again, breaks on each ID & month). Equation I want to implement for this new variable is:
1 - (2nd ranked total amount(s) ) / 1st ranked total amount(s) )

Thank you for everyone and I wish you a great weekend,
-S
drdunkel
Posts: 5
Joined: Thu Jul 24, 2014 5:31 pm

### Re: Creating new variable (ranking-based subtract)

With a nudge from Ruben Geert van den Berg (http://www.spss-tutorials.com/) - thanks again!, I got this to work.
Here syntax, in case someone in the future looks for info on similar question:

IF ID = lag(ID) AND month = LAG(month) new_variabl = total_amount2 / LAG(total_amount2).
EXECUTE.

IF (new_variabl LE 0.99) newvar2=1 - new_variabl.
EXECUTE.

AGGREGATE