crosstab for sum instead of count

[Gary Stevens-2]

I would like to crosstab a dataset with the data summed (instead of
counted) and be able to copy and paste that crosstab output into a
spreadsheet.  I appreciate the groups guidance.

example dataset

variables    Hospital, County, LOS(length of stay)
                 ABC      A     5
                 ABC      A     9
                 XYZ      A     2
                 XYZ      A     1

spreadsheet
 columns and rows          A
                  ABC      14
                  XYZ       3

crosstab County by Hospital and sum the LOS and (like the crosstab output)
copy and paste to work with in a spreadsheet.

[lucameyer]

Try the following (tested SPSS 15):

data list free /hospital (a3) county (a1) los (f2).
begin data
ABC      A     5
ABC      A     9
XYZ      A     2
XYZ      A     1
end data.
exe.

weight by los.
crosstabs /tables=hospital by county.
weight off.

The output table can be exported to Excel by selecting the resulting table
in the Output and using the menu File >> Export and selecting Excel from the
File Type option.

HTH,
Luca

Mr. Luca MEYER
Market research, data analysis & more
www.lucameyer.com - Tel: [phone]


-----Messaggio originale-----
Da: SPSSX(r) Discussion [mailto:[hidden email]] Per conto di Gary
Stevens
Inviato: mercoledì 10 ottobre 2007 22.23
A: [hidden email]
Oggetto: crosstab for sum instead of count

I would like to crosstab a dataset with the data summed (instead of
counted) and be able to copy and paste that crosstab output into a
spreadsheet.  I appreciate the groups guidance.

example dataset

variables    Hospital, County, LOS(length of stay)
                 ABC      A     5
                 ABC      A     9
                 XYZ      A     2
                 XYZ      A     1

spreadsheet
 columns and rows          A
                  ABC      14
                  XYZ       3

crosstab County by Hospital and sum the LOS and (like the crosstab output)
copy and paste to work with in a spreadsheet.

No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.14.7/1062 - Release Date: 10/10/2007
17.11


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.5.488 / Virus Database: 269.14.7/1062 - Release Date: 10/10/2007
17.11

[Richard Ristow]

At 04:22 PM 10/10/2007, Gary Stevens wrote:

>I would like to crosstab a dataset with the data summed (instead of
>counted) and be able to copy and paste that crosstab output into a
>spreadsheet.
>
>example dataset

|-----------------------------|---------------------------|
|Output Created               |10-OCT-2007 20:43:07       |
|-----------------------------|---------------------------|
Hospital County LOS

ABC      A        5
ABC      A        9
XYZ      A        2
XYZ      A        1

Number of cases read:  4    Number of cases listed:  4

As for the crosstabs,
>spreadsheet
>  columns and rows          A
>                   ABC      14
>                   XYZ       3

Like this? SPSS 14 draft output (WRR:not saved separately):

TEMPORARY.
WEIGHT BY LOS.
CROSSTABS
   /TABLES=Hospital  BY County
   /FORMAT= AVALUE TABLES
   /CELLS= COUNT
   /COUNT ROUND CELL .


Crosstabs
|-----------------------------|---------------------------|
|Output Created               |10-OCT-2007 21:06:37       |
|-----------------------------|---------------------------|
Case Processing Summary [suppressed]

Hospital * County Crosstabulation
Count
|--------|---|------|-----|
|        |   |County|Total|
|        |   |------|     |
|        |   |A     |     |
|--------|---|------|-----|
|Hospital|ABC|14    |14   |
|        |---|------|-----|
|        |XYZ|3     |3    |
|--------|---|------|-----|
|Total       |17    |17   |
|------------|------|-----|

>and (like the crosstab output) copy and paste to work with in a
>spreadsheet.

For this, I ran the CROSSTAB wrapped in OMS/OMSEND commands:

* OMS.
OMS
  /TAG =  'XTab'
  /SELECT TABLES
  /IF COMMANDS = ["Crosstabs"]
      SUBTYPES = ["Crosstabulation"]
  /DESTINATION FORMAT = TABTEXT
   OUTFILE = "C:\Documents and Settings\Richard\My Documents" +
               "\Temporary\SPSS\"                             +
                "2007-10-10 Stevens - "                       +
                "crosstab for sum instead of count.txt".

TEMPORARY.
WEIGHT BY LOS.
CROSSTABS
   /TABLES=Hospital  BY County
   /FORMAT= AVALUE TABLES
   /CELLS= COUNT
   /COUNT ROUND CELL .

OMSEND TAG='XTab'.

The resulting output file opens nicely in Excel. The resulting
spreadsheet begins with lines

"Crosstabs "

"Hospital * County Crosstabulation"
"Count"

You can live with those, delete them in Excel, or as I recall it's easy
to use OMS options not to print them, but I'm not pursuing this that
far.

-Good luck,
  Richard
==================================
APPENDIX: All code, with test data
==================================
DATA LIST LIST SKIP=1/
              Hospital  County  LOS
              (A3,      A1,     F3).
BEGIN DATA
variables    Hospital, County, LOS(length of stay)
                  ABC      A     5
                  ABC      A     9
                  XYZ      A     2
                  XYZ      A     1
END DATA.
LIST.


* OMS.
OMS
  /TAG =  'XTab'
  /SELECT TABLES
  /IF COMMANDS = ["Crosstabs"]
      SUBTYPES = ["Crosstabulation"]
  /DESTINATION FORMAT = TABTEXT
   OUTFILE = "C:\Documents and Settings\Richard\My Documents" +
               "\Temporary\SPSS\"                             +
                "2007-10-10 Stevens - "                       +
                "crosstab for sum instead of count.txt".

TEMPORARY.
WEIGHT BY LOS.
CROSSTABS
   /TABLES=Hospital  BY County
   /FORMAT= AVALUE TABLES
   /CELLS= COUNT
   /COUNT ROUND CELL .

OMSEND TAG='XTab'.

[Fry, Jonathan B.]

One simple way is

means  LOS by Hospital by County/ cells=sum.

Jonathan Fry
SPSS Inc.

-----Original Message-----
From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Gary Stevens
Sent: Wednesday, October 10, 2007 3:23 PM
To: [hidden email]
Subject: crosstab for sum instead of count

I would like to crosstab a dataset with the data summed (instead of
counted) and be able to copy and paste that crosstab output into a
spreadsheet.  I appreciate the groups guidance.

example dataset

variables    Hospital, County, LOS(length of stay)
                 ABC      A     5
                 ABC      A     9
                 XYZ      A     2
                 XYZ      A     1

spreadsheet
 columns and rows          A
                  ABC      14
                  XYZ       3

crosstab County by Hospital and sum the LOS and (like the crosstab output)
copy and paste to work with in a spreadsheet.