Export to EXCEL - Align pivot tables -

[mils]

Hi,

I have the following tables and although I've managed to align the main table with the comparison (self test) table I would like to align "proportion" tables with "mean" tables and their respective comparison tables.

I'm asking this because when I export all pivot tables to an excel document, the tables are not "align". Ideally, I would like to see (Variable Name | Label | Statistics|) for both "proportion" and "mean" tables if its possible.

I hope it makes sense.


DEFINE !screen1 (variables = !CMDEND)
* Custom Tables.
CTABLES
  /VLABELS VARIABLES=!variables $Global  wec DISPLAY=LABEL
  /TABLE !variables[C] [COUNT F40.0, COLPCT.COUNT PCT40.1] BY $Global [C] + wec [C]  
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=!variables  EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE
  /CATEGORIES VARIABLES=$Global wec  ORDER=A KEY=VALUE EMPTY=INCLUDE
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES     CATEGORIES=ALLVISIBLE MERGE=NO.
!ENDDEFINE.

DEFINE !screen2 (variables = !CMDEND)
CTABLES
  /VLABELS VARIABLES=!variables $Global wec  DISPLAY=LABEL
  /TABLE !variables [S][MEAN f40.1]
BY  $Global [C] + wec [C]
  /SLABELS POSITION=ROW VISIBLE=no
  /CATEGORIES VARIABLES=$Global wec ORDER=A KEY=VALUE EMPTY=INCLUDE
  /TITLES    TITLE='table1'
  /COMPARETEST TYPE=MEAN ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES  CATEGORIES=ALLVISIBLE MEANSVARIANCE=ALLCATS MERGE=NO.
!ENDDEFINE.


Please, let me know if you need anything else.

[David Marso]

First off, I don't believe anyone is going to bother to run this under documented set of macros, attempt to replicate your export method and then come up with a shuffling idea.
1. No test data.
2. No sample macro call.
3. No example of Excel table.
Help others assist you by being proactive in providing an easily replicated context.
Meanwhile, perhaps explore OMS?
---

mils wrote

Hi,

I have the following tables and although I've managed to align the main table with the comparison (self test) table I would like to align "proportion" tables with "mean" tables and their respective comparison tables.

I'm asking this because when I export all pivot tables to an excel document, the tables are not "align". Ideally, I would like to see (Variable Name | Label | Statistics|) for both "proportion" and "mean" tables if its possible.

I hope it makes sense.


DEFINE !screen1 (variables = !CMDEND)
* Custom Tables.
CTABLES
  /VLABELS VARIABLES=!variables $Global  wec DISPLAY=LABEL
  /TABLE !variables[C] [COUNT F40.0, COLPCT.COUNT PCT40.1] BY $Global [C] + wec [C]  
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=!variables  EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE
  /CATEGORIES VARIABLES=$Global wec  ORDER=A KEY=VALUE EMPTY=INCLUDE
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES     CATEGORIES=ALLVISIBLE MERGE=NO.
!ENDDEFINE.

DEFINE !screen2 (variables = !CMDEND)
CTABLES
  /VLABELS VARIABLES=!variables $Global wec  DISPLAY=LABEL
  /TABLE !variables [S][MEAN f40.1]
BY  $Global [C] + wec [C]
  /SLABELS POSITION=ROW VISIBLE=no
  /CATEGORIES VARIABLES=$Global wec ORDER=A KEY=VALUE EMPTY=INCLUDE
  /TITLES    TITLE='table1'
  /COMPARETEST TYPE=MEAN ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES  CATEGORIES=ALLVISIBLE MEANSVARIANCE=ALLCATS MERGE=NO.
!ENDDEFINE.


Please, let me know if you need anything else.

[mils]

Hi,

You are completely right David. Below you can find an example with test data. I hope it helps

DATA LIST LIST
 / make (A15) mpg weight price .
BEGIN DATA.
"AMC Concord",22,2930,4099
"AMC Pacer",17,3350,4749
"AMC Spirit",22,2640,3799
"Buick Century",20, 3250,4816
"Buick Electra",15,4080,7827
END DATA.
compute qtotal=1.
exe.

* Custom Tables.
CTABLES
  /VLABELS VARIABLES=make qtotal DISPLAY=LABEL
  /TABLE make[C] [COUNT F40.0, COLPCT.COUNT PCT40.1] BY qtotal [C]  
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=make  EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE
  /CATEGORIES VARIABLES=qtotal  ORDER=A KEY=VALUE EMPTY=INCLUDE
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES     CATEGORIES=ALLVISIBLE MERGE=NO.


CTABLES
  /VLABELS VARIABLES=weight qtotal  DISPLAY=LABEL
  /TABLE weight [S][MEAN f40.1]
BY  qtotal [C]
  /SLABELS POSITION=ROW VISIBLE=no
  /CATEGORIES VARIABLES=qtotal ORDER=A KEY=VALUE EMPTY=INCLUDE
  /TITLES    TITLE='table1'
  /COMPARETEST TYPE=MEAN ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES  CATEGORIES=ALLVISIBLE MEANSVARIANCE=ALLCATS MERGE=NO.

I've also added and excel output where you can see that the tables are not aligned. I hope it helps.


output_example.xlsx

[Bruce Weaver]

List members who do not read via Nabble will (probably) not get the Excel file you attached.  Those who are interested can get it here:

http://spssx-discussion.1045642.n5.nabble.com/Export-to-EXCEL-Align-pivot-tables-td5729171.html#a5729185

mils wrote

Hi,

You are completely right David. Below you can find an example with test data. I hope it helps

DATA LIST LIST
 / make (A15) mpg weight price .
BEGIN DATA.
"AMC Concord",22,2930,4099
"AMC Pacer",17,3350,4749
"AMC Spirit",22,2640,3799
"Buick Century",20, 3250,4816
"Buick Electra",15,4080,7827
END DATA.
compute qtotal=1.
exe.

* Custom Tables.
CTABLES
  /VLABELS VARIABLES=make qtotal DISPLAY=LABEL
  /TABLE make[C] [COUNT F40.0, COLPCT.COUNT PCT40.1] BY qtotal [C]  
  /SLABELS POSITION=ROW VISIBLE=NO
  /CATEGORIES VARIABLES=make  EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE
  /CATEGORIES VARIABLES=qtotal  ORDER=A KEY=VALUE EMPTY=INCLUDE
  /COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES     CATEGORIES=ALLVISIBLE MERGE=NO.


CTABLES
  /VLABELS VARIABLES=weight qtotal  DISPLAY=LABEL
  /TABLE weight [S][MEAN f40.1]
BY  qtotal [C]
  /SLABELS POSITION=ROW VISIBLE=no
  /CATEGORIES VARIABLES=qtotal ORDER=A KEY=VALUE EMPTY=INCLUDE
  /TITLES    TITLE='table1'
  /COMPARETEST TYPE=MEAN ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN INCLUDEMRSETS=YES  CATEGORIES=ALLVISIBLE MEANSVARIANCE=ALLCATS MERGE=NO.

I've also added and excel output where you can see that the tables are not aligned. I hope it helps.


output_example.xlsx

[PRogman]

I don't understand what should be 'aligned'.
There is just 1 value for qtotal (=1 column).
In the 1st CTables command you specify 2 entries per make (count and colpct):

  /TABLE       make[C] [COUNT F40.0, COLPCT.COUNT PCT40.1]
      BY       qtotal [C]   

...and the totals above...=> 5 makes * 2 entries + 2 totals = 12 rows
In the 2nd CTables 1 entry for weight (mean):

 /TABLE       weight [S][MEAN f40.1]
     BY        qtotal [C] 

...no totals... 1 mean weight= 1 row.

If you want count and col.pct on the same row, use:

  /TABLE make [C] 
      BY qtotal [C][COUNT F40.0, COLPCT.COUNT PCT40.1]

Maybe a demo of layout of the wanted output?
/PR

[mils]

Hi,

I want the tables to be aligned in the excel export. If I create a "frequency" tables like the above with the comparisons of column proportions then is easy to align. In excel it will show as:

Column A  Column B Column C
var name   Label       Counts
                                 %

Column A  Column B Column C
var name   Label       sig testing

However, If I create a "mean" table like the above whit the comparisons of column means, I will get the following:

Column A  Column B Column C
var name   average

Column A  Column B Column C
var name   sig testing

I know I could do something clever with the var name or/and the label (like not showing it) but I would like to keep them

This is just an aesthetic issue. I'm happy to use a different format if someone have a better idea on how to create tables.

As an example (if you can open the attached excel document) I would like to have the variable qtotal in the same column (I hope this makes sense).

Thanks in advance.

[Jignesh Sutar]

Try something like this:

compute Dummy=1.
value labels Dummy 1 " ".
set tnumbers=labels tvars=labels.
CTABLES
   /VLABELS VARIABLES=weight qtotal  DISPLAY=LABEL /vlabels variables =Dummy display=none
   /TABLE Dummy[c]>weight [S][MEAN f40.1]
 BY  qtotal [C]
   /SLABELS POSITION=ROW VISIBLE=no
   /CATEGORIES VARIABLES=qtotal ORDER=A KEY=VALUE EMPTY=INCLUDE
  /TITLES    TITLE='table1'
   /COMPARETEST TYPE=MEAN ALPHA=0.05 ADJUST=NONE ORIGIN=COLUMN
 INCLUDEMRSETS=YES  CATEGORIES=ALLVISIBLE MEANSVARIANCE=ALLCATS MERGE=NO.

On 9 April 2015 at 17:07, mils <[hidden email]> wrote:

Hi,

I want the tables to be aligned in the excel export. If I create a
"frequency" tables like the above with the comparisons of column proportions
then is easy to align. In excel it will show as:

Column A  Column B Column C
var name   Label       Counts
                                 %

Column A  Column B Column C
var name   Label       sig testing

However, If I create a "mean" table like the above whit the comparisons of
column means, I will get the following:

Column A  Column B Column C
var name   average

Column A  Column B Column C
var name   sig testing

I know I could do something clever with the var name or/and the label (like
not showing it) but I would like to keep them

This is just an aesthetic issue. I'm happy to use a different format if
someone have a better idea on how to create tables.

As an example (if you can open the attached excel document) I would like to
have the variable qtotal in the same column (I hope this makes sense).

Thanks in advance.



-----
mils
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Export-to-EXCEL-Align-pivot-tables-tp5729171p5729189.html

Sent from the SPSSX Discussion mailing list archive at 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

[mils]

Hi Jignesh,

yes, that worked just as I wanted!

Thanks!