How to report rows with zero cases in Crosstabs?

[Eero Olli]

Dear group,

 

* My question is a trivial one: How do I get Crosstabs to report rows with zero cases?

* In this example i would like to have the first crosstable to include the row C

* and the second crosstable to include a row for Fish. 

 

 

DATA LIST LIST (",")/ var1 (N1) Pet (A4) Year (A4).

begin data

1,Cat,2010

1,Dog,2010

2,Dog,2011

2,Cat,2010

2,Dog,2011

4,Cat,2011

END DATA.

LIST.

 

DATASET NAME cases WINDOW=front.

DATASET ACTIVATE cases.

 

VALUE LABELS var1

1 'A'

2 'B'

3 'C'

4 'D'.            

 

VALUE LABELS Pet

"Cat"    "Small cats"

"Dog"    "Small dogs"

"Fish"   "Aquarium fish".

CROSSTABS /TABLES=var1 BY Year

/Pet BY Year.

 

 

 

Sincerely,

Eero Olli

 

 

Eero Olli

Senioradviser at the Equality- and Anti-Discrimination Ombud                                Phone: [phone]

Mail: POB 8048 Dep, 0031 Oslo

Visits: Mariboesgate 13, Oslo                                                                                                                   www.ldo.no

 

 

[Bruce Weaver]

See the "Integer Mode" examples under CROSSTABS in the Command Syntax Reference Manual (aka the "Fine Manual").

Eero Olli wrote

Dear group,

* My question is a trivial one: How do I get Crosstabs to report rows with zero cases?
* In this example i would like to have the first crosstable to include the row C
* and the second crosstable to include a row for Fish.


DATA LIST LIST (",")/ var1 (N1) Pet (A4) Year (A4).
begin data
1,Cat,2010
1,Dog,2010
2,Dog,2011
2,Cat,2010
2,Dog,2011
4,Cat,2011
END DATA.
LIST.

DATASET NAME cases WINDOW=front.
DATASET ACTIVATE cases.

VALUE LABELS var1
1 'A'
2 'B'
3 'C'
4 'D'.

VALUE LABELS Pet
"Cat"    "Small cats"
"Dog"    "Small dogs"
"Fish"   "Aquarium fish".
CROSSTABS /TABLES=var1 BY Year
/Pet BY Year.



Sincerely,
Eero Olli


Eero Olli
Senioradviser at the Equality- and Anti-Discrimination Ombud                                Phone: [phone]
Mail: POB 8048 Dep, 0031 Oslo
Visits: Mariboesgate 13, Oslo                                                                                                                   www.ldo.no<http://www.ldo.no/>

[Andy W]

Very cool, I didn't know about that integer mode for crosstabs. A more flexible approach in general would be to use Ctables commands (but it is an "extra feature", so good to know other options).

Thanks Bruce, I wish I had known about this a few months ago. Below I have an example of producing the desired tables using ctables and integer mode crosstabs.

***************************************************************.
*OP's original data, a bit garbled by extra line breaks.
DATA LIST LIST (",")/ var1 (N1) Pet (A4) Year (A4).
begin data
1,Cat,2010
1,Dog,2010
2,Dog,2011
2,Cat,2010
2,Dog,2011
4,Cat,2011
END DATA.
LIST.
DATASET NAME cases WINDOW=front.
DATASET ACTIVATE cases.
VALUE LABELS var1
1 'A'
2 'B'
3 'C'
4 'D'.            
VALUE LABELS Pet
"Cat"    "Small cats"
"Dog"    "Small dogs"
"Fish"   "Aquarium fish".
CROSSTABS /TABLES=var1 BY Year
/Pet BY Year.

*Example with ctable with original variables (to include empty categories).
ctables
/table var1 by Year
/table Pet by Year.

*Integer mode for crosstabs needs numeric varaibles.
AUTORECODE VARIABLES=Pet 
  /INTO Pet_Num.
ALTER TYPE Year (F4.0).
*auto-recode does not add Fish apparently.
add value labels Pet_Num
3 'Fish'.

CROSSTABS variables = var1 (1,4) Pet_Num (1,3) Year (2010,2011)
/TABLES=var1 BY Year
/Pet_Num BY Year.
***************************************************************.

[Jon K Peck]

Regarding the AUTORECODE comment, it is true that the "fish" category doesn't appear in the output or get a value label because fish didn't appear in the input.  For the simple example in this case, just adding the value label is sensible.  But AUTORECODE has a template property that can solve this problem in the general case.

If you use the SAVE TEMPLATE subcommand, AUTORECODE creates a sav-format file containing the mapping.  This file can be edited in the Data Editor, where you can define additional mappings.  Then future autorecodes can use the APPLY TEMPLATE subcommand to map the input values and will generate appropriate value labels even when the values do not occur.  (New values can also be added and the template resaved.)

This feature was designed for situations such as daily pulls from a database where all possible values might not occur every day but you want the autorecode to map to the same set of values regardless.

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




From:        Andy W <[hidden email]>
To:        [hidden email]
Date:        08/17/2012 07:05 AM
Subject:        Re: [SPSSX-L] How to report rows with zero cases in Crosstabs?
Sent by:        "SPSSX(r) Discussion" <[hidden email]>

---

Very cool, I didn't know about that integer mode for crosstabs. A more flexible approach in general would be to use Ctables commands (but it is an "extra feature", so good to know other options).

Thanks Bruce, I wish I had known about this a few months ago. Below I have an example of producing the desired tables using ctables and integer mode crosstabs.

***************************************************************.
*OP's original data, a bit garbled by extra line breaks.
DATA LIST LIST (",")/ var1 (N1) Pet (A4) Year (A4).
begin data
1,Cat,2010
1,Dog,2010
2,Dog,2011
2,Cat,2010
2,Dog,2011
4,Cat,2011
END DATA.
LIST.
DATASET NAME cases WINDOW=front.
DATASET ACTIVATE cases.
VALUE LABELS var1
1 'A'
2 'B'
3 'C'
4 'D'.
VALUE LABELS Pet
"Cat"    "Small cats"
"Dog"    "Small dogs"
"Fish"   "Aquarium fish".
CROSSTABS /TABLES=var1 BY Year
/Pet BY Year.

*Example with ctable with original variables (to include empty categories).
ctables
/table var1 by Year
/table Pet by Year.

*Integer mode for crosstabs needs numeric varaibles.
AUTORECODE VARIABLES=Pet
 /INTO Pet_Num.
ALTER TYPE Year (F4.0).
*auto-recode does not add Fish apparently.
add value labels Pet_Num
3 'Fish'.

CROSSTABS variables = var1 (1,4) Pet_Num (1,3) Year (2010,2011)
/TABLES=var1 BY Year
/Pet_Num BY Year.
***************************************************************.

Andy W
[hidden email]
http://andrewpwheeler.wordpress.com/

---

View this message in context: Re: How to report rows with zero cases in Crosstabs?
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

[Eero Olli]

Thanks to Bruce, Andy and Jon!

 

I knew there was an elegant solution, I could not see. Now I learned something new, and the problem is solved.

 

 

Best,

Eero Olli