how to do a table with summarized info

[Rodrigo Briceño]

Dear SPSS listers.
I have a survey where there are some set of questions regarding the
presence or absence of some condition (yes, no) and some other set where
the respondents asked the number of times they suffered that condition.
Respondents are households heads, so each row in my database is a HH.
There are 9 conditions and then 18 variables for reporting this
information. My issue is related with how to design a table where I can
put the two sets of information at the same time.

CND NHH TTCP
c1  20   35
c2  10   23
c3  35   78
...
c9


Where:
CND:  condition
NHH:  number of households
TTCP: total times condition present

I tried with an OLAP cube, but the info is not properly presented, and I
want to avoid doing some formatting to tables, since there are a lot of
tables that must be done.

Thanks in advance.

P.D. I can send an extract of the DB in order to get a clearer vision
--

__________________________________________________________________

*Rodrigo Briceño*
Project Manager
Sanigest Internacional

[phone] ext. 113  * Oficina* *Costa Rica*
[phone]                  *Fax
*[phone]*                  Celular**
*[hidden email] <mailto:[hidden email]>
www.sanigest.com <http://www.sanigest.com/>

MSN: [hidden email] <mailto:[hidden email]>
SKYPE: rbriceno1087

_____________________

This communication contains legal information which is privileged and
confidential. It is for the exclusive use of the address and
distribution, dissemination, copying or use by others is strictly
prohibited. If you have received this communication by error, please
delete the original message and e-mail us.


Esta comunicación contiene información legal privilegiada y confidencial
para el uso exclusivo del destinatario. La distribución, diseminación,
copia u otro uso por terceras personas es estrictamente prohibida. Si
usted ha recibido esta comunicación por error, le rogamos borrar el
mensaje original y comunicárnoslo a esta misma dirección.

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

[Richard Ristow]

At 01:23 PM 11/18/2008, Rodrigo Briceño wrote:

>I have a survey where there are some set of
>questions regarding the presence or absence of
>some condition (yes, no) and some other set
>where the respondents asked the number of times
>they suffered that condition. Respondents are
>households heads, so each row in my database is
>a HH. There are 9 conditions and then 18
>variables for reporting this information. My
>issue is related with how to design a table
>where I can put the two sets of information at the same time.

|-----------------------------|---------------------------|
|Output Created               |19-NOV-2008 00:14:10       |
|-----------------------------|---------------------------|
CND NHH TTCP

c1   20   35
c2   10   23
c3   35   78

Number of cases read:  3    Number of cases listed:  3
>Where:
>CND:  condition
>NHH:  number of households
>TTCP: total times condition present

Often, you can treat data that gives counts as if
if had the individual cases, by WEIGHTing by the
count. But I'm not sure that will work for you. A data line like
CND NHH TTCP
c1   20   35
has lost some information: it doesn't tell in how
many of the 20 household the condition occurred,
or what was the breakdown of number of occurrences, by household.

Can you give us an example of a table you'd like
to see, so we can tell whether it is, in principle, possible with your data?
=============================
APPENDIX: Test data, and code
(WRR: not saved separately)
=============================
(This isn't a solution; it's just test data, from
the original posting, to use if there's a reason to write a solution later.)
DATA LIST LIST /
    CND NHH TTCP
   (A2, F3,  F3).
BEGIN DATA
    c1  20   35
    c2  10   23
    c3  35   78
END DATA.

LIST.

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

[Richard Ristow]

At Wed, 19 Nov 2008 00:23:25 -0500, I wrote,

>>Can you give us an example of a table you'd like to see?


At 04:00 PM 11/21/2008, Rodrigo Briceno replied, off-list, with a set
of test data, from which this is an extract:
|-----------------------------|---------------------------|
|Output Created               |23-NOV-2008 01:16:41       |
|-----------------------------|---------------------------|
[TestData]

id                atimes btimes ctimes dtimes etimes ftimes gtimes

10102014005090011     0      0      0      0      0      2      0
10102049037050111     0      1      0      0      0      0      0
10102050002000011     0      3      0      0      0      0      0
10102050002060031     0      1      0      0      0      0      0
10102050022000121     3      0      0      0      0      0      0
10102050023000131     0      0      0      0      0      0      0
10104029026020081     0      1      0      0      0      0      0
10104029047010131     0      0      0      0      0      0      0
10105041041000121     3      3      0      1      0      3      0
10105080031000071     0      0      0      0      0      0      0
10105080041000081     0      0      0      0      0      1      0
10105089031070131     0      0      0      0      0      0      0
10105089031080141     0      0      0      0      0      0      0
10106014020000021     0      0      0      0      0      0      0
10106015035000111     0      0      0      0      0      0      0
10106015036000121     0      0      0      0      0      0      0
10106015507000141     0      2      0      0      0      2      0
10106030019000071     0      0      0      0      0      0      0
10107023054010301     0      0      0      0      0      0      0
10107023096000361     0      0      0      0      0      0      0

Number of cases read:  20    Number of cases listed:  20

and added,
>The thing is to have in rows the events and in columns two
>summaries: how many hh reported the event and how many events do the
>hh reported.

If "how many events the hh reported" means the total number of this
kind of event reported by all households, this (tested) should work:

DATASET ACTIVATE TestData.
DATASET COPY     LongForm.
DATASET ACTIVATE LongForm WINDOW=FRONT.

*  I.     "Unroll" to a separate line for each occurrence count      .

*  Restructure, using VARSTOCASES                              ..... .

VARSTOCASES
   /MAKE Occur    'Occurrences of event'
    FROM atimes btimes ctimes dtimes etimes ftimes gtimes
   /INDEX = Event 'Event identifier' (Occur)
   /KEEP  =  id
   /NULL  = DROP.

Variables to Cases
|-----------------------------|---------------------------|
|Output Created               |23-NOV-2008 01:16:42       |
|-----------------------------|---------------------------|
[LongForm]

Generated Variables
|-----|---------------------|
|Name |Label                |
|-----|---------------------|
|Event|Event identifier     |
|-----|---------------------|
|Occur|Occurrences of event |
|-----|---------------------|
Processing Statistics
|-------------|-|
|Variables In |8|
|Variables Out|3|
|-------------|-|



*  Drop the word "times" from the ends of the identifiers      ..... .

COMPUTE   #TmsIdx = INDEX(Event,'times').
IF        #TmsIdx GT 0
           EVENT   = SUBSTR(Event,1,#TmsIdx - 1).

*  Drop 0 counts                                               ..... .

SELECT IF Occur GT 0.

.  /**/   LIST /*-*/.
List
|-----------------------------|---------------------------|
|Output Created               |23-NOV-2008 01:16:43       |
|-----------------------------|---------------------------|
[LongForm]

id                Event  Occur

10102014005090011 f         2
10102049037050111 b         1
10102050002000011 b         3
10102050002060031 b         1
10102050022000121 a         3
10104029026020081 b         1
10105041041000121 a         3
10105041041000121 b         3
10105041041000121 d         1
10105041041000121 f         3
10105080041000081 f         1
10106015507000141 b         2
10106015507000141 f         2

Number of cases read:  13    Number of cases listed:  13


*  II.    Summarize, by type of event                                .

AGGREGATE OUTFILE=*
    /BREAK  = Event
    /Tot_HH  'Total households experiencing this event' = NU
    /Tot_Occ 'Total times this event occurred'          = SUM(Occur).

FORMATS Tot_HH Tot_Occ(F4).

LIST.

List
|-----------------------------|---------------------------|
|Output Created               |23-NOV-2008 01:16:44       |
|-----------------------------|---------------------------|
Event  Tot_HH Tot_Occ

a          2       6
b          6      11
d          1       1
f          4       8

Number of cases read:  4    Number of cases listed:  4
=============================
APPENDIX: Test data, and code
=============================
*  .................   Test data  - to post    ..................... .
NEW FILE.
DATA LIST LIST /
    id                atimes btimes ctimes dtimes etimes ftimes gtimes
   (A17,              7F3).
BEGIN DATA
    10102014005090011     0      0      0      0      0      2      0
    10102049037050111     0      1      0      0      0      0      0
    10102050002000011     0      3      0      0      0      0      0
    10102050002060031     0      1      0      0      0      0      0
    10102050022000121     3      0      0      0      0      0      0
    10102050023000131     0      0      0      0      0      0      0
    10104029026020081     0      1      0      0      0      0      0
    10104029047010131     0      0      0      0      0      0      0
    10105041041000121     3      3      0      1      0      3      0
    10105080031000071     0      0      0      0      0      0      0
    10105080041000081     0      0      0      0      0      1      0
    10105089031070131     0      0      0      0      0      0      0
    10105089031080141     0      0      0      0      0      0      0
    10106014020000021     0      0      0      0      0      0      0
    10106015035000111     0      0      0      0      0      0      0
    10106015036000121     0      0      0      0      0      0      0
    10106015507000141     0      2      0      0      0      2      0
    10106030019000071     0      0      0      0      0      0      0
    10107023054010301     0      0      0      0      0      0      0
    10107023096000361     0      0      0      0      0      0      0
END DATA.
DATASET NAME     TestData WINDOW=FRONT.

*  .................   Post after this point   ..................... .
*  ................................................................. .
LIST.

DATASET ACTIVATE TestData.
DATASET COPY     LongForm.
DATASET ACTIVATE LongForm WINDOW=FRONT.

*  I.     "Unroll" to a separate line for each occurrence count      .

*  Restructure, using VARSTOCASES                              ..... .

VARSTOCASES
   /MAKE Occur    'Occurrences of event'
    FROM atimes btimes ctimes dtimes etimes ftimes gtimes
   /INDEX = Event 'Event identifier' (Occur)
   /KEEP  =  id
   /NULL  = DROP.

*  Drop the word "times" from the ends of the identifiers      ..... .

COMPUTE   #TmsIdx = INDEX(Event,'times').
IF        #TmsIdx GT 0
           EVENT   = SUBSTR(Event,1,#TmsIdx - 1).

*  Drop 0 counts                                               ..... .

SELECT IF Occur GT 0.

.  /**/   LIST /*-*/.


*  II.    Summarize, by type of event                                .

AGGREGATE OUTFILE=*
    /BREAK  = Event
    /Tot_HH  'Total households experiencing this event' = NU
    /Tot_Occ 'Total times this event occurred'          = SUM(Occur).

FORMATS Tot_HH Tot_Occ(F4).

LIST.

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