MULT RESP pivot tables in CTABLES?

classic Classic list List threaded Threaded
13 messages Options
Reply | Threaded
Open this post in threaded view
|

MULT RESP pivot tables in CTABLES?

John F Hall

I have been using MULT RESP to produce dichotomous tables, then editing them in pivot to get rid of the two columns under Responses (N and Percent) and sort the rows under Percent of Cases into descending order.  For example:

 

Dichotomy group tabulated at value 3.

 

Enough money for basic financial needs

"Arrears for bills and loans" b

(Items ranked in descending order)

arrears Frequencies

 

 

Percent of Cases

 

 

Arrears: Electricity, gas, fuel bills

35.1%

 

Arrears: Mortgage / Rent

34.6%

 

Arrears: LRates

33.8%

 

Arrears: LWater

25.7%

 

Arrears: Credit card payments

25.1%

 

Arrears: Telephone bills (including mobile phone, broadband)

25.1%

 

Arrears: TV Licence

24.2%

 

Arrears: Loans from Banks, Building Societies or Credit Unions

14.8%

 

Arrears: Hire purchase instalments or similar (e.g. mail order catalogues, car finance, interest-free credit etc)

14.3%

 

Arrears: Other loans/bills

11.2%

 

Arrears: Income Tax or VAT payments

4.6%

 

Arrears: Private education or health bills

1.6%

 

Arrears: Child Support or Maintenance

0.7%

 

N = 100%

2766

 

b Dichotomy group tabulated at value 3.

 

This is tricky and time-consuming.  Can CTABLES do the same thing?

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

PRogman
Yes, CTABLES can do that. This example uses the adl.sav sample dataset (to
work you may have to edit the folder path in the first FILE HANDLE). First
define the Multiple response set for later use in CTables.
I also added som clean-up if you are exporting the output to other media
(the  SPSSINC MODIFY OUTPUT).

HTH,
PRogman

FILE HANDLE SPSS_Examples /NAME='%SPSS_HOME%\Samples\English' .
FILE HANDLE Data          /NAME='SPSS_Examples\adl.sav' .

GET FILE = Data.

DATASET NAME Demo WINDOW=FRONT.

MRSETS
  /MDGROUP
   NAME           = $Spt
   LABEL          = 'Symptoms'
   CATEGORYLABELS = VARLABELS
   VARIABLES      = diabetic hypertns afib priorstr
   VALUE          = 1
  /DISPLAY
   NAME           = [$Spt]
.
CTABLES
  /VLABELS    VARIABLES = $Spt
              DISPLAY   = DEFAULT
  /TABLE      $Spt        [C][COLPCT.COUNT '% of cases' PCT8.1]
  /CATEGORIES VARIABLES = $Spt  EMPTY=INCLUDE ORDER=D KEY=COUNT
  /TITLES     TITLE     = 'Baseline Symptoms'
              CAPTION   = 'More than 1 symptom/patient is possible'
.
*Change or hide Procedure Title *.
SPSSINC MODIFY OUTPUT TITLES
  /IF COMMAND = "CTABLES" PROCESS  = PRECEDING
  /REPLACE ITEMS ITEMTITLE         = "Inclusion status"
  /VISIBILITY    VISIBLE           = ASIS  /*FALSE or ASIS */
  /PAGEBREAKS    BREAKBEFORETITLES = NO .




--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

John F Hall
Many thanks for this: it will take a while to get my head round the coding
and modify it for my data.  Busy day today: (soccer) Cup Final, (royal)
wedding, Golden Wedding of other grandparents.

John F Hall  MA (Cantab) Dip Ed (Dunelm)
[Retired academic survey researcher]

Email:          [hidden email]
Website:     Journeys in Survey Research
Course:       Survey Analysis Workshop (SPSS)
Research:   Subjective Social Indicators (Quality of Life)

-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of PRogman
Sent: 18 May 2018 13:28
To: [hidden email]
Subject: Re: MULT RESP pivot tables in CTABLES?

Yes, CTABLES can do that. This example uses the adl.sav sample dataset (to
work you may have to edit the folder path in the first FILE HANDLE). First
define the Multiple response set for later use in CTables.
I also added som clean-up if you are exporting the output to other media
(the  SPSSINC MODIFY OUTPUT).

HTH,
PRogman

FILE HANDLE SPSS_Examples /NAME='%SPSS_HOME%\Samples\English' .
FILE HANDLE Data          /NAME='SPSS_Examples\adl.sav' .

GET FILE = Data.

DATASET NAME Demo WINDOW=FRONT.

MRSETS
  /MDGROUP
   NAME           = $Spt
   LABEL          = 'Symptoms'
   CATEGORYLABELS = VARLABELS
   VARIABLES      = diabetic hypertns afib priorstr
   VALUE          = 1
  /DISPLAY
   NAME           = [$Spt]
.
CTABLES
  /VLABELS    VARIABLES = $Spt
              DISPLAY   = DEFAULT
  /TABLE      $Spt        [C][COLPCT.COUNT '% of cases' PCT8.1]
  /CATEGORIES VARIABLES = $Spt  EMPTY=INCLUDE ORDER=D KEY=COUNT
  /TITLES     TITLE     = 'Baseline Symptoms'
              CAPTION   = 'More than 1 symptom/patient is possible'
.
*Change or hide Procedure Title *.
SPSSINC MODIFY OUTPUT TITLES
  /IF COMMAND = "CTABLES" PROCESS  = PRECEDING
  /REPLACE ITEMS ITEMTITLE         = "Inclusion status"
  /VISIBILITY    VISIBLE           = ASIS  /*FALSE or ASIS */
  /PAGEBREAKS    BREAKBEFORETITLES = NO .




--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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

=====================
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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

John F Hall
In reply to this post by PRogman

Took a minute or two to find the adl.sav file, but your code provides exactly what I want;

 

Baseline Symptoms

 

% of cases

$Spt Symptoms

hypertns Hypertensive

84.6%

diabetic Diabetes mellitus

38.5%

afib Atrial fibrillation

10.3%

priorstr Prior stroke

(N = 100%)

2.6%

100

More than 1 symptom/patient is possible

 

I needed to add another row (N = 100%) obtained from Data View (usually displayed as Valid Cases in summary tables).  Will tweak later: time for Mrs H's wake-up cuppa

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

John F Hall
In reply to this post by PRogman

Actually (N = 100%) is 39 (100 – 61 0r 26+12+1) as per:

 

count symptoms1 = diabetic hypertns afib priorstr (1).

freq symptoms1.

 

 

symptoms1

 

Frequency

Percent

Valid Percent

Cumulative Percent

Valid

0

61

61.0

61.0

61.0

1

26

26.0

26.0

87.0

2

12

12.0

12.0

99.0

3

1

1.0

1.0

100.0

Total

100

100.0

100.0

 

 

mult resp groups

    symptoms "Baseline symptoms"

    (diabetic hypertns afib priorstr (1))

/freq symptoms.

Case Summary

 

Cases

Valid

Missing

Total

N

Percent

N

Percent

N

Percent

symptomsa

39

39.0%

61

61.0%

100

100.0%

a. Dichotomy group tabulated at value 1.

Baseline Symptoms

 

% of cases

 

$Spt Symptoms

hypertns Hypertensive

84.6%

 

diabetic Diabetes mellitus

38.5%

 

afib Atrial fibrillation

10.3%

 

priorstr Prior stroke

(N = 100%)

2.6%

39

 

More than 1 symptom/patient is possible

 

Is there way of modifying the Python syntax to pick up the 39 from somwhere and add a row to the table as above?

 

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

From: John F Hall <[hidden email]>
Sent: 19 May 2018 07:55
To: 'PRogman' <[hidden email]>; '[hidden email]' <[hidden email]>
Subject: RE: MULT RESP pivot tables in CTABLES?

 

Took a minute or two to find the adl.sav file, but your code provides exactly what I want;

 

Baseline Symptoms

 

% of cases

$Spt Symptoms

hypertns Hypertensive

84.6%

diabetic Diabetes mellitus

38.5%

afib Atrial fibrillation

10.3%

priorstr Prior stroke

(N = 100%)

2.6%

100

More than 1 symptom/patient is possible

 

I needed to add another row (N = 100%) obtained from Data View (usually displayed as Valid Cases in summary tables).  Will tweak later: time for Mrs H's wake-up cuppa

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

John F Hall
In reply to this post by PRogman

Brilliant: only had to change four things:

 

MRSETS

  /MDGROUP

   NAME           = $Spt

   LABEL          = 'Consumer items already has'

   CATEGORYLABELS = VARLABELS

   VARIABLES      = car to hdtv

   VALUE          = 1

  /DISPLAY

   NAME           = [$Spt]

.

CTABLES

  /VLABELS    VARIABLES = $Spt

              DISPLAY   = DEFAULT

  /TABLE      $Spt        [C][COLPCT.COUNT '% of cases' PCT8.1]

  /CATEGORIES VARIABLES = $Spt  EMPTY=INCLUDE ORDER=D KEY=COUNT

  /TITLES     TITLE     = 'Items owned'

              CAPTION   = 'More than 1 item is possible'

.

*Change or hide Procedure Title *.

SPSSINC MODIFY OUTPUT TITLES

  /IF COMMAND = "CTABLES" PROCESS  = PRECEDING

  /REPLACE ITEMS ITEMTITLE         = "Inclusion status"

  /VISIBILITY    VISIBLE           = ASIS  /*FALSE or ASIS */

  /PAGEBREAKS    BREAKBEFORETITLES = NO .

 

Items owned

 

% of cases

$Spt Consumer items

TV Television

98.6%

washing Washing machine

98.3%

Curtains Curtains or window blinds

97.8%

phone Telephone

97.2%

tablech Table and chairs at which all the family can eat

89.7%

computer Home computer

84.7%

Internet Internet connection at home

83.6%

nodamp Damp-free home

79.7%

car Car

79.2%

Insurance Home Insurance

78.2%

HDTV High definition Plasma or LCD TV

72.9%

paytv Pay TV (e.g. Sky, Virgin)

59.7%

sparebed A spare bedroom

55.6%

dishwash Dishwasher

47.6%

cartwo Second car or vehicle (not motorcycle)

33.1%

bathtwo Second bathroom (with shower or bath)

31.0%

alarm Home security system (e.g. burglar alarm)

26.9%

sechome A second home

7.7%

More than 1 item is possible

 

COUNT consumer = car to hdtv (1).

FREQ says valid N = 12097, but there are 4306 cases with value 0 so some forensics needed to verify the actual base N.

 

Oh the joys of secondary analysis of other people's surveys, in this case

Poverty and Social Exclusion in the UK 2012: http://www.bristol.ac.uk/sps/research/projects/completed/2015/rc1453/

Data downloaded from UK Data Service. SN: 7879, http://doi.org/10.5255/UKDA-SN-7879-1

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

From: John F Hall <[hidden email]>
Sent: 19 May 2018 10:08
To: 'PRogman' <[hidden email]>; '[hidden email]' <[hidden email]>
Subject: RE: MULT RESP pivot tables in CTABLES?

 

Actually (N = 100%) is 39 (100 – 61 0r 26+12+1) as per:

 

count symptoms1 = diabetic hypertns afib priorstr (1).

freq symptoms1.

 

 

symptoms1

 

Frequency

Percent

Valid Percent

Cumulative Percent

Valid

0

61

61.0

61.0

61.0

1

26

26.0

26.0

87.0

2

12

12.0

12.0

99.0

3

1

1.0

1.0

100.0

Total

100

100.0

100.0

 

 

mult resp groups

    symptoms "Baseline symptoms"

    (diabetic hypertns afib priorstr (1))

/freq symptoms.

Case Summary

 

Cases

Valid

Missing

Total

N

Percent

N

Percent

N

Percent

symptomsa

39

39.0%

61

61.0%

100

100.0%

a. Dichotomy group tabulated at value 1.

Baseline Symptoms

 

% of cases

 

$Spt Symptoms

hypertns Hypertensive

84.6%

 

diabetic Diabetes mellitus

38.5%

 

afib Atrial fibrillation

10.3%

 

priorstr Prior stroke

(N = 100%)

2.6%

39

 

More than 1 symptom/patient is possible

 

Is there way of modifying the Python syntax to pick up the 39 from somwhere and add a row to the table as above?

 

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

From: John F Hall <[hidden email]>
Sent: 19 May 2018 07:55
To: 'PRogman' <[hidden email]>; '[hidden email]' <[hidden email]>
Subject: RE: MULT RESP pivot tables in CTABLES?

 

Took a minute or two to find the adl.sav file, but your code provides exactly what I want;

 

Baseline Symptoms

 

% of cases

$Spt Symptoms

hypertns Hypertensive

84.6%

diabetic Diabetes mellitus

38.5%

afib Atrial fibrillation

10.3%

priorstr Prior stroke

(N = 100%)

2.6%

100

More than 1 symptom/patient is possible

 

I needed to add another row (N = 100%) obtained from Data View (usually displayed as Valid Cases in summary tables).  Will tweak later: time for Mrs H's wake-up cuppa

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

PRogman
In reply to this post by John F Hall
The table gets tricky as one count is about multiple responses and the other
is about cases. CTABLES can do it but will not place it in the same column.
If you remove lines 3,4,6,7,8 in the /TABLE subcommand (i.e. all RESPONSES,
and sum cases %)  you will get fairly close to your preferred output.

 
FILE HANDLE SPSS_Examples /NAME='%SPSS_HOME%\Samples\English' .
FILE HANDLE Data          /NAME='SPSS_Examples\adl.sav' .

GET FILE = Data.

DATASET NAME Demo WINDOW=FRONT.

MRSETS
  /MDGROUP
   NAME           = $Spt
   LABEL          = 'Symptoms'
   CATEGORYLABELS = VARLABELS
   VARIABLES      = diabetic hypertns afib priorstr
   VALUE          = 1
  /DISPLAY
   NAME           = [$Spt]
.
CTABLES
  /VLABELS    VARIABLES  = $Spt
              DISPLAY    = DEFAULT
  /TABLE      $Spt       [C][COUNT             'Cases \n(n)'       F8.0
                            ,COLPCT.COUNT      'Cases \n(%)'     PCT8.1
                            ,RESPONSES         'Responses \n(n)'   F8.0
                            ,COLPCT.RESPONSES  'Responses \n(%)' PCT8.1
                            ,TOTALS [COUNT               F8.0
                                    ,COLPCT.COUNT      PCT8.1
                                    ,RESPONSES           F8.0
                                    ,COLPCT.RESPONSES  PCT8.1
                                    ]
                            ]
  /CATEGORIES VARIABLES = $Spt  ORDER=D KEY=COUNT EMPTY=INCLUDE TOTAL=YES
POSITION=AFTER LABEL='N=100%'
  /TITLES     TITLE     = 'Baseline Symptoms'
              CAPTION   = 'Dichotomy group tabulated at value 1'
                          'More than 1 symptom/responder is possible'
.

OUTPUT:
                       Baseline Symptoms
-------------------------------------------------------------
                           Cases   Cases Responses  Responses
                            (n)     (%)     (n)        (%)
-------------------------------------------------------------
Symptoms Hypertensive         33   84,6%        33      62,3%
         Diabetes mellitus    15   38,5%        15      28,3%
         Atrial fibrillation   4   10,3%         4       7,5%
         Prior stroke          1    2,6%         1       1,9%
         N=100%               39  100,0%        53     100,0%
-------------------------------------------------------------
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible

/PRogman



John F Hall wrote

> Actually (N = 100%) is 39 (100 - 61 0r 26+12+1) as per:
>
> count symptoms1 = diabetic hypertns afib priorstr (1).
> freq symptoms1.
>
> symptoms1
>
>                 Frequency  Percent   Valid Percent   Cumulative Percent
> Valid  0         61   61.0   61.0   61.0
>          1         26   26.0   26.0   87.0
>          2        12   12.0   12.0   99.0
>          3         1    1.0    1.0    100.0
> Total          100   100.0  100.0
>
> mult resp groups
>     symptoms "Baseline symptoms"
>     (diabetic hypertns afib priorstr (1))
> /freq symptoms.
>
> Case Summary
> Cases               Valid          Missing          Total
>                     N Percent     N Percent      N Percent
> symptomsa   39   39.0%   61   61.0%   100   100.0%
> a. Dichotomy group tabulated at value 1.
>
>
> Baseline Symptoms
> % of cases
> $Spt Symptoms
>    hypertns Hypertensive   84.6%
>    diabetic Diabetes mellitus   38.5%
>    afib Atrial fibrillation   10.3%
>    priorstr Prior stroke   2.6%
>    (N = 100%)   39
> More than 1 symptom/patient is possible
>
> Is there way of modifying the Python syntax to pick up the 39 from
> somewhere
> and add a row to the table as above?
>
>
> John F Hall  MA (Cantab) Dip Ed (Dunelm)
>
> [Retired academic survey researcher]





--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

=====================
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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

John F Hall

Peder

Thanks so much for this.  

 

The table I need is for those experiencing one or more health problems:

 

count healthprobs = jobprob to holsprob (1).

var lab healthprobs 'Number of items for which health causes problems'.

freq healthprobs.

 

healthprobs Number of items for which health causes problems

 

Frequency

Percent

Valid Percent

Cumulative Percent

Valid

0

1048

74.3

74.3

74.3

1

127

9.0

9.0

83.3

2

80

5.7

5.7

88.9

3

60

4.3

4.3

93.2

4

51

3.6

3.6

96.8

5

25

1.8

1.8

98.6

6

13

0.9

0.9

99.5

7

7

0.5

0.5

100.0

Total

1411

100.0

100.0

 

 

After tweaking your syntax to replace diabetic hypertns afib priorstr with jobprob to holsprob, I produced the following table:

 

Health causes problems with:

 

Cases

(n)

Cases

(%)

Responses

(n)

Responses

(%)

$Spt Health causes problems with:

homeprob Health causes problems looking after hom

183

50.4%

183

19.8%

socprob Health causes problems with social life

163

44.9%

163

17.7%

intprob Health causes problems with hobbies etc

154

42.4%

154

16.7%

holsprob Health causes problems with holidays

137

37.7%

137

14.8%

hlprob Health causes problems with home life

104

28.7%

104

11.3%

jobprob Health causes problems with job

94

25.9%

94

10.2%

sexprob Health causes problems with sex life

88

24.2%

88

9.5%

N=100%

363

100.0%

923

100.0%

Dichotomy group tabulated at value 1

More than 1 symptom/responder is possible

I don't really need the Responses columns, so I tweaked the syntax again to remove the Responses columns:

(Why does SPSS revert to TNR 12pt after copying output, when I only want Arial?)

Tables after copying: modified to 9pt single space:

 

Health causes problems with:

 

Cases

(n)

Cases

(%)

$Spt Health causes problems with:

homeprob Health causes problems looking after hom

183

50.4%

socprob Health causes problems with social life

163

44.9%

intprob Health causes problems with hobbies etc

154

42.4%

holsprob Health causes problems with holidays

137

37.7%

hlprob Health causes problems with home life

104

28.7%

jobprob Health causes problems with job

94

25.9%

sexprob Health causes problems with sex life

88

24.2%

N=100%

363

100.0%

Dichotomy group tabulated at value 1

More than 1 symptom/responder is possible

John (There goes TNR 12pt again!)

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of PRogman
Sent: 22 May 2018 22:15
To: [hidden email]
Subject: Re: MULT RESP pivot tables in CTABLES?

 

The table gets tricky as one count is about multiple responses and the other is about cases. CTABLES can do it but will not place it in the same column.

If you remove lines 3,4,6,7,8 in the /TABLE subcommand (i.e. all RESPONSES, and sum cases %)  you will get fairly close to your preferred output.

 

FILE HANDLE SPSS_Examples /NAME='%SPSS_HOME%\Samples\English' .

FILE HANDLE Data          /NAME='SPSS_Examples\adl.sav' .

 

GET FILE = Data.

 

DATASET NAME Demo WINDOW=FRONT.

 

MRSETS

  /MDGROUP

   NAME           = $Spt

   LABEL          = 'Symptoms'

   CATEGORYLABELS = VARLABELS

   VARIABLES      = diabetic hypertns afib priorstr

   VALUE          = 1

  /DISPLAY

   NAME           = [$Spt]

.

CTABLES

  /VLABELS    VARIABLES  = $Spt

              DISPLAY    = DEFAULT

  /TABLE      $Spt       [C][COUNT             'Cases \n(n)'       F8.0

                            ,COLPCT.COUNT      'Cases \n(%)'     PCT8.1

                            ,RESPONSES         'Responses \n(n)'   F8.0

                            ,COLPCT.RESPONSES  'Responses \n(%)' PCT8.1

                            ,TOTALS [COUNT               F8.0

                                    ,COLPCT.COUNT      PCT8.1

                                    ,RESPONSES           F8.0

                                    ,COLPCT.RESPONSES  PCT8.1

                                    ]

                            ]

  /CATEGORIES VARIABLES = $Spt  ORDER=D KEY=COUNT EMPTY=INCLUDE TOTAL=YES POSITION=AFTER LABEL='N=100%'

  /TITLES     TITLE     = 'Baseline Symptoms'

              CAPTION   = 'Dichotomy group tabulated at value 1'

                          'More than 1 symptom/responder is possible'

.

 

OUTPUT:

                       Baseline Symptoms

-------------------------------------------------------------

                           Cases   Cases Responses  Responses

                            (n)     (%)     (n)        (%)

-------------------------------------------------------------

Symptoms Hypertensive         33   84,6%        33      62,3%

         Diabetes mellitus    15   38,5%        15      28,3%

         Atrial fibrillation   4   10,3%         4       7,5%

         Prior stroke          1    2,6%         1       1,9%

         N=100%               39  100,0%        53     100,0%

-------------------------------------------------------------

Dichotomy group tabulated at value 1

More than 1 symptom/responder is possible

 

/PRogman

 

 

 

John F Hall wrote

> Actually (N = 100%) is 39 (100 - 61 0r 26+12+1) as per:

>

> count symptoms1 = diabetic hypertns afib priorstr (1).

> freq symptoms1.

>

> symptoms1

>

>                 Frequency  Percent   Valid Percent   Cumulative Percent

> Valid  0         61   61.0   61.0   61.0

>          1         26   26.0   26.0   87.0

>          2        12   12.0   12.0   99.0

>          3         1    1.0    1.0    100.0

> Total          100   100.0  100.0

>

> mult resp groups

>     symptoms "Baseline symptoms"

>     (diabetic hypertns afib priorstr (1)) /freq symptoms.

>

> Case Summary

> Cases               Valid          Missing          Total

>                     N Percent     N Percent      N Percent

> symptomsa   39   39.0%   61   61.0%   100   100.0%

> a. Dichotomy group tabulated at value 1.

>

>

> Baseline Symptoms

> % of cases

> $Spt Symptoms

>    hypertns Hypertensive   84.6%

>    diabetic Diabetes mellitus   38.5%

>    afib Atrial fibrillation   10.3%

>    priorstr Prior stroke   2.6%

>    (N = 100%)   39

> More than 1 symptom/patient is possible

>

> Is there way of modifying the Python syntax to pick up the 39 from

> somewhere and add a row to the table as above?

>

>

> John F Hall  MA (Cantab) Dip Ed (Dunelm)

>

> [Retired academic survey researcher]

 

 

 

 

 

--

Sent from: http://spssx-discussion.1045642.n5.nabble.com/

 

=====================

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

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

Kornbrot, Diana
You might be better off with cross tabs: 
causes as rows, number of items as columns

has advantage of tables that transfer better and option for associated block graph

All the procedures in REPORTS have horrible output as they predate SPSS discovering the tab key
It would take about 1 week to update the output, and would be a handy exercise for an intern - UT
unsurprisingly IBM is keener on new end analyses that  updating old ones
SPSS is to be commended for the new stuff, but I do wish they would make legacy procedures more user friendly

I have to use REPORTS because need see and kurtosis summarise according to many categorisations. Feel very frustrated by tidying up the fghastly TEXT output using EXCEL

best

Diana

On 23 May 2018, at 09:01, John F Hall <[hidden email]> wrote:

Peder
Thanks so much for this.  
 
The table I need is for those experiencing one or more health problems:
 
count healthprobs = jobprob to holsprob (1).
var lab healthprobs 'Number of items for which health causes problems'.
freq healthprobs.
 
healthprobs Number of items for which health causes problems
 
Frequency
Percent
Valid Percent
Cumulative Percent
Valid
0
1048
74.3
74.3
74.3
1
127
9.0
9.0
83.3
2
80
5.7
5.7
88.9
3
60
4.3
4.3
93.2
4
51
3.6
3.6
96.8
5
25
1.8
1.8
98.6
6
13
0.9
0.9
99.5
7
7
0.5
0.5
100.0
Total
1411
100.0
100.0
 
 
After tweaking your syntax to replace diabetic hypertns afib priorstr with jobprob to holsprob, I produced the following table:
 
Health causes problems with:
 
Cases 
(n)
Cases 
(%)
Responses 
(n)
Responses 
(%)
$Spt Health causes problems with:
homeprob Health causes problems looking after hom
183
50.4%
183
19.8%
socprob Health causes problems with social life
163
44.9%
163
17.7%
intprob Health causes problems with hobbies etc
154
42.4%
154
16.7%
holsprob Health causes problems with holidays
137
37.7%
137
14.8%
hlprob Health causes problems with home life
104
28.7%
104
11.3%
jobprob Health causes problems with job
94
25.9%
94
10.2%
sexprob Health causes problems with sex life
88
24.2%
88
9.5%
N=100%
363
100.0%
923
100.0%
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
I don't really need the Responses columns, so I tweaked the syntax again to remove the Responses columns:
(Why does SPSS revert to TNR 12pt after copying output, when I only want Arial?)
Tables after copying: modified to 9pt single space:
 
Health causes problems with:
 
Cases 
(n)
Cases 
(%)
$Spt Health causes problems with:
homeprob Health causes problems looking after hom
183
50.4%
socprob Health causes problems with social life
163
44.9%
intprob Health causes problems with hobbies etc
154
42.4%
holsprob Health causes problems with holidays
137
37.7%
hlprob Health causes problems with home life
104
28.7%
jobprob Health causes problems with job
94
25.9%
sexprob Health causes problems with sex life
88
24.2%
N=100%
363
100.0%
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
John (There goes TNR 12pt again!)
 
John F Hall  MA (Cantab) Dip Ed (Dunelm)
[Retired academic survey researcher]
 
Email:          [hidden email]
Website:     Journeys in Survey Research
Course:       Survey Analysis Workshop (SPSS)
Research:   Subjective Social Indicators (Quality of Life)
 
-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of PRogman
Sent: 22 May 2018 22:15
To: [hidden email]
Subject: Re: MULT RESP pivot tables in CTABLES?
 
The table gets tricky as one count is about multiple responses and the other is about cases. CTABLES can do it but will not place it in the same column.
If you remove lines 3,4,6,7,8 in the /TABLE subcommand (i.e. all RESPONSES, and sum cases %)  you will get fairly close to your preferred output.
 
FILE HANDLE SPSS_Examples /NAME='%SPSS_HOME%\Samples\English' .
FILE HANDLE Data          /NAME='SPSS_Examples\adl.sav' .
 
GET FILE = Data. 
 
DATASET NAME Demo WINDOW=FRONT.
 
MRSETS
  /MDGROUP 
   NAME           = $Spt
   LABEL          = 'Symptoms' 
   CATEGORYLABELS = VARLABELS 
   VARIABLES      = diabetic hypertns afib priorstr 
   VALUE          = 1
  /DISPLAY
   NAME           = [$Spt]
.
CTABLES
  /VLABELS    VARIABLES  = $Spt
              DISPLAY    = DEFAULT
  /TABLE      $Spt       [C][COUNT             'Cases \n(n)'       F8.0
                            ,COLPCT.COUNT      'Cases \n(%)'     PCT8.1
                            ,RESPONSES         'Responses \n(n)'   F8.0 
                            ,COLPCT.RESPONSES  'Responses \n(%)' PCT8.1
                            ,TOTALS [COUNT               F8.0
                                    ,COLPCT.COUNT      PCT8.1 
                                    ,RESPONSES           F8.0 
                                    ,COLPCT.RESPONSES  PCT8.1 
                                    ] 
                            ]
  /CATEGORIES VARIABLES = $Spt  ORDER=D KEY=COUNT EMPTY=INCLUDE TOTAL=YES POSITION=AFTER LABEL='N=100%'
  /TITLES     TITLE     = 'Baseline Symptoms'
              CAPTION   = 'Dichotomy group tabulated at value 1' 
                          'More than 1 symptom/responder is possible'
.
 
OUTPUT:
                       Baseline Symptoms
-------------------------------------------------------------
                           Cases   Cases Responses  Responses
                            (n)     (%)     (n)        (%)
-------------------------------------------------------------
Symptoms Hypertensive         33   84,6%        33      62,3%
         Diabetes mellitus    15   38,5%        15      28,3%
         Atrial fibrillation   4   10,3%         4       7,5%
         Prior stroke          1    2,6%         1       1,9%
         N=100%               39  100,0%        53     100,0%
-------------------------------------------------------------
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
 
/PRogman
 
 
 
John F Hall wrote
> Actually (N = 100%) is 39 (100 - 61 0r 26+12+1) as per:
> 
> count symptoms1 = diabetic hypertns afib priorstr (1).
> freq symptoms1.
> 
> symptoms1
> 
>                 Frequency  Percent   Valid Percent   Cumulative Percent
> Valid  0         61   61.0   61.0   61.0
>          1         26   26.0   26.0   87.0
>          2        12   12.0   12.0   99.0
>          3         1    1.0    1.0    100.0
> Total          100   100.0  100.0
> 
> mult resp groups
>     symptoms "Baseline symptoms"
>     (diabetic hypertns afib priorstr (1)) /freq symptoms.
> 
> Case Summary
> Cases               Valid          Missing          Total
>                     N Percent     N Percent      N Percent
> symptomsa   39   39.0%   61   61.0%   100   100.0%
> a. Dichotomy group tabulated at value 1.
> 
> 
> Baseline Symptoms
> % of cases
> $Spt Symptoms
>    hypertns Hypertensive   84.6%
>    diabetic Diabetes mellitus   38.5%
>    afib Atrial fibrillation   10.3%
>    priorstr Prior stroke   2.6%
>    (N = 100%)   39
> More than 1 symptom/patient is possible
> 
> Is there way of modifying the Python syntax to pick up the 39 from 
> somewhere and add a row to the table as above?
> 
> 
> John F Hall  MA (Cantab) Dip Ed (Dunelm)
> 
> [Retired academic survey researcher]
 
 
 
 
 
--
 
=====================
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
===================== 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

_____________________________________
Professor Diana Kornbrot
Mobile
+44 (0) 7403 18 16 12
Work
University of Hertfordshire
College Lane, Hatfield, Hertfordshire AL10 9AB, UK
+44 (0) 170 728 4626
[hidden email]
http://dianakornbrot.wordpress.com/
http://go.herts.ac.uk/Diana_Kornbrot
skype:  kornbrotme
Home
19 Elmhurst Avenue
London N2 0LT, UK
+44 (0) 208 444 2081                                                   
 ------------------------------------------------------------                                    



===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

John F Hall

I have to work without the original 1991 questionnaire: it would make life much easier, but I have been unable to track it down.  The table is not about independent and dependent variables, but answers to a question which must have been something like:

 

I am going to read you a list of things that people are sometime unable to do because of health problems.  For each one could you tell me whether you have problems doing it (PROMPT: because of your health)

[Coded 1 = Yes, 2 = No, but no value labels in the file]

 

Your job

Looking after your home

Your social life

Your home life

Your sex life

Your hobbies etc

Your holidays

 

I modified the table to:

 

 

Cases 

(%)

$Spt Health causes problems with:

homeprob Health causes problems looking after hom

50.4%

socprob Health causes problems with social life

44.9%

intprob Health causes problems with hobbies etc

42.4%

holsprob Health causes problems with holidays

37.7%

hlprob Health causes problems with home life

28.7%

jobprob Health causes problems with job

25.9%

sexprob Health causes problems with sex life

24.2%

N=100%

363

 

Still a bit cluttered, but I can always change the output options to labels only.  The 1990 Health and Lifestyle Survey is not mine and I have to use the existing variable names and labels.  If I delete "Health causes problems" in the labels, the table will be clearer, but the labels will be less easy to understand in the Data Editor. 

 

Peder's modified syntax is working wonders with the 2012 Poverty and Social Exclusion survey, again not my survey, but at least full documentation is available.  The 1990 survey was conducted using public funds, but the "owning" body no longer exists.  ESRC now insists on full preservation and deposit of all research materials funded by them.  Universities are now insisting on deposit of all research data by MPhil and PhD students.  About time too.  These are valuable resources: failure to preserve them is a criminal waste of money (and loss of transparency to expose poor research).

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

From: SPSSX(r) Discussion <[hidden email]> On Behalf Of Kornbrot, Diana
Sent: 23 May 2018 10:20
To: [hidden email]
Subject: Re: MULT RESP pivot tables in CTABLES?

 

You might be better off with cross tabs: 

causes as rows, number of items as columns

 

has advantage of tables that transfer better and option for associated block graph

 

All the procedures in REPORTS have horrible output as they predate SPSS discovering the tab key

It would take about 1 week to update the output, and would be a handy exercise for an intern - UT

unsurprisingly IBM is keener on new end analyses that  updating old ones

SPSS is to be commended for the new stuff, but I do wish they would make legacy procedures more user friendly

 

I have to use REPORTS because need see and kurtosis summarise according to many categorisations. Feel very frustrated by tidying up the fghastly TEXT output using EXCEL

 

best

 

Diana



On 23 May 2018, at 09:01, John F Hall <[hidden email]> wrote:

 

Peder

Thanks so much for this.  

 

The table I need is for those experiencing one or more health problems:

 

count healthprobs = jobprob to holsprob (1).

var lab healthprobs 'Number of items for which health causes problems'.

freq healthprobs.

 

healthprobs Number of items for which health causes problems

 

Frequency

Percent

Valid Percent

Cumulative Percent

Valid

0

1048

74.3

74.3

74.3

1

127

9.0

9.0

83.3

2

80

5.7

5.7

88.9

3

60

4.3

4.3

93.2

4

51

3.6

3.6

96.8

5

25

1.8

1.8

98.6

6

13

0.9

0.9

99.5

7

7

0.5

0.5

100.0

Total

1411

100.0

100.0

 

 

After tweaking your syntax to replace diabetic hypertns afib priorstr with jobprob to holsprob, I produced the following table:

 

Health causes problems with:

 

Cases 

(n)

Cases 

(%)

Responses 

(n)

Responses 

(%)

$Spt Health causes problems with:

homeprob Health causes problems looking after hom

183

50.4%

183

19.8%

socprob Health causes problems with social life

163

44.9%

163

17.7%

intprob Health causes problems with hobbies etc

154

42.4%

154

16.7%

holsprob Health causes problems with holidays

137

37.7%

137

14.8%

hlprob Health causes problems with home life

104

28.7%

104

11.3%

jobprob Health causes problems with job

94

25.9%

94

10.2%

sexprob Health causes problems with sex life

88

24.2%

88

9.5%

N=100%

363

100.0%

923

100.0%

Dichotomy group tabulated at value 1

More than 1 symptom/responder is possible

I don't really need the Responses columns, so I tweaked the syntax again to remove the Responses columns:

(Why does SPSS revert to TNR 12pt after copying output, when I only want Arial?)

Tables after copying: modified to 9pt single space:

 

Health causes problems with:

 

Cases 

(n)

Cases 

(%)

$Spt Health causes problems with:

homeprob Health causes problems looking after hom

183

50.4%

socprob Health causes problems with social life

163

44.9%

intprob Health causes problems with hobbies etc

154

42.4%

holsprob Health causes problems with holidays

137

37.7%

hlprob Health causes problems with home life

104

28.7%

jobprob Health causes problems with job

94

25.9%

sexprob Health causes problems with sex life

88

24.2%

N=100%

363

100.0%

Dichotomy group tabulated at value 1

More than 1 symptom/responder is possible

John (There goes TNR 12pt again!)

 

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:          [hidden email]

Website:     Journeys in Survey Research

Course:       Survey Analysis Workshop (SPSS)

Research:   Subjective Social Indicators (Quality of Life)

 

-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of PRogman
Sent: 22 May 2018 22:15
To: [hidden email]
Subject: Re: MULT RESP pivot tables in CTABLES?

 

The table gets tricky as one count is about multiple responses and the other is about cases. CTABLES can do it but will not place it in the same column.

If you remove lines 3,4,6,7,8 in the /TABLE subcommand (i.e. all RESPONSES, and sum cases %)  you will get fairly close to your preferred output.

 

FILE HANDLE SPSS_Examples /NAME='%SPSS_HOME%\Samples\English' .

FILE HANDLE Data          /NAME='SPSS_Examples\adl.sav' .

 

GET FILE = Data. 

 

DATASET NAME Demo WINDOW=FRONT.

 

MRSETS

  /MDGROUP 

   NAME           = $Spt

   LABEL          = 'Symptoms' 

   CATEGORYLABELS = VARLABELS 

   VARIABLES      = diabetic hypertns afib priorstr 

   VALUE          = 1

  /DISPLAY

   NAME           = [$Spt]

.

CTABLES

  /VLABELS    VARIABLES  = $Spt

              DISPLAY    = DEFAULT

  /TABLE      $Spt       [C][COUNT             'Cases \n(n)'       F8.0

                            ,COLPCT.COUNT      'Cases \n(%)'     PCT8.1

                            ,RESPONSES         'Responses \n(n)'   F8.0 

                            ,COLPCT.RESPONSES  'Responses \n(%)' PCT8.1

                            ,TOTALS [COUNT               F8.0

                                    ,COLPCT.COUNT      PCT8.1 

                                    ,RESPONSES           F8.0 

                                    ,COLPCT.RESPONSES  PCT8.1 

                                    ] 

                            ]

  /CATEGORIES VARIABLES = $Spt  ORDER=D KEY=COUNT EMPTY=INCLUDE TOTAL=YES POSITION=AFTER LABEL='N=100%'

  /TITLES     TITLE     = 'Baseline Symptoms'

              CAPTION   = 'Dichotomy group tabulated at value 1' 

                          'More than 1 symptom/responder is possible'

.

 

OUTPUT:

                       Baseline Symptoms

-------------------------------------------------------------

                           Cases   Cases Responses  Responses

                            (n)     (%)     (n)        (%)

-------------------------------------------------------------

Symptoms Hypertensive         33   84,6%        33      62,3%

         Diabetes mellitus    15   38,5%        15      28,3%

         Atrial fibrillation   4   10,3%         4       7,5%

         Prior stroke          1    2,6%         1       1,9%

         N=100%               39  100,0%        53     100,0%

-------------------------------------------------------------

Dichotomy group tabulated at value 1

More than 1 symptom/responder is possible

 

/PRogman

 

 

 

John F Hall wrote

> Actually (N = 100%) is 39 (100 - 61 0r 26+12+1) as per:

> 

> count symptoms1 = diabetic hypertns afib priorstr (1).

> freq symptoms1.

> 

> symptoms1

> 

>                 Frequency  Percent   Valid Percent   Cumulative Percent

> Valid  0         61   61.0   61.0   61.0

>          1         26   26.0   26.0   87.0

>          2        12   12.0   12.0   99.0

>          3         1    1.0    1.0    100.0

> Total          100   100.0  100.0

> 

> mult resp groups

>     symptoms "Baseline symptoms"

>     (diabetic hypertns afib priorstr (1)) /freq symptoms.

> 

> Case Summary

> Cases               Valid          Missing          Total

>                     N Percent     N Percent      N Percent

> symptomsa   39   39.0%   61   61.0%   100   100.0%

> a. Dichotomy group tabulated at value 1.

> 

> 

> Baseline Symptoms

> % of cases

> $Spt Symptoms

>    hypertns Hypertensive   84.6%

>    diabetic Diabetes mellitus   38.5%

>    afib Atrial fibrillation   10.3%

>    priorstr Prior stroke   2.6%

>    (N = 100%)   39

> More than 1 symptom/patient is possible

> 

> Is there way of modifying the Python syntax to pick up the 39 from 

> somewhere and add a row to the table as above?

> 

> 

> John F Hall  MA (Cantab) Dip Ed (Dunelm)

> 

> [Retired academic survey researcher]

 

 

 

 

 

--

 

=====================

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

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

 

_____________________________________
Professor Diana Kornbrot
Mobile
+44 (0) 7403 18 16 12
Work
University of Hertfordshire
College Lane, Hatfield, Hertfordshire AL10 9AB, UK
+44 (0) 170 728 4626
[hidden email]
http://dianakornbrot.wordpress.com/
http://go.herts.ac.uk/Diana_Kornbrot
skype:  kornbrotme
Home
19 Elmhurst Avenue
London N2 0LT, UK
+44 (0) 208 444 2081                                                   
 ------------------------------------------------------------                                    

 

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

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

Mario Giesel-2
I agree, Diana, upgrading CTABLES would be fabulous.
This seems like a relevant satisfaction driver to me.
 
Mario Giesel
Munich, Germany


John F Hall <[hidden email]> schrieb am 11:23 Mittwoch, 23.Mai 2018:


I have to work without the original 1991 questionnaire: it would make life much easier, but I have been unable to track it down.  The table is not about independent and dependent variables, but answers to a question which must have been something like:
 
I am going to read you a list of things that people are sometime unable to do because of health problems.  For each one could you tell me whether you have problems doing it (PROMPT: because of your health)
[Coded 1 = Yes, 2 = No, but no value labels in the file]
 
Your job
Looking after your home
Your social life
Your home life
Your sex life
Your hobbies etc
Your holidays
 
I modified the table to:
 
 
Cases 
(%)
$Spt Health causes problems with:
homeprob Health causes problems looking after hom
50.4%
socprob Health causes problems with social life
44.9%
intprob Health causes problems with hobbies etc
42.4%
holsprob Health causes problems with holidays
37.7%
hlprob Health causes problems with home life
28.7%
jobprob Health causes problems with job
25.9%
sexprob Health causes problems with sex life
24.2%
N=100%
363
 
Still a bit cluttered, but I can always change the output options to labels only.  The 1990 Health and Lifestyle Survey is not mine and I have to use the existing variable names and labels.  If I delete "Health causes problems" in the labels, the table will be clearer, but the labels will be less easy to understand in the Data Editor. 
 
Peder's modified syntax is working wonders with the 2012 Poverty and Social Exclusion survey, again not my survey, but at least full documentation is available.  The 1990 survey was conducted using public funds, but the "owning" body no longer exists.  ESRC now insists on full preservation and deposit of all research materials funded by them.  Universities are now insisting on deposit of all research data by MPhil and PhD students.  About time too.  These are valuable resources: failure to preserve them is a criminal waste of money (and loss of transparency to expose poor research).
 
John F Hall  MA (Cantab) Dip Ed (Dunelm)
[Retired academic survey researcher]
 
Email:          [hidden email]
 
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of Kornbrot, Diana
Sent: 23 May 2018 10:20
To: [hidden email]
Subject: Re: MULT RESP pivot tables in CTABLES?
 
You might be better off with cross tabs: 
causes as rows, number of items as columns
 
has advantage of tables that transfer better and option for associated block graph
 
All the procedures in REPORTS have horrible output as they predate SPSS discovering the tab key
It would take about 1 week to update the output, and would be a handy exercise for an intern - UT
unsurprisingly IBM is keener on new end analyses that  updating old ones
SPSS is to be commended for the new stuff, but I do wish they would make legacy procedures more user friendly
 
I have to use REPORTS because need see and kurtosis summarise according to many categorisations. Feel very frustrated by tidying up the fghastly TEXT output using EXCEL
 
best
 
Diana


On 23 May 2018, at 09:01, John F Hall <[hidden email]> wrote:
 
Peder
Thanks so much for this.  
 
The table I need is for those experiencing one or more health problems:
 
count healthprobs = jobprob to holsprob (1).
var lab healthprobs 'Number of items for which health causes problems'.
freq healthprobs.
 
healthprobs Number of items for which health causes problems
 
Frequency
Percent
Valid Percent
Cumulative Percent
Valid
0
1048
74.3
74.3
74.3
1
127
9.0
9.0
83.3
2
80
5.7
5.7
88.9
3
60
4.3
4.3
93.2
4
51
3.6
3.6
96.8
5
25
1.8
1.8
98.6
6
13
0.9
0.9
99.5
7
7
0.5
0.5
100.0
Total
1411
100.0
100.0
 
 
After tweaking your syntax to replace diabetic hypertns afib priorstr with jobprob to holsprob, I produced the following table:
 
Health causes problems with:
 
Cases 
(n)
Cases 
(%)
Responses 
(n)
Responses 
(%)
$Spt Health causes problems with:
homeprob Health causes problems looking after hom
183
50.4%
183
19.8%
socprob Health causes problems with social life
163
44.9%
163
17.7%
intprob Health causes problems with hobbies etc
154
42.4%
154
16.7%
holsprob Health causes problems with holidays
137
37.7%
137
14.8%
hlprob Health causes problems with home life
104
28.7%
104
11.3%
jobprob Health causes problems with job
94
25.9%
94
10.2%
sexprob Health causes problems with sex life
88
24.2%
88
9.5%
N=100%
363
100.0%
923
100.0%
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
I don't really need the Responses columns, so I tweaked the syntax again to remove the Responses columns:
(Why does SPSS revert to TNR 12pt after copying output, when I only want Arial?)
Tables after copying: modified to 9pt single space:
 
Health causes problems with:
 
Cases 
(n)
Cases 
(%)
$Spt Health causes problems with:
homeprob Health causes problems looking after hom
183
50.4%
socprob Health causes problems with social life
163
44.9%
intprob Health causes problems with hobbies etc
154
42.4%
holsprob Health causes problems with holidays
137
37.7%
hlprob Health causes problems with home life
104
28.7%
jobprob Health causes problems with job
94
25.9%
sexprob Health causes problems with sex life
88
24.2%
N=100%
363
100.0%
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
John (There goes TNR 12pt again!)
 
John F Hall  MA (Cantab) Dip Ed (Dunelm)
[Retired academic survey researcher]
 
Email:          [hidden email]
Website:     Journeys in Survey Research
Course:       Survey Analysis Workshop (SPSS)
Research:   Subjective Social Indicators (Quality of Life)
 
-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of PRogman
Sent: 22 May 2018 22:15
To: [hidden email]
Subject: Re: MULT RESP pivot tables in CTABLES?
 
The table gets tricky as one count is about multiple responses and the other is about cases. CTABLES can do it but will not place it in the same column.
If you remove lines 3,4,6,7,8 in the /TABLE subcommand (i.e. all RESPONSES, and sum cases %)  you will get fairly close to your preferred output.
 
FILE HANDLE SPSS_Examples /NAME='%SPSS_HOME%\Samples\English' .
FILE HANDLE Data          /NAME='SPSS_Examples\adl.sav' .
 
GET FILE = Data. 
 
DATASET NAME Demo WINDOW=FRONT.
 
MRSETS
  /MDGROUP 
   NAME           = $Spt
   LABEL          = 'Symptoms' 
   CATEGORYLABELS = VARLABELS 
   VARIABLES      = diabetic hypertns afib priorstr 
   VALUE          = 1
  /DISPLAY
   NAME           = [$Spt]
.
CTABLES
  /VLABELS    VARIABLES  = $Spt
              DISPLAY    = DEFAULT
  /TABLE      $Spt       [C][COUNT             'Cases \n(n)'       F8.0
                            ,COLPCT.COUNT      'Cases \n(%)'     PCT8.1
                            ,RESPONSES         'Responses \n(n)'   F8.0 
                            ,COLPCT.RESPONSES  'Responses \n(%)' PCT8.1
                            ,TOTALS [COUNT               F8.0
                                    ,COLPCT.COUNT      PCT8.1 
                                    ,RESPONSES           F8.0 
                                    ,COLPCT.RESPONSES  PCT8.1 
                                    ] 
                            ]
  /CATEGORIES VARIABLES = $Spt  ORDER=D KEY=COUNT EMPTY=INCLUDE TOTAL=YES POSITION=AFTER LABEL='N=100%'
  /TITLES     TITLE     = 'Baseline Symptoms'
              CAPTION   = 'Dichotomy group tabulated at value 1' 
                          'More than 1 symptom/responder is possible'
.
 
OUTPUT:
                       Baseline Symptoms
-------------------------------------------------------------
                           Cases   Cases Responses  Responses
                            (n)     (%)     (n)        (%)
-------------------------------------------------------------
Symptoms Hypertensive         33   84,6%        33      62,3%
         Diabetes mellitus    15   38,5%        15      28,3%
         Atrial fibrillation   4   10,3%         4       7,5%
         Prior stroke          1    2,6%         1       1,9%
         N=100%               39  100,0%        53     100,0%
-------------------------------------------------------------
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
 
/PRogman
 
 
 
John F Hall wrote
> Actually (N = 100%) is 39 (100 - 61 0r 26+12+1) as per:
> 
> count symptoms1 = diabetic hypertns afib priorstr (1).
> freq symptoms1.
> 
> symptoms1
> 
>                 Frequency  Percent   Valid Percent   Cumulative Percent
> Valid  0         61   61.0   61.0   61.0
>          1         26   26.0   26.0   87.0
>          2        12   12.0   12.0   99.0
>          3         1    1.0    1.0    100.0
> Total          100   100.0  100.0
> 
> mult resp groups
>     symptoms "Baseline symptoms"
>     (diabetic hypertns afib priorstr (1)) /freq symptoms.
> 
> Case Summary
> Cases               Valid          Missing          Total
>                     N Percent     N Percent      N Percent
> symptomsa   39   39.0%   61   61.0%   100   100.0%
> a. Dichotomy group tabulated at value 1.
> 
> 
> Baseline Symptoms
> % of cases
> $Spt Symptoms
>    hypertns Hypertensive   84.6%
>    diabetic Diabetes mellitus   38.5%
>    afib Atrial fibrillation   10.3%
>    priorstr Prior stroke   2.6%
>    (N = 100%)   39
> More than 1 symptom/patient is possible
> 
> Is there way of modifying the Python syntax to pick up the 39 from 
> somewhere and add a row to the table as above?
> 
> 
> John F Hall  MA (Cantab) Dip Ed (Dunelm)
> 
> [Retired academic survey researcher]
 
 
 
 
 
--
 
=====================
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
===================== 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
 
_____________________________________
Professor Diana Kornbrot
Mobile
+44 (0) 7403 18 16 12
Work
University of Hertfordshire
College Lane, Hatfield, Hertfordshire AL10 9AB, UK
+44 (0) 170 728 4626
[hidden email]
http://dianakornbrot.wordpress.com/
http://go.herts.ac.uk/Diana_Kornbrot
skype:  kornbrotme
Home
19 Elmhurst Avenue
London N2 0LT, UK
+44 (0) 208 444 2081                                                   
 ------------------------------------------------------------                                    

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


===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

Jon Peck
In reply to this post by Kornbrot, Diana
The REPORT output was never converted to pivot table format, because the layout flexibility it has isn't well suited for pivot tables, and users expressed a desire to retain the plain text output.  However, many of the features, including kurtosis and such are available in the SUMMARIZE procedure, and the output is structurally similar to typical REPORT output.

The REPORT output does not use tabs, because tabs are treated different by different applications, and they can mess things up a lot.  However, if you just need to convert spaces to tabs according to some specific layout, that could be automated on the SPSS side.

On Wed, May 23, 2018 at 2:19 AM, Kornbrot, Diana <[hidden email]> wrote:
You might be better off with cross tabs: 
causes as rows, number of items as columns

has advantage of tables that transfer better and option for associated block graph

All the procedures in REPORTS have horrible output as they predate SPSS discovering the tab key
It would take about 1 week to update the output, and would be a handy exercise for an intern - UT
unsurprisingly IBM is keener on new end analyses that  updating old ones
SPSS is to be commended for the new stuff, but I do wish they would make legacy procedures more user friendly

I have to use REPORTS because need see and kurtosis summarise according to many categorisations. Feel very frustrated by tidying up the fghastly TEXT output using EXCEL

best

Diana

On 23 May 2018, at 09:01, John F Hall <[hidden email]> wrote:

Peder
Thanks so much for this.  
 
The table I need is for those experiencing one or more health problems:
 
count healthprobs = jobprob to holsprob (1).
var lab healthprobs 'Number of items for which health causes problems'.
freq healthprobs.
 
healthprobs Number of items for which health causes problems
 
Frequency
Percent
Valid Percent
Cumulative Percent
Valid
0
1048
74.3
74.3
74.3
1
127
9.0
9.0
83.3
2
80
5.7
5.7
88.9
3
60
4.3
4.3
93.2
4
51
3.6
3.6
96.8
5
25
1.8
1.8
98.6
6
13
0.9
0.9
99.5
7
7
0.5
0.5
100.0
Total
1411
100.0
100.0
 
 
After tweaking your syntax to replace diabetic hypertns afib priorstr with jobprob to holsprob, I produced the following table:
 
Health causes problems with:
 
Cases 
(n)
Cases 
(%)
Responses 
(n)
Responses 
(%)
$Spt Health causes problems with:
homeprob Health causes problems looking after hom
183
50.4%
183
19.8%
socprob Health causes problems with social life
163
44.9%
163
17.7%
intprob Health causes problems with hobbies etc
154
42.4%
154
16.7%
holsprob Health causes problems with holidays
137
37.7%
137
14.8%
hlprob Health causes problems with home life
104
28.7%
104
11.3%
jobprob Health causes problems with job
94
25.9%
94
10.2%
sexprob Health causes problems with sex life
88
24.2%
88
9.5%
N=100%
363
100.0%
923
100.0%
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
I don't really need the Responses columns, so I tweaked the syntax again to remove the Responses columns:
(Why does SPSS revert to TNR 12pt after copying output, when I only want Arial?)
Tables after copying: modified to 9pt single space:
 
Health causes problems with:
 
Cases 
(n)
Cases 
(%)
$Spt Health causes problems with:
homeprob Health causes problems looking after hom
183
50.4%
socprob Health causes problems with social life
163
44.9%
intprob Health causes problems with hobbies etc
154
42.4%
holsprob Health causes problems with holidays
137
37.7%
hlprob Health causes problems with home life
104
28.7%
jobprob Health causes problems with job
94
25.9%
sexprob Health causes problems with sex life
88
24.2%
N=100%
363
100.0%
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
John (There goes TNR 12pt again!)
 
John F Hall  MA (Cantab) Dip Ed (Dunelm)
[Retired academic survey researcher]
 
Email:          [hidden email]
Website:     Journeys in Survey Research
Course:       Survey Analysis Workshop (SPSS)
Research:   Subjective Social Indicators (Quality of Life)
 
-----Original Message-----
From: SPSSX(r) Discussion <[hidden email]> On Behalf Of PRogman
Sent: 22 May 2018 22:15
To: [hidden email]
Subject: Re: MULT RESP pivot tables in CTABLES?
 
The table gets tricky as one count is about multiple responses and the other is about cases. CTABLES can do it but will not place it in the same column.
If you remove lines 3,4,6,7,8 in the /TABLE subcommand (i.e. all RESPONSES, and sum cases %)  you will get fairly close to your preferred output.
 
FILE HANDLE SPSS_Examples /NAME='%SPSS_HOME%\Samples\English' .
FILE HANDLE Data          /NAME='SPSS_Examples\adl.sav' .
 
GET FILE = Data. 
 
DATASET NAME Demo WINDOW=FRONT.
 
MRSETS
  /MDGROUP 
   NAME           = $Spt
   LABEL          = 'Symptoms' 
   CATEGORYLABELS = VARLABELS 
   VARIABLES      = diabetic hypertns afib priorstr 
   VALUE          = 1
  /DISPLAY
   NAME           = [$Spt]
.
CTABLES
  /VLABELS    VARIABLES  = $Spt
              DISPLAY    = DEFAULT
  /TABLE      $Spt       [C][COUNT             'Cases \n(n)'       F8.0
                            ,COLPCT.COUNT      'Cases \n(%)'     PCT8.1
                            ,RESPONSES         'Responses \n(n)'   F8.0 
                            ,COLPCT.RESPONSES  'Responses \n(%)' PCT8.1
                            ,TOTALS [COUNT               F8.0
                                    ,COLPCT.COUNT      PCT8.1 
                                    ,RESPONSES           F8.0 
                                    ,COLPCT.RESPONSES  PCT8.1 
                                    ] 
                            ]
  /CATEGORIES VARIABLES = $Spt  ORDER=D KEY=COUNT EMPTY=INCLUDE TOTAL=YES POSITION=AFTER LABEL='N=100%'
  /TITLES     TITLE     = 'Baseline Symptoms'
              CAPTION   = 'Dichotomy group tabulated at value 1' 
                          'More than 1 symptom/responder is possible'
.
 
OUTPUT:
                       Baseline Symptoms
-------------------------------------------------------------
                           Cases   Cases Responses  Responses
                            (n)     (%)     (n)        (%)
-------------------------------------------------------------
Symptoms Hypertensive         33   84,6%        33      62,3%
         Diabetes mellitus    15   38,5%        15      28,3%
         Atrial fibrillation   4   10,3%         4       7,5%
         Prior stroke          1    2,6%         1       1,9%
         N=100%               39  100,0%        53     100,0%
-------------------------------------------------------------
Dichotomy group tabulated at value 1
More than 1 symptom/responder is possible
 
/PRogman
 
 
 
John F Hall wrote
> Actually (N = 100%) is 39 (100 - 61 0r 26+12+1) as per:
> 
> count symptoms1 = diabetic hypertns afib priorstr (1).
> freq symptoms1.
> 
> symptoms1
> 
>                 Frequency  Percent   Valid Percent   Cumulative Percent
> Valid  0         61   61.0   61.0   61.0
>          1         26   26.0   26.0   87.0
>          2        12   12.0   12.0   99.0
>          3         1    1.0    1.0    100.0
> Total          100   100.0  100.0
> 
> mult resp groups
>     symptoms "Baseline symptoms"
>     (diabetic hypertns afib priorstr (1)) /freq symptoms.
> 
> Case Summary
> Cases               Valid          Missing          Total
>                     N Percent     N Percent      N Percent
> symptomsa   39   39.0%   61   61.0%   100   100.0%
> a. Dichotomy group tabulated at value 1.
> 
> 
> Baseline Symptoms
> % of cases
> $Spt Symptoms
>    hypertns Hypertensive   84.6%
>    diabetic Diabetes mellitus   38.5%
>    afib Atrial fibrillation   10.3%
>    priorstr Prior stroke   2.6%
>    (N = 100%)   39
> More than 1 symptom/patient is possible
> 
> Is there way of modifying the Python syntax to pick up the 39 from 
> somewhere and add a row to the table as above?
> 
> 
> John F Hall  MA (Cantab) Dip Ed (Dunelm)
> 
> [Retired academic survey researcher]
 
 
 
 
 
--
 
=====================
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
===================== 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

_____________________________________
Professor Diana Kornbrot
Mobile
+44 (0) 7403 18 16 12
Work
University of Hertfordshire
College Lane, Hatfield, Hertfordshire AL10 9AB, UK
+44 (0) 170 728 4626
[hidden email]
http://dianakornbrot.wordpress.com/
http://go.herts.ac.uk/Diana_Kornbrot
skype:  kornbrotme
Home
19 Elmhurst Avenue
London N2 0LT, UK
+44 (0) 208 444 2081                                                   
 ------------------------------------------------------------                                    



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



--
Jon K Peck
[hidden email]

===================== 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
Reply | Threaded
Open this post in threaded view
|

Re: MULT RESP pivot tables in CTABLES?

PRogman
In reply to this post by John F Hall
If you change the option DISPLAY = DEFAULT in the subcommand /VLABELS to
DISPLAY = NONE you will get rid of the label column (i.e. 'Symptoms') in the
table. It will not change anything in the Data Editor.
/PRogman


...
Still a bit cluttered, but I can always change the output options to labels
only.  The 1990 Health and Lifestyle Survey is not mine and I have to use
the existing variable names and labels.  If I delete "Health causes
problems" in the labels, the table will be clearer, but the labels will be
less easy to understand in the Data Editor.  
...

John F Hall  MA (Cantab) Dip Ed (Dunelm)

[Retired academic survey researcher]

 

Email:           &lt;mailto:johnfhall@&gt; johnfhall@

Website:      &lt;http://surveyresearch.weebly.com/&gt; Journeys in Survey
Research

Course:
&lt;http://surveyresearch.weebly.com/1-survey-analysis-workshop-spss.html&gt;
Survey Analysis Workshop (SPSS)

Research:
&lt;http://surveyresearch.weebly.com/3-subjective-social-indicators-quality-of-
life.html&gt; Subjective Social Indicators (Quality of Life)

 



--
Sent from: http://spssx-discussion.1045642.n5.nabble.com/

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