Custom tables to keep only total columns
Locked
 
|
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 |
|
|
Hi taras, Thanks. But variable " OptivaNo" is string variable. Thanks On Mar 3, 2017 2:44 PM, "Taras Koniukhov" <[hidden email]> wrote:
|
Re: Custom tables to keep only total columns
|
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 |
|
|
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:
-- |
|
|
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! |
|
|
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, -- |
|
|
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? |
|
|
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 :-) -- |
|
|
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.. |
|
|
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! |
|
|
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, -- |
|
|
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 :-( |
|
|
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, -- |
«
Return to SPSSX Discussion
|
1 view|%1 views
| Free forum by Nabble | Edit this page |