Can I Mimic the Solver in Excel Within SPSS?

[Feinstein, Zachary]

I have two continuous variables that are standardized.  When I cross
them I wind up with a 2 X 2 matrix.

*       People at or above 0.0 on both dimensions go into the top-right
matrix.
*       People below 0.0 on both dimensions go into the lower-left
matrix.
*       People at or above 0.0 on the 1st dimension but below 0.0 on the
2nd dimension go into the lower-right matrix.
*       People at or above 0.0 on the 2nd dimension but below 0.0 on the
1st dimension go into the upper-left matrix.

I find all of this stuff using CROSSTABS or a variation of CTABLES.  Now
I will do this crosstab for about 40 different breaks.  Let us assume
each break is a country within the world.  So visualize, if you will, 40
rows followed by four columns of information.  Each row will have the
ROWPCT for the respective row so that they add up to 100% for each row.
Each of the columns designates which matrix the person falls into.

Now comes the real tricky part.  I do not know if 0.0 is the optimal
place to break my distribution.  I have an ideal 40 X 4 matrix that I
want my results to match so my goal is to use something like the SOLVER
to minimize the gap between my solution and the ideal that has been
submitted to me.

Can anyone offer any suggestions on how to do this with SPSS using a
standard data file?  Perhaps I need the power of Python?  Perhaps this
can easy be done creating variables in the standard SPSS file?

Thank you in advance.  And please note as well that my data is weighted.
If weighting complicates things too much I can live without it, for now.

Zachary

====================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

[Maguin, Eugene]

Zachary,

My big question is understanding your dataset.
Your description seems to be saying two different things. You begin ...

>>I have two continuous variables that are standardized.  When I cross
them I wind up with a 2 X 2 matrix.

This suggests a data set looking like

Id   x   y
01  -3  12
02  18  -8
03  -2  -4
Etc.

>>I find all of this stuff using CROSSTABS or a variation of CTABLES.  Now
I will do this crosstab for about 40 different breaks.  Let us assume
each break is a country within the world.

Ok so you have another column for the country variable. So you could have
recoded two variables, (x and y in my example) and crosstabbed them against
country as a third variable. So far nothing special. But then you say ...


>>So visualize, if you will, 40
rows followed by four columns of information.  Each row will have the
ROWPCT for the respective row so that they add up to 100% for each row.
Each of the columns designates which matrix the person falls into.

This seems to describe a new dataset which looks like this, I think

Country cell11 cell12 cell21 cell22
France    12     45     21     22
Taiwan    81     02     11     06
Etc.

So this reminds me of crosstab tables written to a file using one of the
crosstab options or else, output from an OMS operation. Is it?  Anyway, you
say

>>Now comes the real tricky part.  I do not know if 0.0 is the optimal
place to break my distribution.  I have an ideal 40 X 4 matrix that I
want my results to match so my goal is to use something like the SOLVER
to minimize the gap between my solution and the ideal that has been
submitted to me.

At this point, and to begin with, which dataset are you referring to? If the
second one, your request doesn't make sense. If the first one, what does
this 'ideal' solution look like? This brings up the second big question:
what are you really trying to do?

I'll need better explanation to understand what are you starting with and
what are you are trying to do.

Gene Maguin

=====================
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

[Feinstein, Zachary]

Thanks Gene and sorry about the delay in the response.  I have had a
chance to think about this a little bit more carefully too.

So my original dataset would have six variables.  (1) id, (2)
country_code, (3) x, (4) y, (5) break_x, and (6) break_y.  Note that
both break_x and break_y will be constants across all of my respondents.
The code in which they would be used would be like the following:

IF ((x >= break_x) AND (y >= break_y)) quad = 1.
IF ((x >= break_x) AND (y < break_y)) quad = 2.
IF ((x < break_x) AND (y >= break_y)) quad = 3.
IF ((x < break_x) AND (y < break_y)) quad = 4.

Now I can run CROSSTABS or AGGREGATE to figure the % that fall into each
of the quads versus the country.  So if I used AGGREGATE it would be 40
X 4 matrix in the SPSS data file.  40 rows for each country by 4 columns
of the quad variable.

Now the tricky part...

I want to take this 40 X 4 matrix in SPSS and subtract from it an a
priori defined matrix that is 40 X 4.  Seems fairly simple.  But I wish
to create a parameter estimation on the original respondents that
minimizes the gap between the two 40 X 4 aggregated matrices.

Does that make sense?  If not I can provide even more details.  Note- I
know how I can program a simple regression-type formula in SPSS on a
single dataset.  But the part I am having trouble with it going back &
forth between my original dataset (let's say of 40,000 respondents) and
my 40 X 4 matrix that is generated as a result.

I hope this makes sense.  Perhaps I need to work the matrix commands in
SPSS or maybe I need Python.  Either way I am a little dumbfounded.

Zachary

-----Original Message-----
From: Gene Maguin [mailto:[hidden email]]
Sent: Thursday, June 19, 2008 3:34 PM
Subject: Re: Can I Mimic the Solver in Excel Within SPSS?

Zachary,

My big question is understanding your dataset.
Your description seems to be saying two different things. You begin ...

>>I have two continuous variables that are standardized.  When I cross
them I wind up with a 2 X 2 matrix.

This suggests a data set looking like

Id   x   y
01  -3  12
02  18  -8
03  -2  -4
Etc.

>>I find all of this stuff using CROSSTABS or a variation of CTABLES.
>>Now
I will do this crosstab for about 40 different breaks.  Let us assume
each break is a country within the world.

Ok so you have another column for the country variable. So you could
have recoded two variables, (x and y in my example) and crosstabbed them
against country as a third variable. So far nothing special. But then
you say ...


>>So visualize, if you will, 40
rows followed by four columns of information.  Each row will have the
ROWPCT for the respective row so that they add up to 100% for each row.
Each of the columns designates which matrix the person falls into.

This seems to describe a new dataset which looks like this, I think

Country cell11 cell12 cell21 cell22
France    12     45     21     22
Taiwan    81     02     11     06
Etc.

So this reminds me of crosstab tables written to a file using one of the
crosstab options or else, output from an OMS operation. Is it?  Anyway,
you say

>>Now comes the real tricky part.  I do not know if 0.0 is the optimal
place to break my distribution.  I have an ideal 40 X 4 matrix that I
want my results to match so my goal is to use something like the SOLVER
to minimize the gap between my solution and the ideal that has been
submitted to me.

At this point, and to begin with, which dataset are you referring to? If
the second one, your request doesn't make sense. If the first one, what
does this 'ideal' solution look like? This brings up the second big
question:
what are you really trying to do?

I'll need better explanation to understand what are you starting with
and what are you are trying to do.

Gene Maguin

=====================
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

[Maguin, Eugene]

Zachary,

Ok, I understand a bit more. You are creating the new variables in your
original dataset and then aggregating those to get a 40x4 observed, let's
called it, summary matrix. The new variables are created by using a separate
cutpoint, break_x and break_y, for each of the two original variables, x and
y. The cutpoints are the same for each country.

Then you have an apriori matrix, also 40x4 that is to be subtracted from the
observed summary matrix.

However, what you'd like to do is to find a pair of cutpoints, the same for
each country, so that the difference between the observed summary matrix and
the apriori matrix is minimized. Is this true? Is it true that the cutpoints
will be the same for each country?

I'll assume that these statements are true. Then, ...
So you have a minimization problem. I'm not sure whether you should be
minimizing the distance between the original data (the 40,000) or between
the summary matrix. I think you have to do it for the 40,000. Perhaps
someone else can comment.

Second, I'm not sure how to formulate the minimization equation. This is
something I've never, ever, tried to do. Again, I hope that a more
knowledgable person can jump in here.

Gene Maguin

=====================
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

[Feinstein, Zachary]

Thanks Gene.

The code below my text is a set that I have handy for non-linear
regression in SPSS syntax.  You can see how it is applicable to
non-regression situations as well too.

Note you are correct in your assumptions.  I need to somehow iteratively
go between my original 40,000 cases and the summarized one.  Then
minimize the summarized one.

Also- I can do it fairly easily in Excel provided I figure a way to
automate the pivot-tables that are created for the first matrix then
compared against my a priori matrix.  The minimization has to take place
on the 40 X 4 matrix but the parameters need to somehow be shot back to
the original 40,000.

Maybe I am missing something and perhaps there is not a "unique"
solution to all of this either.  Would be great to hear from one of the
SPSS experts.

Thanks again for your help.

Zachary

* NonLinear Regression.
MODEL PROGRAM a = 1 b1 = 1.
COMPUTE PRED_ = a + b1 * oct_top3.
NLR scale_top2
 /OUTFILE='C:\Temp\spss4056\SPSSFNLR.TMP'
 /PRED PRED_
 /CRITERIA SSCONVERGENCE 1E-8 PCON 1E-8.
* I get the exact same results as before.

* NonLinear Regression with constraints- bounding it at 0 and 100.
MODEL PROGRAM a = 1 b1 = 1 .
COMPUTE PRED_ = a + b1 * oct_top3.
CONSTRAINED FUNCTION.
COMPUTE CONSTR1_ = a + (b1 * 0).
COMPUTE CONSTR2_ = a + (b1 * 100).
CNLR scale_top2
  /OUTFILE='C:\Temp\spss4056\SPSSFNLR.TMP'
  /PRED PRED_
  /BOUNDS CONSTR1_ >= 0; CONSTR2_ <= 100
  /CRITERIA STEPLIMIT 2 ISTEP 1E+20.

-----Original Message-----
From: Gene Maguin [mailto:[hidden email]]
Sent: Tuesday, June 24, 2008 2:09 PM
Subject: Re: Can I Mimic the Solver in Excel Within SPSS?

Zachary,

Ok, I understand a bit more. You are creating the new variables in your
original dataset and then aggregating those to get a 40x4 observed,
let's called it, summary matrix. The new variables are created by using
a separate cutpoint, break_x and break_y, for each of the two original
variables, x and y. The cutpoints are the same for each country.

Then you have an apriori matrix, also 40x4 that is to be subtracted from
the observed summary matrix.

However, what you'd like to do is to find a pair of cutpoints, the same
for each country, so that the difference between the observed summary
matrix and the apriori matrix is minimized. Is this true? Is it true
that the cutpoints will be the same for each country?

I'll assume that these statements are true. Then, ...
So you have a minimization problem. I'm not sure whether you should be
minimizing the distance between the original data (the 40,000) or
between the summary matrix. I think you have to do it for the 40,000.
Perhaps someone else can comment.

Second, I'm not sure how to formulate the minimization equation. This is
something I've never, ever, tried to do. Again, I hope that a more
knowledgable person can jump in here.

Gene Maguin

=====================
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