SPSSX Discussion

Loop for variables crosstab

Classic

List

Threaded

13 messages Options

jfca283

Loop for variables crosstab

Hello
i'm tired of editing a syntax file for doing a crosstabs.
I hava 10 variables and i need to cross them. Obviously a crosstab v1 v2 is the same that crosstab v2 v1.
How can i declare the variables i want to process and besides, no repeating v1 v2 doing v2 v1?
Thanks for your cooperation and time.

MaxJasper

Re: Loop for variables crosstab

Now with this kind of attitude you make IBM Executives very very angry!

From: jfca283 [via SPSSX Discussion] [mailto:[hidden email]]
Sent: 2013-Nov-14 09:31
Subject: Loop for variables crosstab

Maguin, Eugene

Re: Loop for variables crosstab

Ooh, I’m sure there are cleverer ways but

Crosstabs x1 by x2 to x5/x2 by x3 to x5 ……

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of MaxJasper
Sent: Thursday, November 14, 2013 11:47 AM
To: [hidden email]
Subject: Re: Loop for variables crosstab

Now with this kind of attitude you make IBM Executives very very angry!

From: jfca283 [via SPSSX Discussion] [[hidden email]]
Sent: 2013-Nov-14 09:31
Subject: Loop for variables crosstab

David Marso

Re: Loop for variables crosstab

Administrator

In reply to this post by jfca283

*YOU* can write a macro !
See DEFINE !ENDDEFINE
Within that use two !DO !DOEND loops and an !IF
We went around this with your file loop business awhile back.
I will NOT write any more code for you!
I thought you were switching to Stata?

jfca283 wrote

Hello
i'm tired of editing a syntax file for doing a crosstabs.
I hava 10 variables and i need to cross them. Obviously a crosstab v1 v2 is the same that crosstab v2 v1.
How can i declare the variables i want to process and besides, no repeating v1 v2 doing v2 v1?
Thanks for your cooperation and time.

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?"

jfca283

Re: Loop for variables crosstab

if you are not interested in answering, then just don't. But please, stop preaching. Stata can't work with non integer weights.

David Marso

Re: Loop for variables crosstab

Administrator

This post was updated on .

I gave you a hint on where to go so YOU can solve your problem.
I'm not preaching. Maybe work on your attitude.
--

jfca283 wrote

if you are not interested in answering, then just don't. But please, stop preaching. Stata can't work with non integer weights.

jfca283

Re: Loop for variables crosstab

You are making fun i'm using spss. Then reply in bad manners. But ok, forget it.

Bruce Weaver

Re: Loop for variables crosstab

Administrator

In reply to this post by David Marso

I too was thinking of nested !DO - !DOEND loops, but with !TAIL rather than !IF. E.g.,

NEW FILE.
DATASET CLOSE all.

* Change path to indicate where you have the SPSS sample data files stored.
GET FILE = "C:\SPSSdata\survey_sample.sav".

descriptives wrkstat marital degree sex race born parborn polviews cappun postlife.

* Macro defintition.

DEFINE !Xtab (Vars = !CMDEND)
!LET !Vcopy = !Vars
!DO !RowVar !in (!Vars)
!LET !Vcopy = !TAIL(!Vcopy)
!DO !ColVar !in (!Vcopy)
CROSSTABS !RowVar by !ColVar.
!DOEND
!DOEND
!ENDDEFINE.

*SET MPRINT on.
!Xtab Vars = sex race born parborn polviews.
*SET MPRINT off.

* It looks like the keyword TO does not work in the Vars argument;
* I think you will have to list all of the variables individually.

David Marso wrote

*YOU* can write a macro !
See DEFINE !ENDDEFINE
Within that use two !DO !DOEND loops and an !IF
We went around this with your file loop business awhile back.
I will NOT write any more code for you!
I thought you were switching to Stata?

jfca283 wrote

Hello
i'm tired of editing a syntax file for doing a crosstabs.
I hava 10 variables and i need to cross them. Obviously a crosstab v1 v2 is the same that crosstab v2 v1.
How can i declare the variables i want to process and besides, no repeating v1 v2 doing v2 v1?
Thanks for your cooperation and time.

--
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/).

David Marso

Re: Loop for variables crosstab

Administrator

45 data passes is a little hard to contemplate if the file is of any large size ;-)
How about one data pass using a variation on Gene's post.

DEFINE TABS (!POS !CHAREND ("(") / !POS !CMDEND).
!LET !VCopy=!TAIL(!1)
CROSSTABS VARIABLES !1 ( !2
/TABLES =!DO !R !IN(!1) !CONCAT(!R," BY ",!VCopy, " /")
!LET !VCopy=!TAIL(!VCopy) !IF (!VCopy=!NULL) !THEN !BREAK !IFEND !DOEND .
!ENDDEFINE.

/* CREATE DUMMY DATA */.
MATRIX.
SAVE TRUNC(UNIFORM(1000,10)*5+1) /OUTFILE * / VARIABLES Var01 TO Var10.
END MATRIX.

* This will break for more than 20 variables ;-(((( *

TABS Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10 (1,10).

I had initially tried:

DEFINE TABS2 (!POS !CMDEND).
!LET !VCopy=!TAIL(!1)
CROSSTABS VARIABLES !1
/TABLES =!DO !R !IN(!1) !CONCAT(!R," BY ",!VCopy, " /")
!LET !VCopy=!TAIL(!VCopy) !IF (!VCopy=!NULL) !THEN !BREAK !IFEND !DOEND .
!ENDDEFINE.
TABS2 Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10 .

resulting in the following syntax and error ;-(.

CROSSTABS VARIABLES Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/TABLES = Var01 BY Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var02 BY Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var03 BY Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var04 BY Var05 Var06 Var07 Var08 Var09 Var10
/ Var05 BY Var06 Var07 Var08 Var09 Var10
/ Var06 BY Var07 Var08 Var09 Var10
/ Var07 BY Var08 Var09 Var10
/ Var08 BY Var09 Var10
/ Var09 BY Var10 .
.
ERROR???
A variable on the CROSSTABS command is missing a range specification.
Perhaps a range is present for some of the variables and not others.
Ranges must be provided for all of the variables (integer mode)
or none of the variables (general mode).Text found: /
Execution of this command stops.

Bruce Weaver wrote

I too was thinking of nested !DO - !DOEND loops, but with !TAIL rather than !IF. E.g.,

NEW FILE.
DATASET CLOSE all.

* Change path to indicate where you have the SPSS sample data files stored.
GET FILE = "C:\SPSSdata\survey_sample.sav".

descriptives wrkstat marital degree sex race born parborn polviews cappun postlife.

* Macro defintition.

DEFINE !Xtab (Vars = !CMDEND)
!LET !Vcopy = !Vars
!DO !RowVar !in (!Vars)
!LET !Vcopy = !TAIL(!Vcopy)
!DO !ColVar !in (!Vcopy)
CROSSTABS !RowVar by !ColVar.
!DOEND
!DOEND
!ENDDEFINE.

*SET MPRINT on.
!Xtab Vars = sex race born parborn polviews.
*SET MPRINT off.

* It looks like the keyword TO does not work in the Vars argument;
* I think you will have to list all of the variables individually.

David Marso wrote

*YOU* can write a macro !
See DEFINE !ENDDEFINE
Within that use two !DO !DOEND loops and an !IF
We went around this with your file loop business awhile back.
I will NOT write any more code for you!
I thought you were switching to Stata?

jfca283 wrote

Hello
i'm tired of editing a syntax file for doing a crosstabs.
I hava 10 variables and i need to cross them. Obviously a crosstab v1 v2 is the same that crosstab v2 v1.
How can i declare the variables i want to process and besides, no repeating v1 v2 doing v2 v1?
Thanks for your cooperation and time.

Jon K Peck

Re: Loop for variables crosstab

In reply to this post by Bruce Weaver

You can do this, of course, with programmability. This code shows how to use the SPSSINC PROGRAM extension command to pass arguments to a Python program.

begin program.
import spss, sys
def manytabs():
specs = []
for i in range(len(sys.argv)-2):
specs.append(sys.argv[i+1] + " BY " + " ".join(sys.argv[i+2:]))
cmd = "CROSSTABS %s" % "/".join(specs)
spss.Submit(cmd)
end program.

spssinc program manytabs wrkstat marital childs.

In this example, there is no functionality advantage over the macro (Bruce - TO does work in the (anonymous) TABLE subcommand). But it opens the door to smarter variations that are more general and require less manual work. Here is an example that automatically crosstabs all categorical variables in the dataset without having to list them. Other selection rules such as all categorical variables with names starting with x could be done similarly.

begin program.
import spss, spssaux
def manytabs():
catvars = spssaux.VariableDict(variableLevel=["nominal", "ordinal"]).variables
specs = []
for i in range(len(catvars)-1):
specs.append(catvars[i] + " BY " + " ".join(catvars[i+1:]))
cmd = "CROSSTABS %s" % "/".join(specs)
spss.Submit(cmd)
end program.

spssinc program manytabs.

Jon Peck (no "h") aka Kim
Senior Software Engineer, IBM
[hidden email]
phone: 720-342-5621

Bruce Weaver

Re: Loop for variables crosstab

Administrator

In reply to this post by David Marso

Fair point about the number of data passes. Here is a new and improved version of what I posted earlier. When I hand it 10 variables, it generates 9 CROSSTABS commands.

* Macro defintition.

DEFINE !Xtab (Vars = !CMDEND)
!LET !Vcopy = !Vars
!DO !RowVar !in (!Vars)
!LET !Vcopy = !TAIL(!Vcopy)
!IF (!LENGTH(!Vcopy) !GT 0) !THEN
CROSSTABS !RowVar by !Vcopy.
!IFEND
!DOEND
!ENDDEFINE.

SET MPRINT on.
!Xtab Vars = wrkstat marital degree sex race born parborn polviews cappun postlife.
SET MPRINT off.

David Marso wrote

45 data passes is a little hard to contemplate if the file is of any large size ;-)
How about one data pass using a variation on Gene's post.

DEFINE TABS (!POS !CHAREND ("(") / !POS !CMDEND).
!LET !VCopy=!TAIL(!1)
CROSSTABS VARIABLES !1 ( !2
/TABLES =!DO !R !IN(!1) !CONCAT(!R," BY ",!VCopy, " /")
!LET !VCopy=!TAIL(!VCopy) !IF (!VCopy=!NULL) !THEN !BREAK !IFEND !DOEND .
!ENDDEFINE.

/* CREATE DUMMY DATA */.
MATRIX.
SAVE TRUNC(UNIFORM(1000,10)*5+1) /OUTFILE * / VARIABLES Var01 TO Var10.
END MATRIX.

* This will break for more than 20 variables ;-(((( *

TABS Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10 (1,10).

I had initially tried:

DEFINE TABS2 (!POS !CMDEND).
!LET !VCopy=!TAIL(!1)
CROSSTABS VARIABLES !1
/TABLES =!DO !R !IN(!1) !CONCAT(!R," BY ",!VCopy, " /")
!LET !VCopy=!TAIL(!VCopy) !IF (!VCopy=!NULL) !THEN !BREAK !IFEND !DOEND .
!ENDDEFINE.
TABS2 Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10 .

resulting in the following syntax and error ;-(.

CROSSTABS VARIABLES Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/TABLES = Var01 BY Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var02 BY Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var03 BY Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var04 BY Var05 Var06 Var07 Var08 Var09 Var10
/ Var05 BY Var06 Var07 Var08 Var09 Var10
/ Var06 BY Var07 Var08 Var09 Var10
/ Var07 BY Var08 Var09 Var10
/ Var08 BY Var09 Var10
/ Var09 BY Var10 .
.
ERROR???
A variable on the CROSSTABS command is missing a range specification.
Perhaps a range is present for some of the variables and not others.
Ranges must be provided for all of the variables (integer mode)
or none of the variables (general mode).Text found: /
Execution of this command stops.

Bruce Weaver wrote

I too was thinking of nested !DO - !DOEND loops, but with !TAIL rather than !IF. E.g.,

NEW FILE.
DATASET CLOSE all.

* Change path to indicate where you have the SPSS sample data files stored.
GET FILE = "C:\SPSSdata\survey_sample.sav".

descriptives wrkstat marital degree sex race born parborn polviews cappun postlife.

* Macro defintition.

DEFINE !Xtab (Vars = !CMDEND)
!LET !Vcopy = !Vars
!DO !RowVar !in (!Vars)
!LET !Vcopy = !TAIL(!Vcopy)
!DO !ColVar !in (!Vcopy)
CROSSTABS !RowVar by !ColVar.
!DOEND
!DOEND
!ENDDEFINE.

*SET MPRINT on.
!Xtab Vars = sex race born parborn polviews.
*SET MPRINT off.

* It looks like the keyword TO does not work in the Vars argument;
* I think you will have to list all of the variables individually.

David Marso wrote

*YOU* can write a macro !
See DEFINE !ENDDEFINE
Within that use two !DO !DOEND loops and an !IF
We went around this with your file loop business awhile back.
I will NOT write any more code for you!
I thought you were switching to Stata?

jfca283 wrote

Hello
i'm tired of editing a syntax file for doing a crosstabs.
I hava 10 variables and i need to cross them. Obviously a crosstab v1 v2 is the same that crosstab v2 v1.
How can i declare the variables i want to process and besides, no repeating v1 v2 doing v2 v1?
Thanks for your cooperation and time.

Ruben Geert van den Berg

Re: Loop for variables crosstab

I thought it could be done with basic nested Python loops too. I love the .expand() method since it allows for a super flexible, efficient variable specification. However, the order of the generated CROSSTABS command keeps getting messed up and it still puzzles me why...

begin program.
vars = 'v1 to v4'
import spss,spssaux
vDict = spssaux.VariableDict(caseless = True)
for colVar in sorted(vDict.expand(vars),key = lambda colVar: vDict.VariableIndex(colVar)):
for rowVar in sorted(vDict.expand(vars),key = lambda rowVar: vDict.VariableIndex(rowVar)):
if vDict.VariableIndex(colVar) > vDict.VariableIndex(rowVar): #not fool proof?
spss.Submit('CROSSTABS %(rowVar)s BY %(colVar)s.'%locals())
end program.

David Marso

Re: Loop for variables crosstab

Administrator

In reply to this post by Bruce Weaver

That can actually be done with 1 data pass.
My macro failed due to SPSS screwing up and thinking my Var01... was a
VARIABLES subcommand ;-( thanks to Jon for sorting that off list).
Now harvesting that output will be fun...
Using OMS... the resulting dataset is a mess.
Stay tuned for VARSTOCASES... CASESTOVARS data rederangenment... Solved but on my other box...
Q @IBM: Why is CROSSTABS limited to 20 tables? I have 4G on one box 8G on the other.
Why can't XTABS handle a GT 20 tables request?
Yeah... prehistoric code. Maybe bring that into the 21st century????
Of course it would probably be a Model Viewer Monstrosity output... THANKS NO!!!
Please kill that silly abomination off ASAP! Kind of like you did to IGRAPH.

Bruce Weaver wrote

Fair point about the number of data passes. Here is a new and improved version of what I posted earlier. When I hand it 10 variables, it generates 9 CROSSTABS commands.

* Macro defintition.

DEFINE !Xtab (Vars = !CMDEND)
!LET !Vcopy = !Vars
!DO !RowVar !in (!Vars)
!LET !Vcopy = !TAIL(!Vcopy)
!IF (!LENGTH(!Vcopy) !GT 0) !THEN
CROSSTABS !RowVar by !Vcopy.
!IFEND
!DOEND
!ENDDEFINE.

SET MPRINT on.
!Xtab Vars = wrkstat marital degree sex race born parborn polviews cappun postlife.
SET MPRINT off.

David Marso wrote

45 data passes is a little hard to contemplate if the file is of any large size ;-)
How about one data pass using a variation on Gene's post.

DEFINE TABS (!POS !CHAREND ("(") / !POS !CMDEND).
!LET !VCopy=!TAIL(!1)
CROSSTABS VARIABLES !1 ( !2
/TABLES =!DO !R !IN(!1) !CONCAT(!R," BY ",!VCopy, " /")
!LET !VCopy=!TAIL(!VCopy) !IF (!VCopy=!NULL) !THEN !BREAK !IFEND !DOEND .
!ENDDEFINE.

/* CREATE DUMMY DATA */.
MATRIX.
SAVE TRUNC(UNIFORM(1000,10)*5+1) /OUTFILE * / VARIABLES Var01 TO Var10.
END MATRIX.

* This will break for more than 20 variables ;-(((( *

TABS Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10 (1,10).

I had initially tried:

DEFINE TABS2 (!POS !CMDEND).
!LET !VCopy=!TAIL(!1)
CROSSTABS VARIABLES !1
/TABLES =!DO !R !IN(!1) !CONCAT(!R," BY ",!VCopy, " /")
!LET !VCopy=!TAIL(!VCopy) !IF (!VCopy=!NULL) !THEN !BREAK !IFEND !DOEND .
!ENDDEFINE.
TABS2 Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10 .

resulting in the following syntax and error ;-(.

CROSSTABS VARIABLES Var01 Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/TABLES = Var01 BY Var02 Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var02 BY Var03 Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var03 BY Var04 Var05 Var06 Var07 Var08 Var09 Var10
/ Var04 BY Var05 Var06 Var07 Var08 Var09 Var10
/ Var05 BY Var06 Var07 Var08 Var09 Var10
/ Var06 BY Var07 Var08 Var09 Var10
/ Var07 BY Var08 Var09 Var10
/ Var08 BY Var09 Var10
/ Var09 BY Var10 .
.
ERROR???
A variable on the CROSSTABS command is missing a range specification.
Perhaps a range is present for some of the variables and not others.
Ranges must be provided for all of the variables (integer mode)
or none of the variables (general mode).Text found: /
Execution of this command stops.

Bruce Weaver wrote

I too was thinking of nested !DO - !DOEND loops, but with !TAIL rather than !IF. E.g.,

NEW FILE.
DATASET CLOSE all.

* Change path to indicate where you have the SPSS sample data files stored.
GET FILE = "C:\SPSSdata\survey_sample.sav".

descriptives wrkstat marital degree sex race born parborn polviews cappun postlife.

* Macro defintition.

DEFINE !Xtab (Vars = !CMDEND)
!LET !Vcopy = !Vars
!DO !RowVar !in (!Vars)
!LET !Vcopy = !TAIL(!Vcopy)
!DO !ColVar !in (!Vcopy)
CROSSTABS !RowVar by !ColVar.
!DOEND
!DOEND
!ENDDEFINE.

*SET MPRINT on.
!Xtab Vars = sex race born parborn polviews.
*SET MPRINT off.

* It looks like the keyword TO does not work in the Vars argument;
* I think you will have to list all of the variables individually.

David Marso wrote

*YOU* can write a macro !
See DEFINE !ENDDEFINE
Within that use two !DO !DOEND loops and an !IF
We went around this with your file loop business awhile back.
I will NOT write any more code for you!
I thought you were switching to Stata?

jfca283 wrote

Hello
i'm tired of editing a syntax file for doing a crosstabs.
I hava 10 variables and i need to cross them. Obviously a crosstab v1 v2 is the same that crosstab v2 v1.
How can i declare the variables i want to process and besides, no repeating v1 v2 doing v2 v1?
Thanks for your cooperation and time.