Custom tables to keep only total columns

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

Custom tables to keep only total columns

Rajeshms
Dear All,

I am generating table using custom table with the following syntax :

CTABLES
  /VLABELS VARIABLES=Constituents OptivaNo ofSubjectscompletedthetest 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest Product_Type Weight DISPLAY=LABEL
  /TABLE Constituents [C] BY OptivaNo [C][COUNT F40.0] + ofSubjectscompletedthetest [S][SUM] + 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest [S][SUM] + Product_Type [C] > Weight [S][MAXIMUM]
  /CATEGORIES VARIABLES=Constituents Product_Type ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /CATEGORIES VARIABLES=OptivaNo ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER
  /TITLES
    TITLE='HRIPT INCI Profile'.

Where for the variable “OptivaNo“ we just want the total count column only which is displayed at last. Every time when we generate this table we are manually removing the count columns for the variable “OptivaNo” till Total count. How can we write a code where only total count column remains. 

Can anyone help me getting this as this will save a lot of time in deleting all other count columns. Thanks every one. 

Regards,

Rajesh M S




===================== 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
|  
Report Content as Inappropriate

Re: Custom tables to keep only total columns

Rajeshms
Hi taras,

Thanks. But variable " OptivaNo" is string variable. 

Thanks

On Mar 3, 2017 2:44 PM, "Taras Koniukhov" <[hidden email]> wrote:
Sorry small correction. Add: period / var name. 

*=======try this=======...
do if not sysmis(OptivaNo) and not missing(OptivaNo).
  compute validOptivaNo = 1.
else.
  compute validOptivaNo = $sysmis.
end if.
variable label validOptivaNo "All Valid values from OptivaNo".
value label validOptivaNo 1 "All Valid values from OptivaNo".
execute.

CTABLES
  /VLABELS VARIABLES=Constituents validOptivaNo ofSubjectscompletedthetest 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest Product_Type Weight DISPLAY=LABEL
  /TABLE Constituents [C] BY validOptivaNo [C][COUNT F40.0] + ofSubjectscompletedthetest [S][SUM] + 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest [S][SUM] + Product_Type [C] > Weight [S][MAXIMUM]
  /CATEGORIES VARIABLES=Constituents Product_Type ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /TITLES TITLE='HRIPT INCI Profile'.

*======================...

2017-03-03 11:09 GMT+02:00 Taras Koniukhov <[hidden email]>:
Hi,

As an option
add new dichotomy (Boolean) variable from  "OptivaNo" (if not missing/sysmis value then 1, else sysmis).
Then instead of OptivaNo, add new dichotomy variable in table task.

*=======try this=======...
do if not sysmis(OptivaNo) and not missing(OptivaNo).
  compute validOptivaNo = 1.
else 
  compute validOptivaNo = $sysmis.
end if.
variable label validOptivaNo "All Valid values from OptivaNo".
value label validOptivaNo 1 "All Valid values from OptivaNo".
execute.

CTABLES
  /VLABELS VARIABLES=Constituents OptivaNo ofSubjectscompletedthetest 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest Product_Type Weight DISPLAY=LABEL
  /TABLE Constituents [C] BY validOptivaNo [C][COUNT F40.0] + ofSubjectscompletedthetest [S][SUM] + 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest [S][SUM] + Product_Type [C] > Weight [S][MAXIMUM]
  /CATEGORIES VARIABLES=Constituents Product_Type ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /TITLES TITLE='HRIPT INCI Profile'.

*======================...


2017-03-03 10:54 GMT+02:00 Rajeshms <[hidden email]>:
Dear All,

I am generating table using custom table with the following syntax :

CTABLES
  /VLABELS VARIABLES=Constituents OptivaNo ofSubjectscompletedthetest 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest Product_Type Weight DISPLAY=LABEL
  /TABLE Constituents [C] BY OptivaNo [C][COUNT F40.0] + ofSubjectscompletedthetest [S][SUM] + 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest [S][SUM] + Product_Type [C] > Weight [S][MAXIMUM]
  /CATEGORIES VARIABLES=Constituents Product_Type ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /CATEGORIES VARIABLES=OptivaNo ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER
  /TITLES
    TITLE='HRIPT INCI Profile'.

Where for the variable “OptivaNo“ we just want the total count column only which is displayed at last. Every time when we generate this table we are manually removing the count columns for the variable “OptivaNo” till Total count. How can we write a code where only total count column remains. 

Can anyone help me getting this as this will save a lot of time in deleting all other count columns. Thanks every one. 

Regards,

Rajesh M S




===================== 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
|  
Report Content as Inappropriate

Re: Custom tables to keep only total columns

Art Kendall


The suggested syntax uses
 compute validOptivaNo = $sysmis.

If the variable were numeric, it would be better practice to use
numeric validOptivaNo (f7).
missing values validOptivaNo (-1).
...
 compute validOptivaNo = -1.

One of the strengths of SPSS is the distinction between system missing and user missing.
System missing is best reserved for the situation where the system is unable to follow you orders.
It is an"oops" signal in debugging your syntax.
When your syntax-checking colleague double checks the work ONE quality check is that no system missing values exist in the dataset.

User missing means that the reason for missingness is known to the user.

Since you call the ID variable OptivaNo is it actually a number held as a string?
Does it contain non-numeric characters? If  not why not use an N format?

As a string variable. does blank mean missing? Could the rest of you syntax deal with
see if something like this helps
data list list /ID (a10).
begin data
""
" "

123456
1234567890
2345678901
end data.

numeric validOptivaNo (f2).

missing values validOptivaNo (-1).
var label validOptivaNo 'Whether test complete or not'.
value labels validOptivaNo
    1 'Yes complete'
    -1 'No not complete'.

do if ID ne "".
    compute validOptivaNo =1.
ELSE.
    compute validOptivaNo =-1.
end if.
list.

 Could the rest of your syntax deal with validOptivaNo as
value labels validOptivaNo
    1 'Yes complete'
    2 'No not complete'.





 
Art Kendall
Social Research Consultants
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Custom tables to keep only total columns

Jon Peck
In reply to this post by Rajeshms
You can do this by using a hiding subtotal instead of the regular total.  Here is an example.
CTABLES
  /TABLE educ BY jobcat
  /CATEGORIES VARIABLES=jobcat [OTHERNM HSUBTOTAL='Total'].

By using OTHERNM in the category list, it will automatically include all the categories found.  HSUBTOTAL applies to all preceding categories since the previous subtotal, which means all categories in this case.

On Fri, Mar 3, 2017 at 1:54 AM, Rajeshms <[hidden email]> wrote:
Dear All,

I am generating table using custom table with the following syntax :

CTABLES
  /VLABELS VARIABLES=Constituents OptivaNo ofSubjectscompletedthetest 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest Product_Type Weight DISPLAY=LABEL
  /TABLE Constituents [C] BY OptivaNo [C][COUNT F40.0] + ofSubjectscompletedthetest [S][SUM] + 
    ofSUBJECTSwithConfirmedallergyinHRIPTtest [S][SUM] + Product_Type [C] > Weight [S][MAXIMUM]
  /CATEGORIES VARIABLES=Constituents Product_Type ORDER=A KEY=VALUE EMPTY=EXCLUDE
  /CATEGORIES VARIABLES=OptivaNo ORDER=A KEY=VALUE EMPTY=EXCLUDE TOTAL=YES POSITION=AFTER
  /TITLES
    TITLE='HRIPT INCI Profile'.

Where for the variable “OptivaNo“ we just want the total count column only which is displayed at last. Every time when we generate this table we are manually removing the count columns for the variable “OptivaNo” till Total count. How can we write a code where only total count column remains. 

Can anyone help me getting this as this will save a lot of time in deleting all other count columns. Thanks every one. 

Regards,

Rajesh M S




===================== 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
|  
Report Content as Inappropriate

Ctables

emma78
In reply to this post by Rajeshms
Hi,
another problem :-)

I use this ctable syntax
DEFINE !ctable8(row=!CHAREND('/') /col=!CMDEND)
CTABLES  
 /VLABELS VARIABLES= !row !col DISPLAY=DEFAULT  
 /TABLE !HEAD(!row) !DO !v !IN (!TAIL(!row))  [  COUNT, MEAN NEQUAL40.2, TOTALS [MEAN NEQUAL40.2] ] + !v !DOEND
        BY  (!HEAD(!col) !DO !v !IN (!TAIL(!col)) + !v !DOEND )
 /CATEGORIES VARIABLES= !row   EMPTY=EXCLUDE TOTAL=YES  POSITION=after
 /CATEGORIES VARIABLES=!col ORDER=A KEY=VALUE EMPTY=exCLUDE TOTAL=YES POSITION=BEFORE
/COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=None ORIGIN=COLUMN INCLUDEMRSETS=YES
    CATEGORIES=ALLVISIBLE MERGE=NO
!ENDDEFINE.

!ctable8 row=v_38  v_39  v_40  v_41  v_42  v_43  v_44  v_45/col=Altersgruppen v_9 ZG v_46.

I try to get only the means and count numbers.
The problem is the last row in the table, for this variable I do not get the mean in the right format, only without decimal and without the 'N=...'.

I tried many things but it doesn't work, has anybody an idea?



Thank you!
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ctables

Jon Peck
The first problem is that, oddly, the syntax in your email does not run for me when I copy it to a syntax window.  The reason is that many of the blanks in it are actually not the normal blank character (code x20) but, rather, the non-breaking space (code xA0), which is not recognized as a blank in syntax.  Presumably the original on your system has x20 blanks, but somewhere in the transmission this is getting messed up.

After I fixed that, the syntax ran but gave many errors like
TABLE: Variable v_38. TOTAL cannot be specified for scale variables.

However, the original problem is due to an issue with the macro.  After running SET MPRINT ON, you can see that the statistics specification for the last variable is missing, so you get default statistics instead of COUNT and MEAN.  You can see that the last part of the TABLE subcommand is

v_44 [ COUNT, MEAN NEQUAL40.2, TOTALS [MEAN NEQUAL40.2] ] + v_45
BY ( Altersgruppen + v_9 + ZG + v_46 ).

Fixing the macro to included the statistics for the last variable would resolve that problem.  Off course, I would do all this with Python :-)

Note also that since totals are ignored because of the variable measurement level, you could just omit TOTALS [MEAN NEQUAL40.2]  in the specification and get rid of all those error messages.  And, as I mentioned offline, you could also factor the statistics specification out of the table expression.  If you do that, the macro doesn't help much, since, effectively, the CTABLES command comes down to
CTABLES
/TABLE (v_38+v_39+v_40+v_41+v_42_v_43+v_44+v_45)[COUNT, MEAN NEQUAL40.2] BY Altersgruppen + v_9 + ZG + v_46

-Jon


On Thu, Mar 16, 2017 at 6:49 AM, emma78 <[hidden email]> wrote:
Hi,
another problem :-)

I use this ctable syntax
DEFINE !ctable8(row=!CHAREND('/') /col=!CMDEND)
CTABLES
 /VLABELS VARIABLES= !row !col DISPLAY=DEFAULT
 /TABLE !HEAD(!row) !DO !v !IN (!TAIL(!row))  [  COUNT, MEAN NEQUAL40.2,
TOTALS [MEAN NEQUAL40.2] ] + !v !DOEND
        BY  (!HEAD(!col) !DO !v !IN (!TAIL(!col)) + !v !DOEND )
 /CATEGORIES VARIABLES= !row   EMPTY=EXCLUDE TOTAL=YES  POSITION=after
 /CATEGORIES VARIABLES=!col ORDER=A KEY=VALUE EMPTY=exCLUDE TOTAL=YES
POSITION=BEFORE
/COMPARETEST TYPE=PROP ALPHA=0.05 ADJUST=None ORIGIN=COLUMN
INCLUDEMRSETS=YES
    CATEGORIES=ALLVISIBLE MERGE=NO
!ENDDEFINE.

!ctable8 row=v_38  v_39  v_40  v_41  v_42  v_43  v_44
v_45/col=Altersgruppen v_9 ZG v_46.

I try to get only the means and count numbers.
The problem is the last row in the table, for this variable I do not get the
mean in the right format, only without decimal and without the 'N=...'.

I tried many things but it doesn't work, has anybody an idea?



Thank you!



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Custom-tables-to-keep-only-total-columns-tp5733930p5733993.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



--
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
|  
Report Content as Inappropriate

Re: Ctables

emma78
Ok, thanks, I got it :-)
Just one last question  ;-)
Is it doable with ctables to have the SLABELS POSITION for
Count in column
and for mean in row?

So that the mean value stands in the bottom of the table and not in a new column but count stands in column?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ctables

Jon Peck
I'm not sure what that table would look like, but in order to create proper labelling, CTABLES only allows the statistics to be in one dimension.  It does allow custom statistics for totals, and you can put the same variable in a table twice with different statistics.

Maybe something like  this is close to what you want, but I would need to see a mockup to be sure.
CTABLES
  /TABLE v_38 [S][COUNT F40.0] + v_39 [S][COUNT F40.0] + v_40 [S][COUNT F40.0] + v_41 [S][COUNT
    F40.0] + v_42 [S][COUNT F40.0] + v_38 [MEAN] + v_39 [MEAN] + v_40 [MEAN] + v_41 [MEAN] + v_42
    [MEAN] + v_43 [MEAN] BY Altersgruppen
  /SLABELS POSITION=ROW
  /CATEGORIES VARIABLES=Altersgruppen ORDER=A KEY=VALUE EMPTY=INCLUDE TOTAL=YES POSITION=BEFORE.

You can use STATS TABLE CALC to add rows or columns based on values in the cells.

On Fri, Mar 17, 2017 at 9:30 AM, emma78 <[hidden email]> wrote:
Ok, thanks, I got it :-)
Just one last question  ;-)
Is it doable with ctables to have the SLABELS POSITION for
Count in column
and for mean in row?

So that the mean value stands in the bottom of the table and not in a new
column but count stands in column?



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Custom-tables-to-keep-only-total-columns-tp5733930p5734000.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



--
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
|  
Report Content as Inappropriate

Re: Ctables

emma78
Ok if I only have only count I can say slables row and then I have the same output without puting them twice.

But what if I have count and % and a mean

I can not set it twice, it doesn't make a difference..
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ctables

emma78
HI,
is it possible to have different statistics for a subtotal and a total?

So that I got fot example count and colpct for the subotal and only count for the 'real' total?


Thanks in advance!
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ctables

Jon Peck
CTABLES requires the same statistics for totals and subtotals.  However, you can get around this by not requesting a total and then adding the same variable to the table changing the measurement level to scale and the statistic to count.  Here is an example.  The educ variable entered both as categorical and again as scale.

CTABLES
  /TABLE educ [C][COUNT F40.0, ROWPCT.COUNT PCT40.1, TOTALS[COUNT F40.0, COLPCT.COUNT PCT40.1]] +
    educ [S][COUNT F40.0] BY jobcat [C]
  /CATEGORIES VARIABLES=educ [8, 12, 14, 15, 16, SUBTOTAL, 17, 18, 19, 20, SUBTOTAL, 21, OTHERNM]
    EMPTY=INCLUDE POSITION=AFTER
  /CATEGORIES VARIABLES=jobcat ORDER=A KEY=VALUE EMPTY=INCLUDE MISSING=EXCLUDE.

On Fri, Mar 31, 2017 at 1:05 AM, emma78 <[hidden email]> wrote:
HI,
is it possible to have different statistics for a subtotal and a total?

So that I got fot example count and colpct for the subotal and only count
for the 'real' total?


Thanks in advance!



--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Custom-tables-to-keep-only-total-columns-tp5733930p5734025.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



--
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
|  
Report Content as Inappropriate

Re: Ctables

emma78
HI,
this is a good work around but if I have variables with missing data, I do not see the right count for the scale variable.
For example I got n=485 in total and for educ only n=190, but I then see the n=485 in my ctable for the scale educ :-(

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Ctables

Jon Peck
To get the count for a scale variable taking missing values into account, use the VALIDN statistic instead of COUNT.

On Wed, Apr 5, 2017 at 5:41 AM, emma78 <[hidden email]> wrote:
HI,
this is a good work around but if I have variables with missing data, I do
not see the right count for the scale variable.
For example I got n=485 in total and for educ only n=190, but I then see the
n=485 in my ctable for the scale educ :-(





--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Custom-tables-to-keep-only-total-columns-tp5733930p5734047.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



--
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
Loading...